TransferText fails if there is quote

[Guest]

Hi
I am using the TransferText method to import a CSV file into a table.
Everything works fine except when there is a quote in the field values

Eg.:
12345678,8P22795,"MY COMPANY" LIMITED,1.000,0.0,1.0,0.000,534.325,534.325

The first 2 columns imported correctly, On the third column only MY COMPANY
is imported (the word LIMITED is missed). 4th column onwards, nothing is
imported.

Any help appreciated.

Thanks
Mohan

 

[Guest]

You would need to set up an import specification and configure it to use a
different text qualifier. Haven't done this for a while, but I think the
basic steps are:

Do a manual import of the csv file right-click in the database window and
select Import...)
Follow the import wizard through and set your field delimiter character and
your text qualifier.
Before you 'Finish' the wizard, click the Advanced button and save the
specification.
You can then refer to this specification when you run the TransferText method.

Good luck,

Jon.

 

[Guest]

Hi

I tried this and still it doesn't work. However, when I import the file
manually (using the wizard), it worked fine. I even tried with changing the
specification with text qualifier as " (quote) and still doesn't work.

I think Access is expecting all of the fields to be qualified with quote or
none of them with quote (you cannot have some with quote and others witout
the quote)

I could try I/0 to read and insert into table line by line but this will
take too long. Anyways, I have asked the people creating the file to remove
the quote in the descripton field.

Thanks for you help.