?
Solved

SQL Server import - " in column causing problems

Posted on 2012-03-12
4
Medium Priority
?
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 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