Stop Access writing two sets of double quotes to csv

Question

So, I'm using the output from a record set and writing out to a csv file. But I'm getting an issue with Quotation marks. Ideally I'd like to include them as text markers. But if I include them in my line of text they get printed as two sets of quotation marks.

I want this as the output (delimited by tabs):

"Header1" "header2" "......[]...."headerX"

I tried this

Sub Write_Tbl(Filename, StrSQL)
Dim unicode, UTF, i As Long , Fileout As Object, forwriting,  TristateUseDefault, TxtStr As String, TextHolder As String, rs As Recordset

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim File_out As Object
Set File_out = fso.CreateTextFile(Filename, True, unicode = UTF - 8)
File_out.Close

Open Filename For Output As #1

Set rs = CurrentDb.OpenRecordset(StrSQL)
rs.MoveFirst

'for headers
    TxtStr = rs.Fields.Item(0).Name         'so that there isn't a tab at the start of the string

For i = 1 To rs.Fields.Count - 1

TxtStr = TxtStr & chr(34) & vbTab & chr(34) & rs.Fields.Item(i).Name
Next i

Write #1, TxtStr & chr(34)          'write headers to file

and got this as the output

""Header1"" ""header2"" ""......[]....""headerX""

So I removed the quotation marks and got this:

'for headers
    TxtStr = rs.Fields.Item(0).Name         'so that there isn't a tab at the start of the string

For i = 1 To rs.Fields.Count - 1

TxtStr = TxtStr & vbTab & rs.Fields.Item(i).Name
Next i

Write #1, TxtStr          'write headers to file

and what I'm getting is

"Header1 header2 ......[]....headerX"

If I monitor the variables in the locals window, there's only one set of quotes so it must be something to do with printing? It doesn't happen if I use single quotation marks (ascii no 39). I also tried just using write to file, rather than as a text stream, but I got memory issues and ERROR 5 issues. STUMPED. Please help.

on another note, doing this: Dim unicode, UTF, i As Long will dim only i as long, the others as variants, you need : 'Dim unicode As Long, UTF As Long, i As Long' — Preston, Oct 14 '16 at 12:07
Thanks, it was actually my intention to do so, but for a long while coding in vba I had no idea that it worked like that, it's an excellent and widely undocumented point. — REdim.Learning, Oct 14 '16 at 12:24

score 1 · Accepted Answer · edited May 23 '17 at 10:27

If you have prepared your text string in VBA, you should use the Print # statement instead of Write # .

Documentation: Print # vs. Write #

Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file.

Note:

I'm not sure if these functions write Unicode at all, or care how the file was created.

Open Filename For Output As #1

will create the file if it doesn't exist, so you can probably omit the whole CreateTextFile part.

Or use File_out.WriteLine() instead, it seems odd to mix both methods (FSO and the ancient Print/Write statements).

Edit: see How to create and write to a txt file using VBA

Thanks, this is really helpful. – REdim.Learning Oct 18 '16 at 08:36 — REdim.Learning, Oct 18 '16 at 08:36

Stop Access writing two sets of double quotes to csv

1 Answers1