TransferText acImportDelim Problem

[Alan B. Densky]

I'm doing an import of a comma delimited file using DoCmd.TransferText
acImportDelim.

I have no control over the formatting of the file that I am importing, it is
comma delimited or nothing, and tab delimited wouldn't help me (that I can
see) anyway.

The problem arises when the csv file has a comma in the middle of it. For
Example: Jons Company, Inc.

The csv file loads correctly if I load it into Excel. But when I run my
import routine on it,
which uses the "DoCmd.TransferText acImportDelim," the import routine sees
the comma in the middle of the affected field, and splits that field
incorrectly, making all of the rest of the fields in the row shift one
column to the right. For some reason, it also adds quotes to the beginning
and end of every field that is imported into my table.

I don't want to re-engineer everything, because I've already done that
because of problems when linking to the data.

Does anyone know a simple way to loop through all of the fields in the csv
file, find any commas, and then get rid of a comma?

I can easily live with Jons Company Inc.instead of Jons Company, Inc.

Alan

 

[SQL Guy]

If the file is going to have the same layout each time you import it, you
can use an "Import Specification" (see help) and import the file as
comma-delimited with double-quotes as text qualifiers.

What you have there is a CSV file which (as you say) imports into Excel with
no problems (except the problems that Excel causes for itself!) but there is
no specific import option for it using TransferText in Access.

I wonder who made that decision at Microsoft!

MH

 

[John Nurick]

Hi Alan,

Does your "CSV" file have just the one field value on each line? If so,
treat it as tab-delimited, and Access will import the value complete
with comma.

If there's more than one field, then you may have a problem. One of the
rules for a valid CSV file is that when a field value contains a comma,
it *must* be enclosed ("qualified" in Access terminology) with quotation
marks. E.g.
99, "Jons Company, Inc", Smallville
contains three fields
while
99, Jons Company, Inc, Smallville
contains four fields.

There's no general way round that, because it's not possible for the
computer to distinguish between a comma used as a separator and a comma
that's part of the data. However, if you know the data well enough you
may be able to write code that will "regularise" your particular file.

Consider the example above, with
99, Jons Company, Inc, Smallville
needing to be split into three fields rather than four. If you know that
the first and third fields can never contain a comma, you know that the
first and last commas in the line of text are the field separators and
that everything between them is the value of the second field. So all
you have to do is to replace the first , with ," and the last comma with
", - and you have a regular CSV file that Access will import properly.

 

[Ken Snell \(MVP\)]

Hi SQL Guy,

I do use an import spec. But as far as the qualifier is concerned, it's
based on the way that the file is given to me.

In the Import Specification window (Advanced button in wizard windows when
importing a text file), you can set the qualifier of text values to the "
character. I assume that your data has " characters around the data that
have the embedded commas.

 

[Alan B. Densky]

Hi SQL Guy,

I do use an import spec. But as far as the qualifier is concerned, it's
based on the way that the file is given to me. I don't create the file. What
I've decided to do is to open the csv file in Excel, and use the find and
replace to replace all commas within the columns with a blank space. Then
save the csv file. Then import it. I tried it a couple of times today and so
far it seems to work okay.

Thanks for your advice though.

Alan

 

[MH]

Be *very* careful importing data into Excel, e.g. try typing the following
into some cells and see what happens:

Bank Sort Code: 25-06-07
Some kind of code: MARC90
A text string containing only numbers: 123456789123456789

Excel will convert anything that looks like a date to a date-time value and
will round any numeric value to 15 significant figures!

And there is no way to disable this functionality, the user has to be aware
of it from the start. This has caused many problems for me over the years!

I would re-try the import spec using text qualifiers (as Ken's post) before
doing anything as drastic as importing data into Excel.

MH