CSV file to Access table and losing the leading zero

[KarenH]

I have an Excel spreadsheet that contains a date field entered in six digit
format, for example 063006, 043006, and so on. I need to convert it to a
csv file and then import it into an Access table. It is formated as text,
and entered with all six digits. After conversion, the CSV file shows it in
six-digit format.

However, after a transfertext in VBA, it ends up dropping the leading zero,
end displays as 63006 and 43006. I have even tried using an import spec and
the same thing happens.

If I manually do an "Import", it comes in fine.

The code I'm using to do the transfer is:
DoCmd.TransferText acImportDelim, , "disburse", "DisbursementsFile", True

Any help would be appreciated. Thanks

 

[Klatuu]

Open the csv file with a text editor rather than Excel. Look to see if the
six digit values are surrounded with qoutes and that the leading 0 is there.

 

[KarenH]

The leading zero is there, but although all of the fields are text, nothing
has quotes around it.

 

[Klatuu]

When you created the Import Spec, did you specifiy Text as the data type for
that field? Also, in your original example, I did not see that you are using
an Import Spec in your TransferText.
If revisiting that does not work, you may consider an undate query that runs
after the import that will fix the date field.
Format(MyProblemField,"000000") should do it.

 

[KarenH]

Thanks for the response! The import spec I had in there did specify text as
the data type, but it didn't make any difference so I took it out. But your
suggestion to use an update query to run after the import did the trick --
THANKS!