Solved

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

Posted on 2010-08-27
16
962 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 250 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

14 Experts available now in Live!

Get 1:1 Help Now