parsing a csv file

[Henc]

Hello,

I would like to parse a csv file before I import it into a table. The file is in csv format with one problem: Certain snippets of text also contain a double-quote character to denote inches. When I import this as a table, I get a bunch of import errors which I then go through manualy to remove the double quotes and replace them with two single quotes. Is there a way that I could have some VB script using regular expressions to run through the file and then import the data? I would really like to keep all of this in MS Access if possible.

Thanks in advance!

 

[Stickarm]

If the only occurance of the double quotes is to denote inches. Then when you import the file, in the import specs make sure the text qualifier is set to {none}. Hope this helps

Phil

 

[MichaelRed]

An alternative might be to 'grab' the entire file into a single string variable and parse it in memory for the REPLACEMENT[/b[ of the Double quote. You could then use SPLIT itteratively to seperate the fields and finally do any (additional) V&V on the seperate fields before appending to a recordset. This is probably a bit more code, but if you do not have 'Bloody Fingers' syndrome, it can be very flexible in the set-up and quite fast in the execution.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Henc]

Stickarm, your reply is noted, but I guess I forgot to also include that the csv file has the text delimited by double quotes.
Ex: 200,10,"Asphalt(10" deep)",20,50
Therefore, Access sees this as being seperate fields and then doesn't know what to do with the leftovers.

MichealRed, I would like to parse it in memory as you say, but how do I remove the double quotes which are not preceded by a comma and not followed by a comma from the ones which "stand alone?" As in the example above, I would want it to strip the double quotes which denote inches and replace them with two single quotes to read:
200,10,"Asphalt(10'' deep)",20,50

Thanks for the help...

 

[MichaelRed]

wheather ytou parse the string in mem or through the read-write of disc file, the issue of what is a pair of quotes remains.

It is unique to each situation, so is not really amenable to a generic soloution. IF your structure is regulaar, you might be able to just count the double quotes in the line. If there is an odd number, you could check for a preceeding numeric value, and where found - remove that "double quote".

Without a careful analysis of the file(s) I cannot be of any more help.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jclarey]

Henc:

Did you ever get this embedded double-quote problem resolved?

I'm having the same issue and just created a new post.

Jeff Clarey
Appleton WI