We help IT Professionals succeed at work.

SQL Server import - " in column causing problems

i have a CSV file that I'm importing into SQL Server 2008 R2. In the Flat File connection manager i have, Format Delimited; Text Qualifier "; Header row delimiter {CR}{LF} Header rows to skip: 0;
and on the field causing the problem i have TextQualified True
the problem is -- i come to one set of data that looks similar to this:

this is the data that goes into "The problem Field"  and it continues, with this information.

the import look's like
Field 1                                                                                                                          
this is the data that goes into "The problem Field"  and it continues    

Field 2
with this information.      

what is SHOULD LOOK LIKE is

Field 1
this is the data that goes into "The problem Field"  and it continues, with this information.

How do i get the second set of " marks to stay within the data?
Comment
Watch Question

lojk.Net and Infrastructure Consultant

Commented:
Unless your data that you are using (when opened in a text viewer like notepad) looks like this

"this is the data that goes into ""The problem Field""  and it continues, with this information.","Field2Value",...

it is not going to work - try stripping quotes from your text at input or consider outputting the text from your source as an XML format - that is much more forgiving (actually understanding) of special characters.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Looks like the field delimiter is set to ",".  Change the field delimiter to something else.  If everything should load into the first column, set it to some string which does not appear in the data, like "~~~~".

You should also probably change from:

Text Qualifier "

to

Text Qualifier NONE

or, again, some character that does not appear in the data, such as ~ (tilde).

Author

Commented:
if i open the CSV in notepad what i see is
Field 1
"this is what should be in a field typically"

in the problem child's case i see
Field1
"This is the problem child field ""some text goes here "" and then, i continue on"
 
So when imported using the " as a text qualifier what should only go into field1  goes into 2 fields like this

Field 1 - This is the problem child field ""some text goes here "" and then

Field 2 - i continue on
so i can't use a ~ as the text really is delimited with "
CERTIFIED EXPERT
Top Expert 2012

Commented:
You will have to fix the data, prior to importing.  SSIS is more strict than previous versions and will not accept it that way.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.