Solved

SQL Server import - " in column causing problems

Posted on 2012-03-12
4
408 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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Express or Standard? 5 60
MSSQL Convert Char to Date Time 5 33
Enabling flash installation using GPO 2 51
get count of orders by customer Sql Server table. 3 44
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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