Bulk Insert with text Qualifier

Hi,
I am tring to Bulk Insert a file that has " as text Qualifier. When I bulk
insert this is importing the quotes. I would like to be able to import
without the quotes.
Please let me know if you know how..
Thank you
Sambluefish (bluefish@.discussions.microsoft.com) writes:
> I am tring to Bulk Insert a file that has " as text Qualifier. When I bulk
> insert this is importing the quotes. I would like to be able to import
> without the quotes.
You probably need to use a format file. Without further information,
about your table and data, I can only refer to read about format files
in Books Onlines.
Since the odds are good that Books Online will leave you in a maze
(the topic on format files is not easily digested), you may need further
assistance. In such case, post your table definition and a sample file.
(If the file is wide, over 70 chars wide, please place it as an
attachment, so it does not get wrecked in transport.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you very much for your help in this. I have posted a sample table and
a
data file.
Table:
CREATE TABLE [dbo].[TEST] (
[Col1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Contenet in my FMT file:
8.0
2
1 SQLCHAR 0 10 "," 1 Col1 SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 10 "\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
And my data file has following data
"My","World"
I am still unable to get the data in without ". I have checked the format
file sections in books online and google- but without much luck.
Your help in this is appreciated.
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> You probably need to use a format file. Without further information,
> about your table and data, I can only refer to read about format files
> in Books Onlines.
> Since the odds are good that Books Online will leave you in a maze
> (the topic on format files is not easily digested), you may need further
> assistance. In such case, post your table definition and a sample file.
> (If the file is wide, over 70 chars wide, please place it as an
> attachment, so it does not get wrecked in transport.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||bluefish (bluefish@.discussions.microsoft.com) writes:
> Thank you very much for your help in this. I have posted a sample table
> and a data file.
> Table:
> CREATE TABLE [dbo].[TEST] (
> [Col1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [col2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Contenet in my FMT file:
> 8.0
> 2
> 1 SQLCHAR 0 10 "," 1 Col1 SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 0 10 "\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
> And my data file has following data
> "My","World"
> I am still unable to get the data in without ". I have checked the format
> file sections in books online and google- but without much luck.
Hehe. That's certainly not easily digestable material.
You can do it, but I have to thank former SQL Server MVP Linda W for
learning me the trick. Here is how you format file should look like:
8.0
3
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 Col1 SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 0 0 "\"\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
First of all, you need to include the " in the delimiter definition. But
the real trick is the addition of a third empty field before the first
". Not that I have a 0 in the database-column number, this column is
not to be imported.
Note: I didn't test this, as the hour is late here. But I hope it
works.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This worked! Thank you very much!!
Please also convey my thanks to Ms. Linda also.
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> Hehe. That's certainly not easily digestable material.
> You can do it, but I have to thank former SQL Server MVP Linda W for
> learning me the trick. Here is how you format file should look like:
> 8.0
> 3
> 1 SQLCHAR 0 0 "\"" 0 "" ""
> 2 SQLCHAR 0 0 "\",\"" 1 Col1 SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 0 0 "\"\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
> First of all, you need to include the " in the delimiter definition. But
> the real trick is the addition of a third empty field before the first
> ". Not that I have a 0 in the database-column number, this column is
> not to be imported.
>
> Note: I didn't test this, as the hour is late here. But I hope it
> works.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Bulk Insert does not handle text qualifier

I'm trying to use Bulk Insert to upload a flat file.
The file is comma delimited but contains one field that has quotation marks around it. That field typically contains a comma (which of course, I don't want to consider as a field delimeter)
here is an example of the layout.

1,test,D,"OCB, England",450727,8575

I use the DTS Bulk Insert task to help me generate a format file to use with the bulk insert statement. I tell the wizard that the file is comma delimited with a text qualifer and it's fine with that. But when I run the bulk insert, it bombs with a "String or Binary data would be trunctated error.
So, any idea's on how to bulk insert to be smart enough to deal with this situation?"String or Binary data would be trunctated" usually means that the string you are trying to insert is longer than that allowed by the field in your destination table. If, for instance, your table's field is defined as 50 characters (the default) and one of your quoted strings is longer than 50 characters, you will get this error.

Either increase the size of your destination field, or cast your string as a field of the proper width before attempting to insert it.

blindman

Bulk Copy Text Qualifier

Hi,
this is the structure of my file .txt:
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
and this is the structure of my file .fmt:
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
Thanks
Simo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>

Bulk Copy Text Qualifier

Hi
this is the structure of my file .txt
--
'S012RM4','BBBBKMQBBBB8
'S012RM4','BBBBKMQBBBB8
--
and this is the structure of my file .fmt
--
7.
1 SQLCHAR 0 50 "," 1 c_
2 SQLCHAR 0 50 "\r\n" 2 c_
--
I'm using a DTS to import this recods with Bulk Copy
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
column 1 = 'S012RM4
column 2 = 'BBBBKMQBBBB8
and not
column 1 = S012RM
column 2 = BBBBKMQBBBB
why
ThankSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>

Bulk Copy Text Qualifier

Hi,
this is the structure of my file .txt:
--
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
--
and this is the structure of my file .fmt:
--
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
--
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
ThanksSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>