Geek Answers Handbook

SQL 2005 CSV import quote enclosed with internal quotes and commas

I have a quote delimited CSV file. Most of the 90,000 lines are great, but I have a few lines that contain a text box containing both a quote and a comma. For example, a field value would be:

AB ", AB

When "Delimited," it becomes

"AB" "AB"

When SQL 2005 tries to import this, I get errors like ...

  Messages
 Error 0xc0202055: Data Flow Task: The column delimiter for column "Column 4" was not found.
  (SQL Server Import and Export Wizard)

This only happens when the quote and the comma are in the text value together. Type Values

AB "AB, which becomes" AB "AB" or AB, AB, which becomes "AB, AB"

works fine.

Here are some examples of strings ...

  "1464885", "LEVER WM", "", "B", "MP17"
 "1465075", ": PLT-BC !! NOTE !!", "", "B", ""
 "1465076", "BRKT-STR MTR! NOTE!", "", "B", ""
 "1465172", ": BRKT-SW MTG! NOTE!", "", "B", "MP16"
 "1465388", "BUSS BAR! NOTE!", "", "B", "MP10"
 "1465391", "PLT-BLKHD" "NOTE" "", "", "B", "MP20"
 "1465564", "SPROCKET: 13TEETH, 74MM OD, 66MM", "ID W / .25" "SETSCR", "B", "MP6"
 "S01266330002", "CABLE: 224" ", E122 / 261.8 CO", "", "B", "MP11"

The last line is an example of a problem - "" causes an error.

+6
import sql-server sql-server-2005 csv
source share
4 answers

I had BASIC problems with SSIS. What Access, Excel, and even DTS seemed to do very well, SSIS is suffocating. Record length variable data is another problem, but yes, these built-in qualifiers are a serious problem. Especially if you do not have access to the import files, because they are located on a foreign server that you pay to get access, and even from 4 to 5 GB in size! I can’t just “replace everything” with every import.

You can check it out on Microsoft Downloads by calling " UnDouble ", and here is another workaround you can try.

It appears that with SSIS in SQL Server 2008, the error still exists. I don’t know why they didn’t access this in the parser, but it looks like we returned on time with SSIS in the main import functions.

UPDATE 11-18-2010: This error still exists in SSIS. Amazing

+4
source share

How about just:

  • Search / replace all "", with ''; (fix all broken fields)
  • Search / replace everything; ''; s, ", (so that" unfix "is correctly empty fields.)
  • Search / replace everything ''; ''; with "", ", (so that" unfix "is correctly empty fields that follow the proper encapsulation of the built-in delimiters.)

This will convert your original to:

"1464885","LEVER WM","","B","MP17" "1465075",":PLT-BC !!NOTE!!","","B","" "1465076","BRKT-STR MTR !NOTE!","","B","" "1465172",":BRKT-SW MTG !NOTE!","","B","MP16" "1465388","BUSS BAR !NOTE!","","B","MP10" "1465391","PLT-BLKHD ""NOTE""","","B","MP20" "1465564","SPROCKET:13TEETH,74MM OD,66MM","ID W/.25"" SETSCR","B","MP6" "S01266330002","CABLE:224'';E122/261,8 CO","","B","MP11"

Which seems to run the glove in SSIS. You may need to repeat the 3rd step to account for 3 empty fields per line (''; '' ';' '; etc.), but the bottom line here is that when you use the built-in text qualifiers, you you must either run away from them or replace them. Let this be a lesson in your CSV creation processes.

+1
source share

Microsoft says double double quotes inside fields with double quote delimiters just don't work. The correction is scheduled for the end of 2011 ...

At the same time, we will have to use workarounds as described in other answers.

+1
source share

I would just search / replace for "and replace it with

Do you have access to the source file?

0
source share

More articles:


All Articles