July 18, 2007 at 1:43 pm
How can I strip unwanted comma's from a flat file that is comma delimeted.
i.e.
John, Smith, 123 Main Street
- 3 fields
John, A, Doe, 345 Walker Street
- Should have 3 fields but the unwanted comma between FName and Lname creates 4
Any ideas how to handle this?
July 18, 2007 at 2:59 pm
Usually in cases like this I use double quotes to identify the text. All comas within quotes are recognized as part of text.
July 18, 2007 at 4:47 pm
In this paticular instance I don't have control over the source file, meaning the file isn't comma quote delimeted.
July 20, 2007 at 2:56 am
if it is not comma delimited, what is it? You need something to split the columns. You could then just replace commas with an empty string.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 20, 2007 at 6:26 am
If there is consistency in the data, that is, if the extra comma is always present, you could import the name components as multiple fields and concatenate them using a derived column transformation.
July 20, 2007 at 12:27 pm
Alternatively, if the ONLY comma issue is with the first field (in your example, the name), you could parse from the right, which would leave you only a name remaining. Then reorder the name. Then strip out any remaining commas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply