Solved TransferText

[The_Doc_Man]

I see the SQL in post #1 has a bunch of qualifying references to a table named "Contacts" - but RST9 is self-referential and makes no mention of table contacts in the FROM clause. Which means that [Contacts]. is essentially "dangling" syntax. You use strSQL inside the OpenRecordset of RST9 - but the problem? It is part of rst9 and yet refers to rst9. It is self-referencing. At no point is the table named "Contacts" brought into play. It would have to be in a FROM clause for you to use it to qualify the field names.

The definition for DAO requires that you have either a table name, a query name, or an SQL statement that would (potentially) return records. A SELECT would do that if it were well-formed, but due to the self-referential nature of strSQL, you cannot open that recordset. You are in essence asking it to open a recordset to itself. That just won't fly.

You could save yourself some typing if table Contacts is really your data source. That is, if you really meant to use "FROM Contacts" and no other table is involved, you can omit the qualifier prefixes. As long as it is a single source, you don't need qualifiers.

You commented that your data was already filtered. Therefore my next question is "in what way?" Because you might be able to build that string already filtered by referring to (copying) the same WHERE clause (or equivalent) that assured your proper filtration from whatever source was involved.

 

[ClaraBarton]

Whoa... you're right. I didn't catch that I was using Contacts AND rst9. How stupid of me. Starting over...

 

[arnelgp]

this will work also on simple table (no attachment field or mvf).
it will export filtered/unfiltered records.

 

[ClaraBarton]

Thank you so much for your help. Although I ended up not using any of it, you steered me where I needed to go and I appreciate it. This is what I'm using and it seems to be working well. Export and Import

        Case 2
          Set fsoFile = CreateObject("Scripting.FileSystemObject") 'ability to search for path

          rst9.MoveFirst
            Do While Not rst9.EOF
                strLines = strLines & rst9.Fields("Company") & ","
                strLines = strLines & rst9.Fields("LastName") & ","
                strLines = strLines & rst9.Fields("FirstName") & ","
                strLines = strLines & rst9.Fields("HyperlinkFullAddress") & ","
                strLines = strLines & rst9.Fields("Business") & ","
                strLines = strLines & rst9.Fields("Home") & ","
                strLines = strLines & rst9.Fields("Mobile") & ","
                strLines = strLines & rst9.Fields("Address") & ", "
                strLines = strLines & rst9.Fields("City") & ", "
                strLines = strLines & rst9.Fields("State") & ", "
                strLines = strLines & rst9.Fields("Zip") & ", "
                strLines = strLines & rst9.Fields("Country") & ","
'                strLines = strLines & rst9.Fields("Notes") & ","
                strLines = strLines & rst9.Fields("Category") & ", "
                strLines = strLines & rst9.Fields("NonGMO") & ","
                strLines = strLines & rst9.Fields("Organic") & vbCrLf
'                strLines = strLines & rst9.Fields("HyperlinkFullAddress") & vbCrLf

           rst9.MoveNext
            Loop
         strPath = CurrentProject.Path & "\Export.csv"

    Set objFile = fsoFile.CreateTextFile(strPath)

    objFile.WriteLine strLines
    objFile.Close
    Set fsoFile = Nothing
    Set objFile = Nothing

  MsgBox "Done!"
        
        Case 3
            Dim strTbl As String
            Dim strImpSpec As String
            
            strTbl = "Export"
            strPath = CurrentProject.Path & "\ExportToFred.csv"
            strImpSpec = "Export Link Specification2"
            
            DoCmd.TransferText acImportDelim, strImpSpec, strTbl, strPath, False
            DoCmd.OpenQuery "qryExportToFred"
            DoCmd.SetWarnings False
            DoCmd.DeleteObject acTable, "Export"
            DoCmd.SetWarnings True
            
  End Select

 

[theDBguy]

Thank you so much for your help. Although I ended up not using any of it, you steered me where I needed to go and I appreciate it. This is what I'm using and it seems to be working well. Export and Import

        Case 2
          Set fsoFile = CreateObject("Scripting.FileSystemObject") 'ability to search for path

          rst9.MoveFirst
            Do While Not rst9.EOF
                strLines = strLines & rst9.Fields("Company") & ","
                strLines = strLines & rst9.Fields("LastName") & ","
                strLines = strLines & rst9.Fields("FirstName") & ","
                strLines = strLines & rst9.Fields("HyperlinkFullAddress") & ","
                strLines = strLines & rst9.Fields("Business") & ","
                strLines = strLines & rst9.Fields("Home") & ","
                strLines = strLines & rst9.Fields("Mobile") & ","
                strLines = strLines & rst9.Fields("Address") & ", "
                strLines = strLines & rst9.Fields("City") & ", "
                strLines = strLines & rst9.Fields("State") & ", "
                strLines = strLines & rst9.Fields("Zip") & ", "
                strLines = strLines & rst9.Fields("Country") & ","
'                strLines = strLines & rst9.Fields("Notes") & ","
                strLines = strLines & rst9.Fields("Category") & ", "
                strLines = strLines & rst9.Fields("NonGMO") & ","
                strLines = strLines & rst9.Fields("Organic") & vbCrLf
'                strLines = strLines & rst9.Fields("HyperlinkFullAddress") & vbCrLf

           rst9.MoveNext
            Loop
         strPath = CurrentProject.Path & "\Export.csv"

    Set objFile = fsoFile.CreateTextFile(strPath)

    objFile.WriteLine strLines
    objFile.Close
    Set fsoFile = Nothing
    Set objFile = Nothing

  MsgBox "Done!"
      
        Case 3
            Dim strTbl As String
            Dim strImpSpec As String
          
            strTbl = "Export"
            strPath = CurrentProject.Path & "\ExportToFred.csv"
            strImpSpec = "Export Link Specification2"
          
            DoCmd.TransferText acImportDelim, strImpSpec, strTbl, strPath, False
            DoCmd.OpenQuery "qryExportToFred"
            DoCmd.SetWarnings False
            DoCmd.DeleteObject acTable, "Export"
            DoCmd.SetWarnings True
          
  End Select

Hi. Glad to hear you got it sorted out. Just for my own understanding, when you say you "filtered" the recordset, are you referring to reducing the number of columns/fields you want to export? That's how I understood your original post. For example, let's say rst9 originated from a table with 30 columns and you only want the (user selected?) 17 columns exported into the CSV file. Is that correct? Cheers!

 

[arnelgp]

well done, but..
you need to delimit (enclosed in double quote ") your fieldname and field value to make it a Real csv file:

your text output should look like this:

"company","lastname","firstname","HyperlinkFullAddress",....
"companyValue","theLastName","theFirstName","theHyperlink",...

what you have, based on your code:

company,lastname,firstname,HyperlinkFullAddress,...

...which is not a CSV file but a Plain Text file.

           Do While Not rst9.EOF
                strLines = strLines &  (Chr(34) + rst9.Fields("Company") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("LastName") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("FirstName") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("HyperlinkFullAddress") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Business") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Home") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Mobile") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Address") + Chr(34)) & ", "
                strLines = strLines & (Chr(34) + rst9.Fields("City") + Chr(34)) & ", "
                strLines = strLines & (Chr(34) + rst9.Fields("State") + Chr(34)) & ", "
                strLines = strLines & (Chr(34) + rst9.Fields("Zip") + Chr(34)) & ", "
                strLines = strLines & (Chr(34) + rst9.Fields("Country") + Chr(34)) & ","
'                strLines = strLines & (Chr(34) + rst9.Fields("Notes") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Category") + Chr(34)) & ", "
                strLines = strLines & (Chr(34) + rst9.Fields("NonGMO") + Chr(34)) & ","
                strLines = strLines & (Chr(34) + rst9.Fields("Organic") + Chr(34)) & ","
'                strLines = strLines & (Chr(34) + rst9.Fields("HyperlinkFullAddress") + Chr(34)) & vbCrLf

           rst9.MoveNext
            Loop

i used + inside the parenthesis to propagate Null Field value.

 

[MajP]

Although I ended up not using any of it, you steered me where I needed to go and I appreciate it.

I guess you never read my post since I do this in a generic and much simpler version. Allows you to export any dao recordset and includes the fields names.

 

[ClaraBarton]

Saved it. Will study it. Maybe use it. Was in a hurry and needed to get this out. Sorry

 

[theDBguy]

Saved it. Will study it. Maybe use it. Was in a hurry and needed to get this out. Sorry

Hi. I would really appreciate it if you could clarify the "filtered recordset" part for me. Just a quick "i am filtering records" or "i am filtering columns" would be good. Thanks.

 

[ClaraBarton]

The main form is filtered by record... perhaps 10 records out of 1000. That filtered recordset is carried to a dialog with options but one of the options needs only some of the fields from the filtered set (more filtering) to be exported to another database.

 

[theDBguy]

The main form is filtered by record... perhaps 10 records out of 1000. That filtered recordset is carried to a dialog with options but one of the options needs only some of the fields from the filtered set (more filtering) to be exported to another database.

Hi. Thanks for the clarification. I was used to using the term "filter" when reducing the number of records. I just got a little bit confused because I knew you said you wanted to reduce the number of columns instead.

One last question, if I may. Is the list if Fields you want to export fixed? Meaning, are you going to export the same set of fields all the time? You mentioned something about the user doing some manipulation earlier, so I was just wondering if that means they are selecting which fields they want to export.

Cheers!

 

[ClaraBarton]

no no. it's a fixed set. I'm trying to automate everything so my brother can't screw it up. We export a csv file to go into some sort of on line app for bulk emailing. Prospects, addresses, etc. in turn are exported to go into the original database for follow up. Sounds more complicated than it is. Just sales leads and general contacts but things crop up that make things more complicated than you originally thought.

 

[theDBguy]

no no. it's a fixed set. I'm trying to automate everything so my brother can't screw it up. We export a csv file to go into some sort of on line app for bulk emailing. Prospects, addresses, etc. in turn are exported to go into the original database for follow up. Sounds more complicated than it is. Just sales leads and general contacts but things crop up that make things more complicated than you originally thought.

Okay, sounds good. Thanks for the additional information. Good luck with your project.