Import from text file with double quotes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a number of text files that I am trying to import into Access and/or
SQL Server. The files are delimited with the pipe sign (|). Most work without
a problem using the TransferDatabase method. However, any fields with a
double quote are not imported. I've specified the delimiter using a
schema.ini file. It seems like DAO treats the double quote as a delimiter no
matter what is set as the delimiter property.

One way I've worked around this is using the TransferText method but I need
code that will work with ADO and the schema.ini is only supported in ADO. The
same problem surfaces using the INSERT INTO MyTable (SELECT * FROM
MyTable.TXT). Any ideas would be greatly appreciated!
 
Hi Michael,

Do you mean (a) quote marks within the data, e.g.
123|He said "Never do that again," and walked out.|999
or (b) quote marks to qualify (in Microsoft's terminology) text fields,
e.g.
123|"London"|999
?

If (a), I don't remember encountering the problem before; please give
more details. If (b), I think you should be using these lines in
schema.ini

Format=Delimited (|)
TextDelimiter=" ;may not be needed because " is the default

The terminology is confusing, because
Format=Delimited (delimiter)
specifies the character that _separates_ each field, while
TextDelimiter=delimiter
specifies the character that "delimits" or "qualifies" text fields.

If this doesn't help, please post back with some more information about
the data.
 
John,

I appreciate the response. I am dealing with scenario a. For example I have
a field that looks like |Label A|4"x6"|. The pipe is my delimiter and I have
nothing as a textdelimiter. All text after the 4 is dropped due to the double
quote. I tried specifying | as my textdelimiter and that produces an error
since | is already my delimiter. So specifying a rare character, like ^, as
the textdelimiter seems to work (even though ^ is not anywhere in the file).
Is there a way of saying TextDelimiter<>"? Using TextDelimiter= didn't seem
to work. Thanks again.

- Michael
 
Michael,

I think it's
TextDelimiter=None

If that doesn't work, googling for
"schema.ini" TextDelimiter
should find some examples.

BTW, using a double prime - Unicode U+2033 - for the inch symbol avoids
the problem and is typographically more correct.
 
Back
Top