Problem with Importing CSV with "=" inside a field value

[Herbert Chan]

Hello,

I have a csv file that I want to get linked into a database. I have
Microsoft Office 2007. I use Excel to open it and everything is fine. I
try using Access to link it directly. I've been tinkering with the schema
and have overcome a number of obstacles. But there is this one that I
cannot overcome.

There is a field with the following kind of value:

78HB2035801
="78792000001"
="78792090101"

For some records, it has an equal sign at the front and wrapped by quotes.
For some there is no equal sign nor quotes.

Access can process the first kind of value (without anything in front and at
the back) for the field. When it encounters the second kind (with equal
sign and quotes), access can only extract = as the value of the field, and
then the rest of the fields after this problematic field all become blanks.

How should I treat such kind of values to enable Access to process every
fields in the record. This problematic field actually is of no use to me.
Any way to skip parsing this field such that Access can extract the fields
that I want?

Herbert

 

[Roger Carlson]

Sometimes when I have a problematic import, I will write my own import code
rather that rely on the Wizard. I use low-level IO to open the file, read
each line with the LineInput statement into a string variable, then parse
the string in code.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImportLineInput.mdb" which illustrates how to do this. It's
not exactly what you need, but it should get you started. You can find the
sample here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=340

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jellifish]

I would import the values as text and sort them out in the table using
replace functions in update queries.

 

[Herbert Chan]

Thank you for your suggestion. I've adopted your suggestion and written a
subroutine to just extract the 2 columns that I need. It's very quick, and
above all, accurate as I can control how to extract the data.

Thank you very much indeed.

Herbert