Reg Exp or parsing CSV ->db

[nzgirl]

I'm writing from a csv file to a table.
I've got the split on comma working fine
EXCEPT when my users have a comma within a field.
In this case the field is actually delimited by
," and ",
rather than by
, ,
But I can't figure out an efficient way to do this...
using regexp I can find and replace those expressions(,"",) but that doesn't help as I still have to split by only one option.

Any ideas?
Thanks

 

[bgreenhouse]

As it seems you have some flexibility, couldn't you just delimit all the fields with ," and ",?

Maybe I'm missing something...


B

 

[nzgirl]

I have to take a standard CSV file... from Excel. So I can't define the delimiters...
I need to ignore the commas in between the ," ", marks...

?

 

[bgreenhouse]

I'm thinking out loud here...You're using VBScript, right...

How about taking the substrings between the quotation marks, replacing all the commas within them with some other type of marker (I don't know, make one up like &comm, then putting them back in place into the csv file before processing it...

Any help?

I could think about code tomorrow morning if you need it ).

B

 

[dilettante]

Well... Why reinvent the wheel?

Why not use either the Tabular Data Control or ADO with the Microsoft Text Driver?

Both of these were designed to parse out data from CSV files, in particular for a file of the same rows over and over.

Of course if you have something special going on here (record 1 in one format, records 2-5 in another, 6-n yet a third) you'd need a custom solution.

For that matter, if you are getting Excel data why not just read it out of the xls file using ADO?

 

[nzgirl]

Thanks for the ideas... (and reply.. very heartening!)
I just don't know how to replace the commas within the ""...
(feeling rather stuuupid!)

This has been driving me crazy for a while now..
now

http://www.4guysfromrolla.com/webtech/120400-1.3.shtml

had something useful with regular expressions but I can't hack the look ahead to do what I want. I can use it to replace the entire portion between the ,"", or to replace the ,"", but not the commas within the ""

 

[bgreenhouse]

Hey - you don't see me posting it off the top of my head, do you ;o) ? I'll take a look and see what I can come up with in the morning.

B

 

[nzgirl]

dilettante:
Of course I don't want to reinvent the wheel if possible.. but my file will be a different size every time with a different number of columns.
Is this possible using either of your solns?
Thanks

 

[nzgirl]

I'm still stuck on this...
I posted to a regexp board and the best answer I got was 'maybe your verison of regexp is old' cause I can't get lookahead (?= to work!!
Has anyone else come across this problem??

Thanks

 

[lucasm]

Can you give us a few lines of sample data (with the quote delimited commas included)?

 

[nzgirl]

Well I finally found a workaround. Its not pretty though.
Basically my data looks like
col1, col2, col3,col4 vbCrLf
col1,"co,l2",col3,col4 vbCrLf
col1, col2,"c,o,l3",col4 vbCrLf
col1, col2, col3,col4 vbCrLf
and I can't replace the actual column delimiters (User is to upload a CSV, we read it and create a table).
So What I have now =
For i=0 to NumRows
posx = instr(arrayfile(i), chr(34))
While posx > 0
posy = instr(posx+1, arrayfile(i), chr(34))
if posy > posx then
Original = mid(arrayfile(i), posx+1, posy-posx)
NewO = replace(Original, ",", ";&quot
arrayfile(i) = Replace(arrayfile(i), Original, NewO)
posx =instr(posy+1, arrayfile(i), chr(34))
else
posx = 0
end if
wend
if posy > 0 then arrayfile(i) = replace(arrayfile(i), chr(34), " &quot
Next

But any improvements would be appreciated!