create array from messed up comma list?

[mtm81]

hi,
I've been given a csv file to convert into an array.. all comman delimited.

thought it would be easy.

Problem is.. each record isn't seperated by a line break!

What actually happens is the record goes across three line breaks, and then onto the new record!

So my plan was to first remove the actual line breaks...

then re-make a new array for each record then only have one line break before the next record.


I've got the breaks removed.. so I thought I would count how many individual fields there are per record.. count up that .. then add a line break and move the next.

this is where I'm stuck!


I know there are 94 individual fields per record.

(originally it was something like 68 on the first line, then the rest on the second line and a couple of stragglers on the third line, then onto the new record..)

Can anyone help getting this corrected?

[mparker1113]

Why not just create the array using the original line breaks. Then when you loop through the array, ignore all indexes which have a null value?

[mattyblah]

Can you post part of the file so I can get a feel for how it is structured? My initial response would be to read the file into a string, and run this replace:
replace(strFile, "," & vbcrlf, ",")

That should replace all instance of a comma followed by a new line. That might help.

[mtm81]

ok the file looks something like this

field1, field2, field3, fields4,
field5, field6,
field7
field1, field2, field3, field4,
field5, field6,
field7


so normally for a csv you'd expect all of those fields to be on one line, so you could do a split by vbcrlf...

so what I've done is first remove all the line breaks so I now have a string of:

field1, field2, field3, field4, field5, field6, field7, field1, field2 etc etc

Therefore I know that each "record" has seven fields.. so I need to loop through the above string and every seventh loop add a new vbcrlf..

this will hopefully give me a string which looks correct..