Misplaced commas in a comma delimited text file

  • 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.

  • 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

  • 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

  • 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.

  • In the Flat File Connection Manager, I set the text qualifier to double quotes (""). It had no affect. Am I doing something wrong?

  • 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