Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Default MultiLine CSV Cells Revisited

    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.

  2. #2
    Join Date
    Oct 2007
    Location
    Madison, WI
    Posts
    7,458

    Default

    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.
    Aaron P Scooter Software

  3. #3
    Join Date
    Aug 2011
    Posts
    3

    Default

    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.

  4. #4
    Join Date
    Oct 2007
    Location
    Madison, WI
    Posts
    7,458

    Default

    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.
    Aaron P Scooter Software

  5. #5
    Join Date
    Aug 2011
    Posts
    3

    Default

    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.

  6. #6
    Join Date
    Oct 2007
    Location
    Madison, WI
    Posts
    7,458

    Default

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

    I'm glad the workaround works well for you.
    Aaron P Scooter Software

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •