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
>