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.