MSSQL2000: PIPE Delimited Issue

[benchpolo]

Data Row 1 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 2 -> BBB|123|ABCDE|||183 EAST WAY #A11|223|TX|CA|12
Data Row 3 -> VVV||ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 4 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 5 -> WWW|123|ABCDE|929292|QA|183 EAST WAY #A11
|223|TX|CA|12
Data Row 6 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223||CA|12
Data Row 7 -> KKK|123|ABCDE|929292||183 EAST WAY #A11|223||CA|12

As illustrated above, I have 7 data row with 10 data columns using PIPE Delimited to extract data in MSSQL 2000 DTS Process using FILE TYPE: ANSI, Row Delimiter: {CR}{LF}, Column Delimiter: Vertical Bar and Text Qualifier: <none>

ISSUE: I cannot figure out why Data Row 5 text file has a second line compare to the other data row, which is 1 line of data. I re-run the DTS process over and over again, but i am getting the same result on selected data rows only. Please help. Thanks.

[iburyak]

Data Row 1 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 2 -> BBB|123|ABCDE|||183 EAST WAY #A11|223|TX|CA|12
Data Row 3 -> VVV||ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 4 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12
Data Row 5 -> WWW|123|ABCDE|929292|QA|183 EAST WAY #A11
|223|TX|CA|12
Data Row 6 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223||CA|12
Data Row 7 -> KKK|123|ABCDE|929292||183 EAST WAY #A11|223||CA|12

As illustrated above, I have 7 data row with 10 data columns using PIPE Delimited to extract data in MSSQL 2000 DTS Process using FILE TYPE: ANSI, Row Delimiter: {CR}{LF}, Column Delimiter: Vertical Bar and Text Qualifier: <none>

ISSUE: I cannot figure out why Data Row 5 text file has a second line compare to the other data row, which is 1 line of data. I re-run the DTS process over and over again, but i am getting the same result on selected data rows only. Please help. Thanks.

Try this exercise on your table:

select * from table_name where charindex(char(10), problem_column) > 0 or charindex(char(13), problem_column) > 0

Sometimes when user enters data manually he hits Enter key at the end. You can’t see it visually in a database column but it is the same as {CR}{LF} and it could be a reason you get a broken line.
To overcome this problem, find problem values and do update on them to the same but without this last 2 characters.
Good Luck.

[benchpolo]

I was able to write RTRIM(<column>), and the {CR}{LF} was removed, but what if there's 2 || (pipe) at the end of the data? Somehow, the RTRIM doesn't apply? Please advise. Thakns.

[iburyak]

So I was right about broken line…. :)
I assume || happens when you have null in column in the middle and it is just a place holder for a column position.
Check your data and make sure it is the case.


Good Luck.

[benchpolo]

Actually, I checked the data in the user screen, and indeed the user entered the information with additional double '||' in the end. I tried the RTRIM, it works on single pipe, but not on double pipe, the row is still split into two, i'm having the user correct the data in their system. Thanks.

[benchpolo]

So, if i have a data row for example address like the one below, how would I eliminate the double pipe to a sql script.

Data: 999 Xavier Avenue #123||

This is not 2 columns it's one column the user added two carriage return in the GUI. Thanks.

[iburyak]

Is it a double-pipe or a place holder for carriage return?


If it is just a double-pipe do following :

select replace('999 Xavier Avenue #123||', '||','')

Good Luck.

[benchpolo]

how would you handle if you have multiple data row having one column with i.e. 1 pipe and the other data 2 pipe.

Example:

Data 1: 123 Westhills Ave #1|
Data 2: 144 Somers Drive #222||

I wrote the following script below to handle this, but not sure if this will take care of the double pipe in a column.

LTRIM(RTRIM(REPLACE(mc.street,'|',''))) as 'address'

Thanks.

[iburyak]

Sure it will take care of single or double-pipe.


Try this to test:

select LTRIM(RTRIM(REPLACE('Data 2: 144 Somers Drive #222||','|',''))) as 'address'

Good Luck.