Hi All,
I'm trying to output a table to a CSV file using TransferText in Access 2012. There are 7 fields in the table and they are all formatted as text.
One of the fields contains a time that must be formatted as a 6 digit number - ie. 9am is 090000. When I export the CSV and open it in Excel I lose the leading zeros. I'm guessing that the TransferText function tries to be clever and converts the text field to a number format simply because it contains a number! Infuriating!
I've been trying to fix this all week! - I've tried using an export specification, but that makes no difference. I tried creating a schema.ini file but that seems to be ignored. I can't enclose the number in quotes, as the final CSV has to be uploaded into another piece of software that requires the formatting to be exact.
Does anyone know a way that I can achieve the result I need? Is there a way to output the CSV without using TransferText which will keep the formatting of my table?
Thanks in advance for any help.
Simon
I'm trying to output a table to a CSV file using TransferText in Access 2012. There are 7 fields in the table and they are all formatted as text.
One of the fields contains a time that must be formatted as a 6 digit number - ie. 9am is 090000. When I export the CSV and open it in Excel I lose the leading zeros. I'm guessing that the TransferText function tries to be clever and converts the text field to a number format simply because it contains a number! Infuriating!
I've been trying to fix this all week! - I've tried using an export specification, but that makes no difference. I tried creating a schema.ini file but that seems to be ignored. I can't enclose the number in quotes, as the final CSV has to be uploaded into another piece of software that requires the formatting to be exact.
Does anyone know a way that I can achieve the result I need? Is there a way to output the CSV without using TransferText which will keep the formatting of my table?
Code:
DoCmd.TransferText TransferType:=acExportDelim, SpecificationName:="SAPExportSpec", TableName:="tbl_ExportCSV1", FileName:="C:\Documents and Settings\b77742\Desktop\1QLive\" & strFileName
Thanks in advance for any help.
Simon