Solved

SQL Server import - " in column causing problems

Posted on 2012-03-12
4
405 Views
Last Modified: 2012-04-29
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?
0
Comment
Question by:quigleyryan
4 Comments
 
LVL 9

Expert Comment

by:lojk
ID: 37709966
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37710034
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).
0
 

Author Comment

by:quigleyryan
ID: 37710096
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 "
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37712853
You will have to fix the data, prior to importing.  SSIS is more strict than previous versions and will not accept it that way.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question