How to deal with Multiple Delimiter in a single csv file using SSIS

bstar_ 61 Reputation points

i have a single csv that contains data with multiple delimiter.
I tried to import it as a flat file into SSIS, but it was unable to split successfully using data flow task
Planning to export it as csv with comma delimiter

Sample data of two record:


I tried using the following values:

  • text delimiter: ""
  • row delimiter: {,}{,}
  • column delimiter: {|}

SSIS splits the one record into 2 rows which is not what i wanted.
It keeps separating one record into two records. it split at value "test"
Any help is greatly appreciated

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,090 questions
{count} votes

Accepted answer
  1. Monalv-MSFT 5,776 Reputation points

    Hi @bstar_ ,

    Could you please share the desired output?

    Please refer to the following pictures:

    Best regards,


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

3 additional answers

Sort by: Most helpful
  1. bstar_ 61 Reputation points

    Hello @Monalv-MSFT !
    Thanks for replying.

    I followed your instructions.
    It looks good at the preview tab, but i have an error when i tried to export the data.


    0 comments No comments

  2. Tom Phillips 17,636 Reputation points

    Nothing will be able to import a delimited file with the row delimiter occurring in the middle of the row. You will need to provide a properly formatted delimited file.

    If your file contains CR/LF in the middle, I suggest when you generate the file, you replace the CR/LF chars with "{CR}{LF}". Then after import, REPLACE(string,"{CR}{LF}","\r\n").

    0 comments No comments

  3. bstar_ 61 Reputation points

    Hi @Tom Phillips ,
    have re-generated the file with properly formatted delimiter file.
    It works now with @Monalv-MSFT solution.
    Thanks both!

    0 comments No comments