MultiLine CSV Cells Revisited

[scheperw]

I'm trying to compare "CSV" files that have formatted text in columns. This results in line feeds (not sure if there is a carriage return) in the cells. I've read the earlier posts about changing the Line Ending format in 3.2 I think but can't navigate to these settings on 3.3.1. Please assist.

[Aaron]

Hello,

Are you comparing your CSV files in the Data Compare session type?

Do you have Quotes or another delimiter around the formatted text that should be in a single column? If so, you can define the Text Qualifier in the File Format's Type tab to make the whole string appear in a single column/cell.

[scheperw]

Yes we are using Data Compare session. The CSV format is for Sybase IQ. It doesn't allow use of Quotes unless every column is quoted. Quoted columns with quoted data was behaving squirrelly as a result we are avoiding use of quotes. We are using ^ as the delimiter character as it isn't seen in our data. Every column is bounded within the delimiter ^ including the final column. Sybase IQ parses the multi-lines properly and loads data between delimiters. Apparently it is counting the columns rather than using the end of line.

[Aaron]

Hello,

Would it be possible to define your files in a way so that each column is delimited with a character (such as , or |) and then every column is also quoted with another non-quote character (such as ^)? This would allow you do define the delimiter of the file format as , and the text qualifier as ^, which would then swallow the line breaks within the text within the column.

[scheperw]

Aaron,
I was able to combine the use of ^ with Quote to produce comparable files with mult-lines. I can verify the data with Quotes and then switch the Quotes off in the ETL process.

This is a good work around and allows me to validate my work. If BC can be enhanced to handle the lines without the Quotes using only the delimiters it would be more compatible with the load files generated for Sybase IQ. This has the advantage of testing production files as they will be created.

[Aaron]

Thanks for the suggestion. I'll add it to our Customer Wishlist.

I'm glad the workaround works well for you.