Trouble importing csv with flat file source

Trouble importing csv with flat file source

• Håkan B

  According to the documentation, the flat file source cannot parse an escaped embedded text qualifier.  (I don't know about non-escaped embedded qualifiers, but I wouldn't be optimistic.)
  
  This means that if " is your text qualifier there better not be any in the data.
  
  I got around this by using a flat file source with one string column (the entire line), and a script transformation to parse it into columns.  It's a pain to have to write your own parsing code, but I don't know any other way.
  

• Vomish

  Hi
  
  I've been having a similar problem with SSIS using a flat file source to import a CSV file. The text qualifier is set to " but the columns still seem to be splitting on delimiters found within the text field, i.e.
  
  1, "A", X        is fine and splits into 3 columns, but
  2, "A,B", Y     splits into 4 columns because of the comma within the text
  
  Since Excel, Access, DTS, etc. all import the file as one would expect, I imagine that I'm making a very simple error somewhere - although I would expect the default behaviour to be similar to the other packages.
  
  Regards
  
  Craig
  

• vijji

  Craig, I can't reproduce the failure you're encountering.  Here's what I tried:
  
  I created an ANSI text file in notepad with the following contents:
  
  1,"A",X
  2,"A,B",Y
  
  Then I configured a connection manager with:
  Locale:  English (United States)
  Code Page: 1252
  Format:  Delimited
  Text Qualifier: "
  
  What are you doing differently
  
  

  
  
  

• Piyush Soni

  Craig, have you tried removing the spaces after the commas

  
  
  

• Suddhasatta

  The spaces only exist in the above example for clarity, not in the actual file (sorry for the confusion) which was in fact created by using the import/export wizard in SQL2000. The general issue is that the parser seems to be picking up column delimiters within qualified text, which I've not seen in any other data processing tool.
  
  Craig
  

• Dave_S

  Seems like bit of step backwards. Why not log it via MSDN Product Feedback.
  
  As a workaround turn off the text qualifier and tweak the column delimiters in the Advanced tab of the connection. Set the preceding column  delimiter to be {,}" and qualified column to be "{,} or "{CR}{LF}
  
  It is not perfect and may break if you have that pattern embedded as you will not look for the escape character.
  
  Assuming any embedded double-quotes have been escaped, (doubled), then you could clean this in the pipeline, try a Derived Column set to replace the column, using the replace function, e.g.
  
  REPLACE([Column 1], "\"\"", "\"")

  
  
  
• Trouble importing csv with flat file source