April 13, 2011 at 7:02 pm
I have a text file with data that looks like this:
"000","COMPUTER CENTER #19009","COMP SYSTEMS, INC. ","327 RANDOM AVE SW ","RANDOM CITY ","VA","240165212","US","5409896000","VA","24016","C00638 ","PRODUCT COMFORT TYPE ACCS", 1
How can I structure the flat file connection to deal with the comma inside the column? I don't want to have to fix these rows manually.
April 13, 2011 at 8:55 pm
When you configure the flat file connection - set it up so text columns are defined with double-quotes. That will tell SSIS to ignore embedded commas in the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2011 at 12:16 am
Another option is to use a real delimiter, instead of the comma, as the comma is too widely used to be efficient as a delimiter.
You could use the pipe | as it is not widely used in text, or even better, a combination of multiple characters, such as |$.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 14, 2011 at 10:50 am
You're right, a pipe character would be a superior delimiter and I wish I could use it. Unfortunately, I have NO control over the format of the source file.
April 14, 2011 at 11:29 am
In the Flat File Connection Manager, I set the text qualifier to double quotes (""). It had no affect. Am I doing something wrong?
April 14, 2011 at 11:35 am
I think you're supposed to enter only one double quote: "
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply