CSV export from Access 2010 help

Question

We are being required by a client to export results of a query (or table) as a comma delimited file with "" text qualifiers). Within one field there are multiple values. They require the values in that field to be pipe (|) delimited.

Is there any way to program this combination in VBA? At this point, the only way I know how to do it is to export it and do a "find...replace" in Excel for that field.

(I am a newbie to VBA with very little experience so any guidance is helpful.)

Thank you!

Answer 1

Before you can set up the Transfertext to do it in VBA, you have to create a file specification to use the Pipe instead of the quotes. This is easier done than said.

On the stupid Ribbon, click the External Data tab --> Text file in the Export (middle) section. 

Click OK on the Export wizard without checking any box.

Leave the Format as Delimited and press Next.

Choose Comma for the delimiter and enter a pipe (|) in the box next to Text Qualifier.

Select whether you want field names.

Do not press Next - press Advanced. Click Save As and save it. Remember the name - the Specs button is the only way to look them up.

Click next and close. You do not need to save the steps if you want to set it up in vb.

For your Transfertext code, you would use TransferText to export the file using the spec created:

    DoCmd.TransferText acExportDelim,  "SpecName", "TableName", FileName, True

Where the Specname is the name you gave the specification, TableName is the name of your table and FileName is the name file.

Answer 2

In your query that you export from, use the Replace function to replace whatever is the delimiter for that field with the pipe symbol.

Answer 3

Thank you for your reply.

Unfortunately I need the pipe delimiter in only one field, the rest need to be remain in quotes.

This only specifies a pipe delimiter.