asked on
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?
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?
Microsoft SQL Server 2008
Last Comment
Anthony Perkins
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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 "
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 "
You will have to fix the data, prior to importing. SSIS is more strict than previous versions and will not accept it that way.
"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.