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.0211 SQLINT 0 12 "\t\"" 1 id SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 255 "\"\t" 2 name SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 255 "\t\"" 3 type_no SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 255 "\"\t\"" 4 city SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 255 "\"\t\"" 5 county SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 255 "\"\t\"" 6 state SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 255 "\"\t\"" 7 postal SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 255 "\"\t" 8 country SQL_Latin1_General_CP1_CI_AS9 SQLINT 0 12 "\t\"" 9 timezone_id SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 0 255 "\"\t\"" 10 size SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 0 255 "\"\t\"" 11 spl SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 0 255 "\"\t\"" 12 mmspl SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 0 255 "\"\t" 13 ragsdale SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 0 255 "\t\"" 14 refinepos SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 0 255 "\"\t" 15 cell SQL_Latin1_General_CP1_CI_AS16 SQLCHAR 0 255 "\t\"" 16 proximity SQL_Latin1_General_CP1_CI_AS17 SQLCHAR 0 255 "\"\t" 17 user_init SQL_Latin1_General_CP1_CI_AS18 SQLCHAR 0 255 "\t" 18 lcdate SQL_Latin1_General_CP1_CI_AS19 SQLCHAR 0 255 "\t" 19 inbinary SQL_Latin1_General_CP1_CI_AS20 SQLCHAR 0 255 "\t" 20 latitude SQL_Latin1_General_CP1_CI_AS21 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.aspxThat 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.01431 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 1Cannot 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? |
|
|
|
|
|