Solved

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

Posted on 2010-08-27
16
965 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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