CODEWITHSUNDEEP
vbacsvms-access
REdim.Learning
REdim.Learning

Reputation: 659

Stop Access writing two sets of double quotes to csv

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.

Upvotes: 2

Views: 484

Answers (1)

Andre
Andre

Reputation: 27634

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

Upvotes: 1

Related Questions