Text Qualifier in a Bulk Insert Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Text Qualifier in a Bulk Insert Statement

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-01-02 : 14:56:26

I have a comma delimited text file like "col1","col2","col3" etc. and when I import this using the import wizard there is a field marked 'text qualifier' - if I add " to this field the data gets imported with out the quotes but when I execute a bulk insert statement it inserts the qoutes. What can I do in the bulk insert statement to prevent the quotes from being inserted?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-02 : 15:01:20

You can use a format file to get rid of the double quotes. It becomes pretty tricky if you've got a lot of columns like this.

Here's an example format file of mine that does the same thing:


8.0
21
1	SQLINT	0	12	"\t\""		1	id		SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	0	255	"\"\t"		2	name		SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	0	255	"\t\""		3	type_no		SQL_Latin1_General_CP1_CI_AS
4	SQLCHAR	0	255	"\"\t\""	4	city		SQL_Latin1_General_CP1_CI_AS
5	SQLCHAR 0	255	"\"\t\""	5	county		SQL_Latin1_General_CP1_CI_AS
6	SQLCHAR	0	255	"\"\t\""	6	state		SQL_Latin1_General_CP1_CI_AS
7	SQLCHAR	0	255	"\"\t\""	7	postal		SQL_Latin1_General_CP1_CI_AS
8	SQLCHAR	0	255	"\"\t"		8	country		SQL_Latin1_General_CP1_CI_AS
9	SQLINT	0	12	"\t\""		9	timezone_id	SQL_Latin1_General_CP1_CI_AS
10	SQLCHAR	0	255	"\"\t\""	10	size		SQL_Latin1_General_CP1_CI_AS
11	SQLCHAR	0	255	"\"\t\""	11	spl		SQL_Latin1_General_CP1_CI_AS
12	SQLCHAR	0	255	"\"\t\""	12	mmspl		SQL_Latin1_General_CP1_CI_AS
13	SQLCHAR	0	255	"\"\t"		13	ragsdale	SQL_Latin1_General_CP1_CI_AS
14	SQLCHAR	0	255	"\t\""		14	refinepos	SQL_Latin1_General_CP1_CI_AS
15	SQLCHAR	0	255	"\"\t"		15	cell		SQL_Latin1_General_CP1_CI_AS
16	SQLCHAR	0	255	"\t\""		16	proximity	SQL_Latin1_General_CP1_CI_AS
17	SQLCHAR	0	255	"\"\t"		17	user_init	SQL_Latin1_General_CP1_CI_AS
18	SQLCHAR	0	255	"\t"		18	lcdate		SQL_Latin1_General_CP1_CI_AS
19	SQLCHAR	0	255	"\t"		19	inbinary 	SQL_Latin1_General_CP1_CI_AS
20	SQLCHAR	0	255	"\t"		20	latitude	SQL_Latin1_General_CP1_CI_AS
21	SQLCHAR	0	255	"\r\n"		21	longitude	SQL_Latin1_General_CP1_CI_AS

Tara Kizer

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-01-02 : 15:56:47

Thanks, I read up a little on that at http://msdn2.microsoft.com/en-us/library/ms178129.aspx

That looks pretty complex, my text file contains 145 columns x 110,000 rows (not my database design - 3rd party!)

So the format is:

8.0 (whats is 8.0)
21 (the number of columns)
1 (column number) SQLINT (datatype) 0 (?) 12(amount of data in the column) "\t\"" (?) 1(column# in SQL server?) ID(column name in sql server?) SQL_Latin1_General_CP1_CI_AS (Data Type In SQL Server?)

So how dow I build this and execute it etc? Do I enter all this in a text file saved as .fmt and fire it through a command line?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-02 : 16:09:07

The number of rows in the text file doesn't impact the format file.

SQL Server Books Online shows what each item means in a format file. Some of the items can be ignored such as the 0. SQL_Latin... is the collation of the column.

When you run bulk insert, you tell it that you have a format file. BULK INSERT can be run in Query Analyzer. bcp is a command line import/export tool. Both can accept format files.

Tara Kizer

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-01-03 : 09:48:40

My text file format is as follows (1 row of data):

quote:
"8","Co Name","3500 N.W. 37th Ave.","Miami","","FL","33142","US","30351001","3100","25.807702","-80.256499",ADDRESS,"Business Hours","Walk Up","","","","","","","","","1","","","","","","","","","","","","","","","","","","","","Co Name","Full Service","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""

When I import this data (all 110,000 rows) I dont want to import the double quote so I created this .fmt file (1st row):

quote:
9.0
143
1 SQLCHAR 0 20 "","" 1 ID SQL_Latin1_General_CP1_CI_AS

I want this format file to remove the very first " the the "," between columns and the last "

In my stored procedure I have:

quote:

Set @sqlstring = ' Bulk Insert TableName '
Set @sqlstring = @sqlstring + ' From ''C:\Client\Data.txt'' '
Set @sqlstring = @sqlstring + ' WITH (FORMATFILE = ''C:\Client\FormatFile.fmt'');'

and when I execute this sp I get:

quote:

Msg 4828, Level 16, State 1, Line 1
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "C:\Client\FormatFile.fmt".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 11:45:44

Your format file needs to include all of the columns. It will remove the double quotes if you use my example format file as an example on how to create yours. A double quote will be \" in the format file. Make sure to include all beginning and ending double quotes plus the commas in the format file. My text file was tab delimited, so that's why you see \t in there. You'll need to switch those to commas.

I would start out with a file that has only two columns in it. Get your format file correct for that and see if you can import the data without the double quotes. Then expand it to the greater set of columns.

Tara Kizer

NeilC
Yak Posting Veteran

55 Posts

Posted - 2007-01-03 : 16:51:03

Thanks, t = tab correct? If my file is comma delimeted I would use "\","\" for that column correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources