Solved How do I Create a Proper CSV file

[MajP]

I am trying to help someone create a manual CSV file. The file appears properly formatted, but it opens up as a single cell in Excel. What am I missing. See thread

Query results horizontally for single line export to Excel

Hi. I need to have the query render the results vertically, into a single row for exporting to Excel for mail merge. I've read that I need multiple crosstab queries to do this, but I am not really sure where/how to start. Table: ID, Team, Color, Sport Wish list results: TeamA, TeamAColor...

www.access-programmers.co.uk

Thanks.

 

[Sun_Force]

This is for vba in Excel. But I think you can use it with a recordset in Access too.

How to create a separate CSV file from VBA?

I need to output some results as a .csv file, that gets parsed later on by another process. In order to produce these results, I have a huge workbook containing all the macros and functions that I ...

stackoverflow.com

 

[MajP]

Thanks, but no real information in there. Here is the DB that makes the CSV.

 

[MajP]

I could not get the comma to work but replaced with a tab and it worked. Still curious.

 

[MarkK]

Maybe a matter of the text encoding of the file? I can get it to work if I don't set Unicode = True...

Function CreateTextFile(FileName as String, Optional Overwrite As Boolean = True. Optional Unicode As Boolean = False) As TextStream

Call it like this, it fails...

Set Stream = FS.CreateTextFile(ExportFile, False, True)

Call it like this, it works.

Set Stream = FS.CreateTextFile(ExportFile)

This routine works for me in your database...

Public Sub Export3()
    Dim ts As Scripting.TextStream
   
    With New Scripting.FileSystemObject
        Set ts = .CreateTextFile(CurrentProject.Path & "\test.csv")
    End With
   
    ts.WriteLine "Team,Color,Sport"
   
    With CurrentDb.OpenRecordset("tblTest")
        Do While Not .EOF
            With .Fields
                ts.WriteLine Join(Array(!Team, !Color, !Sport), ",")
            End With
            .MoveNext
        Loop
        .Close
    End With
   
    ts.Close
   
    VBA.Shell "Excel.exe """ & CurrentProject.Path & "\test.csv"""
End Sub

 

[Gasman]

I am trying to help someone create a manual CSV file. The file appears properly formatted, but it opens up as a single cell in Excel. What am I missing. See thread

Query results horizontally for single line export to Excel

Hi. I need to have the query render the results vertically, into a single row for exporting to Excel for mail merge. I've read that I need multiple crosstab queries to do this, but I am not really sure where/how to start. Table: ID, Team, Color, Sport Wish list results: TeamA, TeamAColor...

www.access-programmers.co.uk

Thanks.

Is the O/P actually using comma in their regional settings?

 

[MajP]

@MarkK,
That is the answer. If I change my code it works going from.
Set Stream = FS.CreateTextFile(ExportFile, False, True)
to
Set Stream = FS.CreateTextFile(ExportFile, False, False)

So changing from unicode to ASCII works. Not sure I fully understand that, but it works.

 

[Pat Hartman]

fsoFile.WriteLine strRec
is what I use to write each line. If you want to use the other methods, you probably need CRLF at the end of each record. Looks like WriteLine automaticly adds the CRLF but the other methods don't

Also, properly formatted .csv files enclose strings in quotes but not other columns.

The reason I had to write the .csv file manually rather than just using TransferText was because the receiving system wanted ALL fields enclosed in quotes regardless of field type.