SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Text Qualifier in a Bulk Insert Statement
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

NeilC
Yak Posting Veteran

USA
55 Posts

Posted - 01/02/2007 :  14:56:26  Show Profile
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

USA
36941 Posts

Posted - 01/02/2007 :  15:01:20  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

NeilC
Yak Posting Veteran

USA
55 Posts

Posted - 01/02/2007 :  15:56:47  Show Profile
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?


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/02/2007 :  16:09:07  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

NeilC
Yak Posting Veteran

USA
55 Posts

Posted - 01/03/2007 :  09:48:40  Show Profile
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".

Edited by - NeilC on 01/03/2007 09:49:41
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/03/2007 :  11:45:44  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

NeilC
Yak Posting Veteran

USA
55 Posts

Posted - 01/03/2007 :  16:51:03  Show Profile
Thanks, t = tab correct? If my file is comma delimeted I would use "\","\" for that column correct?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000