Solved

SQL Server import - " in column causing problems

Posted on 2012-03-12
4
403 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:
ScottPletcher 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot not using aggregate yield error 3 42
How to SQL Trace a SPECIFIC query 24 59
TSQL previous 5 25
sql query questions 2 24
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now