SSIS REPLACE Double Quote

Question

Post reply

SSIS REPLACE Double Quote

Viewing 6 posts - 16 through 20 (of 20 total)

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

dcostelloe2012 Valued Member Points: 65 More actions · Answer 1

Use Replace

REPLACE( @[System::MachineName] , "'", "''" )

pavani.oruganti SSC Enthusiast Points: 126 More actions · Answer 2

Hello All,

Im in desperate need. Please help.

Im loading a csv file using SSIS. I have a column with | embedded in it. And this column varies in lenth and so does the occurence of | delimiter. Its only in some of my rows does a particular string appears and I need to append this string to the next column.

Im trying to use the Derived Column transformation with REPLACE function but in vain. Can someone suggest where im going wrong.

Here is my sample data.

transit|Name|edcytd|Missing, defect

Needs to be tranformed as

transit|Name|edcytd,Missing-defect

I know this is a two step process. I can use concatenate function Expr1+"-"+Expr2 to merge the two strings. How do I seperatethe needed string from the original?

Thanks

Pallavi

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Don't you have a text qualifier defined?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pavani.oruganti SSC Enthusiast Points: 126 More actions · Answer 4

I tried that but really did not see any difference in how the data is read.Also, this delimitter is only in one column and I need them to be present in the table as well.Its only the occasional last part of the string to be appended to the next column.

I also read a post on adding text qualifier. I understood that it helps removes the unwanted special characters in the beginning or at the end of the column. Please correct me if wrong.

Thanks

Pallavi

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

If this is your input data

transit|Name|edcytd|Missing, defect

you'll need to add text qualifiers to the input like this:

"transit"|"Name"|"edcytd|Missing, defect"

That way, you'll be able to distinguish the last | as part of the data, instead of a delimiter.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

lokesh.hyperion@gmail.com SSC Enthusiast Points: 115 More actions · Answer 6

I have spent almost a day trying to replace double quotes in SSIS derived column using replace:

My input file has header and I need to replace EXTRA double quotes with null in column F.

Input file:

#A|B|C|D|E|F|G

"2023-02-20"|"ABC"|"123"||"4 - Stop Doing"|"UGG"|"1234"

"2023-12-20"|"ABC"|"456"||"3 - Change Offers"|"1234-28" "|"1234"

"2021-12-20"|"ABC"|"714"||"3 - Change Offers"|"0076-26" "|"1234"

"2020-12-20"|"ABC"|"717"||"3 - Change Offers"|"12543-26" "|"1234"

"2021-12-20"|"ABC"|"713"||"3 - Change Offers"|"353-26" "|"1234"

"2023-12-20"|"ABC"|"246"||"3 - Change Offers"|"1-26" "|"1234"

Desired Output:

"2023-02-20"|"ABC"|"123"||"4 - Stop Doing"|"UGG"|"1234"

"2023-12-20"|"ABC"|"456"||"3 - Change Offers"|"1234-28"|"1234"

"2021-12-20"|"ABC"|"714"||"3 - Change Offers"|"0076-26"|"1234"

"2020-12-20"|"ABC"|"717"||"3 - Change Offers"|"12543-26"|"1234"

"2021-12-20"|"ABC"|"713"||"3 - Change Offers"|"353-26"|"1234"

"2023-12-20"|"ABC"|"246"||"3 - Change Offers"|"1-26"|"1234"

I have used:

REPLACE(trim(F),"\"\"","\"\) it did not work for me

REPLACE(F,"\" ","") it did not work for me