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

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
lanac222Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bryan ButlerConnect With a Mentor Commented:
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
 
Bryan ButlerCommented:
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
 
lanac222Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Bryan ButlerCommented:
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
 
lanac222Author Commented:
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
 
Bryan ButlerCommented:
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
 
lanac222Author Commented:
But how sql import data interface does it perfectly?
0
 
Bryan ButlerCommented:
What do you mean?
0
 
lanac222Author Commented:
when I do the import through SQL visual studio data import interface. I have no problem.
0
 
Bryan ButlerCommented:
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
 
lanac222Author Commented:
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
 
Bryan ButlerCommented:
can you post the file, or at least some sample lines?
0
 
lanac222Author Commented:
This is the csv file with in-string comma.
DYN16-4.csv
0
 
lanac222Author Commented:
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
 
lanac222Author Commented:
I found this will work for me.
'WITH (
      FIELDTERMINATOR = ''","'',
      FIRSTROW = 2,
      KEEPNULLS,
      ROWTERMINATOR = ''"\n"''
)'
0
 
lanac222Author Commented:
even this is not actual solution to my question, but it does help me to figure it out.
0
All Courses

From novice to tech pro — start learning today.