Code to export each query to separate csv BUT with certain conditions (1 Viewer)

jaryszek · Sep 18, 2019

Hi Guys,

i want to export selected queries to separated csv files and do it via VBA.
The problem is that my standard settings of Access are:

And i have to always go to advance --> and change decimal symbol from ";" to "." and Field delimeter from ";" to ",".

Is there any command to do this? I saw there is transferText but there is no way to set up these fields above.

What can i do? - my thoughts:
1. Change general Field delimeters and decimal symbol in Access only when code is running and return them back after export
2. Find the method which can change the automatically in VBA.

Please help with this,
Best,
Jacek

theDBguy · Sep 18, 2019

Hi. Have you tried creating and using an export specification? I was wondering if you can specify the delimiter and decimal separator in an export spec.

jaryszek · Sep 18, 2019

Hi,

thanks. Yes i can export specification for each query manually and use it in macro. But i do not want to do this. I want to have full automation here.

Maybe there is something like: application.fieldseparator ....

Best,
Jacek

sxschech · Sep 18, 2019

You can have multiple specs and then in your code you can specify which spec to use or can pass the specname in as a variable. An alternative is to use a recordset and save to text file then you can concatenate the separation character. Here is an example:

Code:

Public Function ExportTextDelimited(filename As String, strQueryName As String, strDelimiter As String, Optional CustomHeader As String)
'http://stackoverflow.com/questions/13309994/export-access-query-results-to-csv
'20160219
    Dim rs          As Recordset
    Dim strHead     As String
    Dim strData     As String
    Dim inti        As Integer
    Dim intFile     As Integer
    Dim fso         As New FileSystemObject
    
    On Error GoTo Handle_Err
    
    fso.CreateTextFile (filename)

    Set rs = CurrentDb.OpenRecordset(strQueryName)

    rs.MoveFirst

    intFile = FreeFile
    strHead = ""

    'Add the Headers
    If Len(CustomHeader) = 0 Then
        For inti = 0 To rs.Fields.Count - 1
            If strHead = "" Then
                strHead = rs.Fields(inti).Name
            Else
                strHead = strHead & strDelimiter & rs.Fields(inti).Name
            End If
        Next
    Else
        strHead = CustomHeader
    End If
    Open filename For Output As #intFile

    Print #intFile, strHead

    strHead = ""

    'Add the Data
    rs.MoveLast
    rs.MoveFirst
    While Not rs.EOF

        For inti = 0 To rs.Fields.Count - 1
            If strData = "" Then
                strData = IIf(IsNumeric(rs.Fields(inti).value), rs.Fields(inti).value, IIf(IsDate(rs.Fields(inti).value), rs.Fields(inti).value, """" & rs.Fields(inti).value & """"))
            Else
                strData = strData & strDelimiter & IIf(IsNumeric(rs.Fields(inti).value), rs.Fields(inti).value, IIf(IsDate(rs.Fields(inti).value), rs.Fields(inti).value, """" & rs.Fields(inti).value & """"))
            End If
        Next

        Print #intFile, strData

        strData = ""
        DoEvents
        Forms!frmutilities.btnProcess.caption = "Exporting row " & rs.AbsolutePosition & " of " & rs.RecordCount
        rs.MoveNext
    Wend

        Close #intFile

    rs.Close
    Set rs = Nothing
    
    'Open the file for viewing
    'Application.FollowHyperlink FileName
    
    Exit Function
        
Handle_Err:
    MsgBox Err & " - " & Err.Description
    
End Function

Code:

...
...
stCustomHeader = "Account Group,Status,Currency,Owner,Company ,Customer Name,Salutation,First Name,Last Name,Title,Street Number,Street,City,State/Province,Postal Code,Country,Phone,Fax,Mobile Phone,Email,Website,Source"
                Call ExportTextDelimited(stExportFileName, "qryFormat_Custom", ",", stCustomHeader)
                MsgBox "File " & stExportFileName & " is now saved", vbOKOnly + vbInformation, "Export to CSV"
                FNameExists = True
...
...

jaryszek · Sep 18, 2019

Hi,

thank you. Can you please provide example - is needed.

Best,
Jacek

jaryszek · Sep 18, 2019

Hi,

thank you. Can you please provide example - is needed.

Best,
Jacek

theDBguy · Sep 18, 2019

jaryszek said:
Hi,

thanks. Yes i can export specification for each query manually and use it in macro. But i do not want to do this. I want to have full automation here.

Maybe there is something like: application.fieldseparator ....

Best,
Jacek

Hi. Not sure if there's anything like that available. I know there is this, but it's read only. So, if there is nothing simple like that available, then the next best thing is to do some prep work up front and then automate the whole thing afterwards.

jaryszek · Sep 18, 2019

Thank you Guys very much!

Hmm the code seems to be complicated and i think i will use saved spec.
Can i run macro only on selected queries?

Best,
Jacek

sxschech · Sep 18, 2019

You shouldn't need to modify the code, just paste in a standard code module. Then to use it is only one line in the code where you were calling the transfer text or spec stuff.

Call ExportTextDelimited(stExportFileName, "qryFormat_Custom", ",", stCustomHeader)

You give it the name of:
csv file
your query/table
the delimiter
and if your output needs a customized header (different than the source field names) that is optional and provided as a csv string.

jaryszek · Sep 18, 2019

thank you very much sxschech for help and support.

Best Wishes,
Jacek

arnelgp · Sep 18, 2019

for each export/import you made, save your export/import spec to a meaningful name.
then you can automate it by calling a function, passing the location+filename of the New csv to create, and the name of the export spec.

Code:

'''''''''''''''''''''''''''
'
' arnelgp
'
'
Public Sub RunSpecOnOtherFile(ByVal SpecName As String, _
                                    ByVal strPathAndFileToRun As String)
    Dim objSpec As ImportExportSpecification
    Dim strXML As String
    Dim intStartPos As Integer, intEndPos As Integer
    
    Set objSpec = CurrentProject.ImportExportSpecifications.Item(SpecName)
    ' get the xml string
    strXML = objSpec.xml
    Debug.Print strXML
    intStartPos = InStr(1, strXML, "Path")
    intStartPos = InStr(intStartPos, strXML, "=")
    intEndPos = InStr(intStartPos, strXML, "xmlns") - 1
    ' insert the new filename between intstartpos and intendpos
    strXML = Left(strXML, intStartPos) & " " & _
            """" & strPathAndFileToRun & """" & _
            Mid(strXML, intEndPos)
    Debug.Print strXML
    ' replace the xml of the spec to the new strXML
    objSpec.xml = strXML
    ' run the specification
    objSpec.Execute
    ' close it
    Set objSpec = Nothing
End Sub

sxschech · Sep 18, 2019

You're welcome. Hope it works for you and if you need further clarification, let me know.

jaryszek · Sep 19, 2019

arnelgp said:

for each export/import you made, save your export/import spec to a meaningful name.
then you can automate it by calling a function, passing the location+filename of the New csv to create, and the name of the export spec.

Code:

'''''''''''''''''''''''''''
'
' arnelgp
'
'
Public Sub RunSpecOnOtherFile(ByVal SpecName As String, _
                                    ByVal strPathAndFileToRun As String)
    Dim objSpec As ImportExportSpecification
    Dim strXML As String
    Dim intStartPos As Integer, intEndPos As Integer
    
    Set objSpec = CurrentProject.ImportExportSpecifications.Item(SpecName)
    ' get the xml string
    strXML = objSpec.xml
    Debug.Print strXML
    intStartPos = InStr(1, strXML, "Path")
    intStartPos = InStr(intStartPos, strXML, "=")
    intEndPos = InStr(intStartPos, strXML, "xmlns") - 1
    ' insert the new filename between intstartpos and intendpos
    strXML = Left(strXML, intStartPos) & " " & _
            """" & strPathAndFileToRun & """" & _
            Mid(strXML, intEndPos)
    Debug.Print strXML
    ' replace the xml of the spec to the new strXML
    objSpec.xml = strXML
    ' run the specification
    objSpec.Execute
    ' close it
    Set objSpec = Nothing
End Sub

Ok,
why just not use DoCmd.transporText to do this?

Code:

DoCmd.TransferText transferType:=acExportDelim, TableName:="Table1", SpecificationName:="Table1Spec", fileName:="C://path/table1.csv", hasfieldnames:=True

and use variables for properties?

Jacek

arnelgp · Sep 19, 2019

there has been post before that when using import/export spec on a different filename other than the one where it was originally created, the spec fails to import/export.

there is a portion on my code, debug.print strXML, there you can see the original Path (filename) is embedded on the import/export spec.
the function replaces that with the one you supply.

Pat Hartman · Sep 19, 2019

I'm concerned that your defaults for the export are so strange. Are you using a non-English version of Access? I've never seen these settings as the default before. I do know that some non-English versions use , rather than . as the decimal separator depending on the local currency.

To use TransferText, you need to export the file ONCE manually so you can create the export spec. Press the advanced button to change the settings and then save the spec. You can use the same spec for all files with the same format. This is the "old" method and works much better than the "new" method which doesn't allow you to change a spec once it is created and also forces the file name to remain constant. With the "old" method, you specify the file name as part of the TransferText so it can be changed as needed.

jaryszek · Sep 19, 2019

I know only old method, i do not know new one.

Thanks Guys.
I will test this one:

Code:

DoCmd.TransferText transferType:=acExportDelim, TableName:="Table1", SpecificationName:="Table1Spec", fileName:="C://path/table1.csv", hasfieldnames:=True

but first i will create manually specs.

I am on polish regional settings so yes it can be strange

Best,
Jacek

jaryszek · Sep 19, 2019

Ok i tried with this one:

Code:

 DoCmd.TransferText transferType:=acExportDelim, TableName:="ExportEnvironments", SpecificationName:="SpecCSV", fileName:="C:\Users\admin\Desktop\Newest Pull request\SpecCSV.csv", hasfieldnames:=True

but i am getting error so SpecCSV has to manually created for EACH query.
You can not use one general specs for all exported queries...

Jacek

arnelgp · Sep 19, 2019

that is why I created the function.

jaryszek · Sep 19, 2019

thank you arnelgp,

ok great i will use it

jaryszek · Sep 19, 2019

One more thing.

in youe function arnelgp you can import any other file which you want.
I tested and it is working. I created SpecCSV spec which is importing data and it is working.
But what about export?

I want to export few queries with the same spec.
How can i provide them into function?

Best,
Jacek

Code to export each query to separate csv BUT with certain conditions (1 Viewer)

Registered User.

Attachments

I’m here to help

Registered User.

Registered User.

Registered User.

Registered User.

I’m here to help

Registered User.

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Super Moderator

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Similar threads

Users who are viewing this thread