Problem with export to csv (1 Viewer)

M

Monoceros

Registered User.

Local time

Today, 17:34

Joined

Oct 30, 2006

Messages

27

• Oct 23, 2011

• #1

I need to export a table (ExportTable) to a csv-file (Export) and I tried the following code :

DoCmd.TransferText acExportDelim, , "ExportTable", "c:\Export.csv"

When I try this, I get the message (it's a translation, the english version may vary) : : "The field separator sign of the specification of the textfile is the same as the decimal sign or the text separation sign"

When I look at my Windows Control Panel, my list separator is ";" while my decimal sign is ",".

What could be wrong ?

 

T

Taruz

Registered User.

Local time

Today, 17:34

Joined

Apr 10, 2009

Messages

168

• Oct 23, 2011

• #2

Hi..

Try to export in this way..:

Code:Copy to clipboard

Dim trz As Integer
Dim strCSV As String
    
    For trz = 1 To 511
        Close #trz
    Next trz
    trz = FreeFile
    Open "c:\Export.csv" For Output Access Write As #trz
    
    With CurrentDb.OpenRecordset("ExportTable", 4)
        Dim x As Integer
            For x = 0 To .Fields.Count - 1
                strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & ", "
            Next x
            Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)

        Do Until .EOF
            strCSV = ""
            For x = 0 To .Fields.Count - 1
                strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>") & ", "
            Next x
            Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
            .MoveNext
        Loop
        
    End With
           
   
    Close #trz

 

Last edited: Oct 23, 2011

M

Monoceros

Registered User.

Local time

Today, 17:34

Joined

Oct 30, 2006

Messages

27

• Oct 23, 2011

• #3

Thank you, the export works, but there seems to be a small bug.

The csv file contains the number of rows as there are rows in the Access-table, but every row contains the same fields as the first record in the Access table.

 

T

Taruz

Registered User.

Local time

Today, 17:34

Joined

Apr 10, 2009

Messages

168

• Oct 23, 2011

• #4

sorry..

re-edited my message above..

 

M

Monoceros

Registered User.

Local time

Today, 17:34

Joined

Oct 30, 2006

Messages

27

• Oct 23, 2011

• #5

Thank you ... it works now

 

M

Monoceros

Registered User.

Local time

Today, 17:34

Joined

Oct 30, 2006

Messages

27

• Oct 23, 2011

• #6

Maybe a last question : is there a way to include the column header ?

 

T

Taruz

Registered User.

Local time

Today, 17:34

Joined

Apr 10, 2009

Messages

168

• Oct 23, 2011

• #7

Monoceros said:

Maybe a last question : is there a way to include the column header ?

Click to expand...

I updated the code again..

 

C

chrisguk

Registered User.

Local time

Today, 09:34

Joined

Mar 9, 2011

Messages

148

• Nov 14, 2011

• #8

But what if you wanted to choose the file location dynamically?