CRLF in middle of comma delimited file 1

[Swi]

Hi,

Many users like to put a CR or LF in Excel to split out two address lines.

Ex. -
123 Any Street
Apt. 123

This is fine when importing as I normally read files with an EOR marker of CRLF, pretty standard.

However, now I am receiving a file where the address lines within a field are delimited by a CRLF in the middle of the file.

It seems that ADO reads this just fine:

Set connCSV = New ADODB.connection
 connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ProcessFolder & ";Extended Properties=" & Chr$(34) & "text;HDR=Yes;FMT=Delimited;IMEX=1" & Chr$(34) & ";"
Set rsCSV = New ADODB.Recordset
rsCSV.Open "SELECT COUNT(*) AS [Qty] FROM [" & U_pageflexDataFile & "]", connCSV, adOpenStatic, adLockReadOnly, adCmdText

The code above it just checking for the quantity however, what is the best way to open a CSV file like this and write it back out so another software can consume is handily? The other software does not like the CRLF in the middle of the file whether the field is enclosed by quotes or not.

Attached is a snippet of the file. Thanks.

Swi

 

[combo]

Csv format has its standard, the parser should obey the rules.

combo

 

[Andrzejek]

Looks to me Excel uses [tt]Chr(10)[/tt] to enter a new line in a cell:
[tt]"222 Test Way" & Chr(10) & "#2"[/tt]

and Excel saves this data as CSV as:
[tt]"222 Test Way [blue]LF[/blue]
#2"[blue]CRLF[/blue][/tt]

Somewhere along the way LF is being replaced with CRLF

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Swi]

Correct, not sure of the source of the data as it is being uploaded to our server but multiple locations. It does seem like a CRLF in the middle of a record is acceptable as long as the field is enclosed by quotes. My question is what is the best way to handle this? Import using a schema and ADO and then write out the files replacing the CRLF with a space?

Most software programs and text editors do not handle this well other than Excel.

Swi

 

[Swi]

I would like to take the client I put, replace the CRLF in the middle of records with a space and then write the file back out in an automated fashion. This is being fed to a software that is not handling the CRLF properly

Swi

 

[Andrzejek]

From your CSV example it looks like your lines of text (records) contain 10 elements, if you [tt]Split()[/tt] them by a comma (0 to 9). So, if you read your CSV line-by-line, Split() every line and check how many elements you have. If you have less than 10 and next line also have less than 10 elements, that may mean those 2 lines (records) need to be Join() together.

Doing it this way also means that you need to accommodate a comma that is not a delimeter but a part of the data: "Bob, John and Susie" :-(



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Swi]

Thanks Andrzejek.

Maybe I just load the data into an ADO recordset and loop through it removing any CR of LF characters using a schema so all data is treated as text.

Just wasn't sure if there was a better/cleaner way. Thanks.

Swi

 

[Andrzejek]

Or maybe you can just read the CSV file (it is just a simple text file after all) and rewrite it getting out unwanted CRLF when the last element of [tt]Split()[/tt] starts (but does not end) with a double quote "

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Swi]

Good idea as well.

Swi