0

I have a small access program that is exporting data to a csv. The csv needs to have field headers that include #'s. However when I export using VBA:

DoCmd.TransferText acExportDelim, "ThisExport", "GeneralQuery", "C:\ThisFile\data.csv", True

It changes the #'s into .'s:

enter image description here

enter image description here

jstacy00
  • 85
  • 3
  • 13
  • 1
    Use valid identifiers like `Email_1`. – Olivier Jacot-Descombes Feb 11 '21 at 20:49
  • I wish it were that easy. The data I'm exporting has to have those exact headers so that it can be imported into another software looking for those headers. – jstacy00 Feb 11 '21 at 21:54
  • Perhaps have to use textfile read/write. Export the file with TransferText then use Read/Write to modify. https://stackoverflow.com/questions/57194061/microsoft-access-vba-edit-text-file – June7 Feb 12 '21 at 00:53
  • Also https://stackoverflow.com/questions/18232469/how-can-i-edit-a-line-in-a-file-opened-with-filesystemobject-in-vba – June7 Feb 12 '21 at 00:58
  • Normalize your database. You should never have repeated fields like that. You need a separate table that is related many to one with just emails and IDs. Then you can have an arbitrary number of emails. – HackSlash Feb 16 '21 at 19:32

1 Answers1

1

One approach uses a UNION query:

SELECT 0 AS Cat, "Email #1" AS A, "Email #2" AS B, "Email #3" AS C FROM Table1
UNION SELECT 1, [Email #1], [Email #2], [Email #3] FROM Table1;

Set TransferText HasFieldNames argument to False and export query.

However, if your import app cannot deal with presence of Cat field, use textfile read/write methods. This is a fairly common topic. Export data with TransferText then use read/write methods to modify.

June7
  • 19,874
  • 8
  • 24
  • 34