Output table to CSV while retaining all fields as text (1 Viewer)

Cavman

Registered User.
Local time
Today, 15:01
Joined
Oct 25, 2012
Messages
66
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?

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
 

DJkarl

Registered User.
Local time
Today, 10:01
Joined
Mar 16, 2007
Messages
1,028
This may sound crazy, but how are you checking the file to see if the zeros are there or not, Excel? If the answer is yes than that may be your problem, try opening the CSV file in notepad or wordpad and see if the Zeros are there, Excel will try to be "helpful" by auto converting text to numbers sometimes.
 
Last edited:

Cavman

Registered User.
Local time
Today, 15:01
Joined
Oct 25, 2012
Messages
66
You're right - there's nothing wring with the CSV file!!! It's Excel that's dropping the zeros. And I'm usually the first person to check the obvious first. Can't believe I've spent all week trying to fix that!

Thanks for that
 

Users who are viewing this thread

Top Bottom