Bulk insert with data having comma

Question

Bulk insert with data having comma

IT researcher

SSCertifiable

Points: 7608
More actions
January 17, 2013 at 7:43 am

#272848

I am trying to use BULK INSERT option of sql server by which i can insert data to table from .csv file. I am able to do it but only problem is where the data already has comma(,) within data.
Now i am using
BULK INSERT designs
FROM 'D:vt.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
But i have so many column which already have content with comma like us,uk
So data insert fails for these cases. So how can i avoid it? i want to insert it as it is with comma by using bulk insert. Please help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Can you switch to using SSIS?

If not then you may need to use a format file with BULK INSERT. You may be out of luck completely if the file is inconsistently formatted. In the file, do all fields have quotes around them or just some? If some, for the fields that have quotes is it consistent where the field that has quotes has it on all lines in the file, or just for some lines?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Biank Ten Centuries Points: 1332 More actions · Answer 2

How about convert the .csv to .xlsx and then use Improt/Export wizard or use SELECT INTO ... FROM OPENROWSET/OPENQUERY or SSIS. You'll have many choices with Excel format, which should be the easiest/simplest way to do it.

IT researcher SSCertifiable Points: 7608 More actions · Answer 3

I have sql server express.So SSIS is not available for me.

Here is a sample of csv file i am trying to insert

QueryResult,QueryResult,500,2,46,A,abc,1,NULL,NULL,"date_atnd,time_atnd",,70,NULL,48,NULL

Here date_atnd,time_atnd has the comma in between. So i think this causes the problem with Bulk insert.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

winmansoft (1/17/2013)
I have sql server express.So SSIS is not available for me.
Here is a sample of csv file i am trying to insert
QueryResult,QueryResult,500,2,46,A,abc,1,NULL,NULL,"date_atnd,time_atnd",,70,NULL,48,NULL
Here date_atnd,time_atnd has the comma in between. So i think this causes the problem with Bulk insert.

If every line has quotes around the field, even when no embedded comma is present, then you can use BULK INSERT with a format file.

BULK INSERT and bcp leverage the same interface so creating format files are the same across both tools. See the Section B, the non-XML format file for delimited data:

Creating a Format File

For your case you'll choose this delimiter to separate the field before your quoted-field:

",\""

and this one for after your quoted-field it will be:

"\","

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

IT researcher SSCertifiable Points: 7608 More actions · Answer 5

I tried to use tab limited file and the bulk insert query as

BULK INSERT designs

FROM 'D:vt.csv'

WITH

(

FIELDTERMINATOR = '\t',

ROWTERMINATOR = ''

)

In this case insert was successful. But the data with comma such as us,uk is inserted to table as "us,uk" .I don't want these double quotes in the sql table.Any solutions?

After going through the link for creating format file i got confused. Can i create format file automatically using any command

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

opc.three (1/18/2013)
winmansoft (1/17/2013)
I have sql server express.So SSIS is not available for me.
Here is a sample of csv file i am trying to insert
QueryResult,QueryResult,500,2,46,A,abc,1,NULL,NULL,"date_atnd,time_atnd",,70,NULL,48,NULL
Here date_atnd,time_atnd has the comma in between. So i think this causes the problem with Bulk insert.
If every line has quotes around the field, even when no embedded comma is present, then you can use BULK INSERT with a format file.
BULK INSERT and bcp leverage the same interface so creating format files are the same across both tools. See the Section B, the non-XML format file for delimited data:
Creating a Format File
For your case you'll choose this delimiter to separate the field before your quoted-field:
",\""
and this one for after your quoted-field it will be:
"\","

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Biank Ten Centuries Points: 1332 More actions · Answer 7

You can update the table

UPDATE tableName

SET col1 = REPLACE(col1, '"', '')

However, I'm still not clear why not converting the file to Excel or change the output using different field terminator when you export it, like : or ;?

IT researcher SSCertifiable Points: 7608 More actions · Answer 8

How can i import from an excel file in sql express 2008 r2?

Biank Ten Centuries Points: 1332 More actions · Answer 9

This should work:

INSERT INTO TableName -- assuming you have a table already

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=FileName.xlsx',

'SELECT * FROM [SheetName$]') -- get SheetName from Excel