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

Question

Hi,
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:

"ID-001|""abc""|""s1234""|""ab"|{CR}{LF}"test""|""others"""{CR}{LR},,
"ID-002|""abc""|""s4567""|""ab"|{CR}{LF}"test2""|""others2"""{CR}{LR},,

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

Answer 1

Hi @bstar_ ,

Could you please share the desired output?

Please refer to the following pictures:

Best regards,
Mona

----------

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.

Answer 2

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.

DTS_E_PRIMEOUTPUTFAILED

Answer 3

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").

Answer 4

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