Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to bulk insert import csv file to sql with inside comma

Posted on 2010-08-27
16
Medium Priority
?
976 Views
Last Modified: 2013-11-30
Hi,

I have a simple csv file with addresses to import to sql table. There are comma inside the string, such as name= 'Mycompany, Inc'. normally from  DTS it is OK. But when run bulk insert it separated to two columns.
how to identify the inside comma from delimiter? I have no control to change the file format or content.

Please help

Thanks,

lanac
0
Comment
Question by:lanac222
  • 9
  • 7
16 Comments
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33544894
There are a number of ways to do it and it may take more than one, but they will depend on the details of the file.  Please provide more details:

1. Are there always the exact same number of columns in the CSV?  If there are always a specific number of fields, or you know how many fields are in each record, then you could use that to determine if there are extra commas.

2. Is it the only column with extras?  If you know it is the only column with extra commas, you can count from the beginning and end and figure out the remaining columns are the address.

3. Do you know the format of the columns before and after the one that may have extra commas?  If you know the pervious field is a 4 digit number, and the field after is always a 6 digit number, then you could use these to parse the middle column with extra commas.

And any other details may be helpfull.  
0
 

Author Comment

by:lanac222
ID: 33544988
Thanks for the quick response, developedtester.

I simply run the query liike

set @queryStr=
'bulk insert dbo.'+ rtrim(@tablename) +
' from '''+ @file+''' '+
'WITH (
      FIELDTERMINATOR = '','',
      FIRSTROW = 2,
      ROWTERMINATOR = ''\n''
)'
exec(@queryStr)

There file and sql table name are dynamic. so I don't know which field may has comma inside. Is there any way to add a signature in the query to keep the inside comma?
 
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33545550
When you say signature, what are you thinking?  I think you mean a pattern that will always represent the data even with extra commas'.  It sounds like that is impossible if the "file and sql table name are dynamic. so I don't know which field may has comma inside".
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:lanac222
ID: 33545617
let me say I may know some columns may have comma in a specific file and table pair. The sql table is defined. what I mean signature is something inside WITH section of the query.  
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33546704
You will have to find some pattern that can make that field unique either by its contents (which doesn't sound possible) or the fields on either side of it (which doesn't sound possible).    The only other way is to get the sender to use a different delimeter which as you said isn't possible.  


0
 

Author Comment

by:lanac222
ID: 33546732
But how sql import data interface does it perfectly?
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33557981
What do you mean?
0
 

Author Comment

by:lanac222
ID: 33558573
when I do the import through SQL visual studio data import interface. I have no problem.
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33558936
Please explain what you mean.  How do you run in visual studio?  Do you have the data in a table already?  If so, it's been parsed already.
0
 

Author Comment

by:lanac222
ID: 33559013
I mean if I do the import through SQL management studio Import and Export Wizard by flat file source. The in-string comma will not be problem.  
0
 
LVL 16

Expert Comment

by:Bryan Butler
ID: 33559021
can you post the file, or at least some sample lines?
0
 

Author Comment

by:lanac222
ID: 33559122
This is the csv file with in-string comma.
DYN16-4.csv
0
 
LVL 16

Accepted Solution

by:
Bryan Butler earned 750 total points
ID: 33559228
You have quotes around the fields that have commas.  If it's that way everywhere in the file, that is why studio is working.  It uses them to determine the fields.
0
 

Author Comment

by:lanac222
ID: 33559289
You are right. In the wizard I put double quote for text qualifier. But I don't know how to put text qualifier in bulk inert query.
0
 

Author Comment

by:lanac222
ID: 33559627
I found this will work for me.
'WITH (
      FIELDTERMINATOR = ''","'',
      FIRSTROW = 2,
      KEEPNULLS,
      ROWTERMINATOR = ''"\n"''
)'
0
 

Author Closing Comment

by:lanac222
ID: 33560860
even this is not actual solution to my question, but it does help me to figure it out.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 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