Import Delimited CSV file with over 255 fields

[mjseim]

Let me preface this question with... I DID NOT CREATE NOR DO I HAVE ANY CONTROL OVER THE FILE I'M NEEDING TO IMPORT INTO ACCESS.

I've got a situation where I'm needing to normalize a delimited .CSV file on a routine basis. The .CSV file has 369 fields. When normalized correctly, the true data should only be about 60 fields.

I didn't think this would be such a hard thing... just import the first 255 fields into one table, and the remaining fields into another table. Then, using a query... normalize the database as necessary.

I've scoured this topic all over... I've seen solutions for "fixed width" files, but not delimited. The only helpful thread I've found says that this is possible only through very complicated parsing through the file.

That's where I'm stuck... This is definitely over my head. If anyone has any help on this I sure would apprecaite it.

 

[gemma-the-husky]

have you tried doing this the easy way

i have looked at help, but cant be sure - is an access table limited to 255 columns?

i just saw that an excel sheet has 256 columns
------------

if so then i assume what is being said is to treat the csv as a text file,
import it a line at a time which definitely wont be a problem, and then parse it by hand

ie if you want column 64, find the 64th and 65th comma or tab, or whatever the delimiter is, and read whats between.

a bit more awkward, but still not too hard.

 

[mjseim]

Good point... however, I'm using Access 2003. Access 2003 has a limit of 255 fields.

If I understand you correctly, your "treat it like a text file and parce it by hand" comment is what I'm currently attempting. First glance, it looks like it might work... just using a lot of "InStr" "Replace" "Mid" and "Left" functions.

Maybe this is an easier question: is there an easy way to identify "apples" in the following string?

^oranges^grapes^bananas^apples^lemon^lime^

I'm thinking that if there is a repeatable way, then I could repeat the function for each field... then, once all the fields are properly established into two tables I could normalize after that.

 

[ajetrumpet]

upload a picture or something MjSeim, and somebody might have a good answer for you...

 

[boblarson]

For this project I would use a text input instead of trying to import. So, you open the text file and read through it line by line and split it into fields as you go line by line. Look into

Open strFile For Input As #1

and then looping until EOF(1).

There are some examples I've posted on the site (just can't look for them right now).