Problems with Carriage Returns

[dhulbert]

I'm importing a number of csv files via SSIS into my database and for the most part the imports work find but some some rows have Carriage Returns in the middle fo a filed and it's causing the dat aloads to fall over. The field is " delimited as it's a text field but any ideas how I can deal with these rogue carriage returns.

Data Sample

 20060601, "25/05/06 as per sec {cr} {cr} 1/04/06 as per sec", 3242324, 2313242

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Catadmin]

You need to take your import and search it for these carriage returns where they are not at the end of the string. You could probably do it in a Script Task. Not sure if you would need to import your file into a temporary (or staging) table before you do the search.

Also might be able to use an Execute SQL Task. T-SQL has commands where you can search strings for patterns. PatIndex or CharIndex could help you out. Then you delete the ones not at the end of a string and process what's left in your file.

I haven't done this before, so I don't have exact instructions, but I'm pretty sure this method would work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"