Access 2010 - transfertext puts quotes around some fields

[Ziggy1]

Hi I am trying to build a PIPE delimited file with no double quotes. I created the specification file and selected <none> for the text qualifier but it will still put it around the fields that have a comma.

I can't have it around any field and I do not see a setting to make sure it is not in the export.

thanks

 

[Ranman256]

The import is confused.
You'd have to switch to a Fixed length setup so commas and quotes won't matter.
ID write a vb script to chop up the columns using the pipes, and set them to pipe delim,fixed width.

 

[Frothingslosh]

You could actually brute force it by reading the file one line at a time and using the Split function with the pipe as the delimiter, but it'll be a fair bit of work.

 

[gemma-the-husky]

it shouldn't put quotes round text if you have selected "no text qualifier". If you select "other /|" as the delimiter, the comma is immaterial.

however, it's just as easy to iterate the query recordset, and write the file in code.
easy to do, and you get full control of the output.

is it an EDI file of some sort, out of interest?


this sort of thing (pseudocode)

 open filename for output as #filenum
 open recordset
 while not rst.eof
     'assemble line
     print #filenum, line
     rst.movenext 
 wend
 close filenum
 close recordset

 

[Ziggy1]

thanks for the replies. I know it "shouldn't" but it does lol... put the double quotes when a field contains a comma.

anyways Gemma/Ranman is on the right track. I did some more searching and ended up with this code snippet....

' Note the ExPath and Exfile are variables instead of hard coding path... you need to declare
Set DB = CurrentDb
Set RS = DB.OpenRecordset("qryEXP")

Open ExPath & ExFile For Output As #1
'Open "c:\1A\ExportTest1.csv" For Output As #1
Do While Not RS.EOF
str1 = ""
For Each fld In RS.Fields
str1 = str1 & fld & "|"
Next
str1 = Mid(str1, 1, Len(str1) - 1) '--remove last ","
Print #1, str1
RS.MoveNext
Loop
Close #1
RS.Close

the process is for a Transportation Import file

 

[jdraw]

Perhaps you could post 10-20 records of your input as a .txt so readers can see what you are dealing with.