Handle Carriage return in a Column using Flat File?

  • Hi,

    I have a table, which has Comments Column. In that i am getting Carriage return. I have used row delimeter as comma separater and column delimeter as {CR}{LF} [Carraige return]. While using the Flat File Connection Manager, this Comment column comes as two separate rows rather than single row.

    How can be handle this in SSIS?

  • Does the comments column have a text delimiter? I mean, is it surrounded by quotes, perhaps?

    Without this, I cannot see how SSIS could ever distinguish between a new row (CRLF) and an embedded CRLF.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • It is the plain text. No quotes in between. It is something like below...

    This is

    sample text.

  • Then you are up a certain creek and short on paddles.

    As I mentioned, how can SSIS distinguish between an embedded CRLF and an end-of-row CRLF?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Even I really don't know how to check the identity of these. Also that is what my clarification is....

  • I just read your original post again and maybe I misunderstood what you are doing.

    Are you trying to export to a flat file or import from a flat file?

    If you are exporting, you should be able to run a REPLACE on the text in the column to get rid of CRLFs before you do the export.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phil.

    I am importing data from a flat file. In my flat file the text data is something like This is

    sample text

    This Flat file comes from a third party tool in which we are not able to do anything. Either we have to do something using code or in thr flat file connection manager i suppose. Please advice.

  • If your data looks like this (for example):

    ID, Text, Date

    1, Good record, 2011-01-01

    2, Bad rec

    ord, 2011-02-02

    3, Another good record, 2011-01-3

    4, A very bad

    bad

    bad

    record, 2011-04-04

    You are going to have to pre-process the file before SSIS can handle it. Whether you can come up with some logic to remove the unwanted CRs depends on the structure of the file (eg, in the above, any line which does not begin with a number is definitely one which needs processing).

    By the way, I think that embedded commas will also cause you pain and you may have to include logic to replace these too in your code.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phil. Let me try as you suggest.

  • Good luck and happy coding! 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply