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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy