Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    22

    Unanswered: SSIS and CSVs with quotes

    Hi folks,

    I have a CSV with some columns containing quoted text with the text itself containing quotes.

    I'm trying to use a Flat File Source in SSIS. I've set a double quote as the text qualifier (") and comma as the delimiter. The trouble I'm having is getting to text that contains a double quote like in the following example;

    123,"xyz","Don't you hate when MS say's it's a "feature" and not a bug","more data"

    SSIS is seeing the first quote as a text qualifier and the second quote as the end of the text. Because the next delimiter is not found (in this case a comma), it returns;

    [staging_output [4195]] Error: The column delimiter for column "Column 3" was not found.

    Any ideas on how I can get SSIS to treat the whole column as a single piece of text that just happens to contain quotes?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your CSV file is in an invalid format: Comma-separated values - Wikipedia, the free encyclopedia

    You must double up your embedded quotes.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2009
    Posts
    22
    Quote Originally Posted by gvee
    Your CSV file is in an invalid format: Comma-separated values - Wikipedia, the free encyclopedia

    You must double up your embedded quotes.

    If it was my CSV it wouldn't be an issue, but unfortunately it isn't and I don't get to dictate the format. It actually gets worse...there are 104 different tables with up to 220 columns all in the one file.

    My approach it to use a Flat File Source, a Conditional Split to split by table, a Data Conversion to strongly type the data, and finally a SQL Server Destination.

    I seem to have hit a stumbling block on this quote issue. I hate getting held up on stupid little things, but isn't it always the way. I swear I spend more time on stupid little things than on the REAL technical challenges.

  4. #4
    Join Date
    Jun 2009
    Posts
    22
    UPDATE: Sorry, I've just checked my source again and it is actually double quoted. So the data in fact looks like this;

    123,"xyz","Don't you hate when MS say's it's a ""feature"" and not a bug","more data"

    It's not stopping SSIS from failing though. This is such a common thing, I'm sure the answer must be simple, but for now it's illuding me.

  5. #5
    Join Date
    Jun 2009
    Posts
    22
    Ok. It seems that there is no way to get Flat File Connection to deal with a CSV with embedded quotes.

    I have been able to deal with the problem by using an OLE DB Connection with the Microsoft Jet 4.0 OLE DB Provider. This seems to be far better at dealing with CSVs. Be aware that you may also need to change some registry keys for the Text ISAM driver depending on the layout of your CSV. Being that I couldn't strict data type my data just yet, I had to ensure that the driver kept everything as text and didn't scan x number of rows to determine data type.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Why use SSIS to import a CSV, just use a select with OPENROWSET, avoid the whole SSIS thing.

  7. #7
    Join Date
    Jun 2009
    Posts
    22
    I have a very complex set of transformations to perform on the data before loading a data warehouse. Why on Earth would I want to "avoid the whole SSIS thing"?

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by scorpioTiger
    I have a very complex set of transformations to perform on the data before loading a data warehouse. Why on Earth would I want to "avoid the whole SSIS thing"?
    How are you doing those transformations ? via sql blocks, sprocs, etc ?, A Stage then Load type of scenario ? I prefer to keep processes lowest level as possible, ie if I am simply staging a file/data, then performing sql operations on that data and then loading to final destination tables why get other interfaces involved ? To me it makes troubleshooting all that more difficult. Just a personal preference. I guess just a leftover from having to do things with only ksh, perl and BCP/SQLLoader,
    Last edited by PMASchmed; 07-02-09 at 12:02.

  9. #9
    Join Date
    Jun 2009
    Posts
    22
    The source CSV contains output from multiple tables, so I have to start with 220 text columns and perform a conditional split. From there I can start data typing. I have to stage the data and then load a data warehouse including managing slowly changing dimensions. All of this has to be appropriately managed and logged. SSIS is simply the tool for the job.

  10. #10
    Join Date
    Nov 2011
    Posts
    1
    I'm experiencing the same issue and was wondering was there a ssis solution found before I start scripting one?
    coldsurfer

  11. #11
    Join Date
    May 2012
    Posts
    1

    Easy solution?

    I hit the same issue also using SQL 2008/VS2008. On the wizard of the import you need to specify the Text Qualifier by default there is none. Place the double quotes in the box and problem is solved. Hope it helps.

    Cheers

  12. #12
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Quote Originally Posted by scorpioTiger View Post
    UPDATE: Sorry, I've just checked my source again and it is actually double quoted. So the data in fact looks like this;

    123,"xyz","Don't you hate when MS say's it's a ""feature"" and not a bug","more data"

    It's not stopping SSIS from failing though. This is such a common thing, I'm sure the answer must be simple, but for now it's illuding me.
    Sorry probably a stupid question but have you tried "'DataHere'"

    Instead of doubling up your quotes as "" data ""
    have double quote, single, data, single, Double

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •