Replace a carriage return in SSIS

Question

Post reply

Replace a carriage return in SSIS

Data9

SSC-Addicted

Points: 453
More actions
July 23, 2014 at 3:36 pm

#293691

Hi All, I have a flat file with data as below:
row delimiter: {CR}{LF}
column delimiter: | -- Pipe
Some times the data is being messed up and the pipe is moving to next line. please see below:
1|test|1234.45|testing|test123|
2|test|1234.45|testing|test123|
3|test|1234.45|testing|test123|
4|test|1234.45|testing|test123
| -- error here
5|test|1234.45|testing|test123|
6|test|1234.45|testing|test123
|
7|test|1234.45|testing|test123|
8|test|1234.45|testing|test123|
Please suggest me the way I can move the pipe to previous row. I am thinking we should replace "{CR}{LF}|{CR}{LF}" with "|"
Thanks in adv

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What about using "|{CR}{LF}" as a column delimiter?

Then you can remove the additional carriage return on your last column in another step.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Data9 SSC-Addicted Points: 453 More actions · Answer 2

Luis, But only some rows will have the pipe symbol followed by carriage return not all. please see my commnets below

1|test|1234.45|testing|test123| ---|{CR}{LF} will work

2|test|1234.45|testing|test123| ---|{CR}{LF} will work

4|test|1234.45|testing|test123 ---|{CR}{LF} will NOT work

| -- error here

6|test|1234.45|testing|test123

|

Appreciate your responce

rxm119528 SSCrazy Points: 2960 More actions · Answer 3

Are you trying to do a export or import on this flat file.

Can you post the flat file as an attachment.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Data9 (7/24/2014)
Luis, But only some rows will have the pipe symbol followed by carriage return not all. please see my commnets below
1|test|1234.45|testing|test123| ---|{CR}{LF} will work
2|test|1234.45|testing|test123| ---|{CR}{LF} will work
3|test|1234.45|testing|test123|
4|test|1234.45|testing|test123 ---|{CR}{LF} will NOT work
| -- error here
5|test|1234.45|testing|test123|
6|test|1234.45|testing|test123
|
7|test|1234.45|testing|test123|
8|test|1234.45|testing|test123|
Appreciate your responce

Line 4 will work because it will end in the next row. Your last column will include a carriage return. You can remove it with a derived column. Additional to that, you will get an additional column that you shouldn't include in your flat file source. Other than that, the test I did, ran with no problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2