Problem with TransferText

[alpder]

I would be grateful for some help.

I have a csv file as follows:

PurchaseOrderNumber,DocumentDate
6310S02621733,15/12/2009
6310S02621734,15/12/2009

I have created a table in Access2003 with the correct field names. The data type for both fields is "Text". I am trying to use VB code to import the data from the csv file to the table using code:

DoCmd.TransferText acImportDelim, , "ORDERS", "C:\cc.csv", True

It reads the data in but omits the letter "S" (fifth character in first field)

If the "S" is relaced with a "V" or "X" and I run the code, the "V" or "X" are imported correctly.

This must be caused by something incredibly simple, but unfortunately I cannot find it!!

Thanks in anticipation.

 

[SkipVought]

Hi,

You might get better results in one of the many MS Access forums.

But here's a shot --

Change the format of your date string to the UNAMBIGUOUS yyyy/mm/dd format.

Your dd/mm/yyyy just may be causing the import manager some heartburn, cuz, Bill Gates & Co probably expect mm/dd/yyyy, just guessing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[alpder]

Thanks for the suggestion Skip, but your suggestion did not fix the problem.

Any other ideas?

alpder

 

[MakeItSo]

Hi alpder,

It reads the data in but omits the letter "S" (fifth character in first field)

If the "S" is relaced with a "V" or "X" and I run the code, the "V" or "X" are imported correctly.

This I don't get. This behaviour is not logical.
Check whether you are not using an import scheme. Something is definitely weird here.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SallyPDS]

May be a dumb idea, but try calling the table "ORDER". It might be that access is in some way thinking that ...S" means the "S" is a delimiter.

At worst, it'll be one more idea eliminated!

 

[alpder]

Yes, I agree with you MakeItSo, this behaviour is NOT logical.

I have tried SallyPDS's idea but it did not change anything.

However, I have created an Import Specification and that appears to have fixed the problem. I do not understand why as the field PurchaseOrderNumber is just listed as text format. I will have to wait for more orders to come in to test it further (The data for this file comes over the internet - most orders have "X" or "V", with occasional orders having the problem "S")

Thank you all for your help.

alpder