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 with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
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.mspxsql

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Jesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
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 with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.
> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

BULK INSERT file with quoted strings, but no quotes when NULL

Hello,
I've put together a format file for a text file that I need to import
using ",\"" and "\",\"", etc. as column delimiters. This would normally
cover the whole problem with quoted strings. However, in this
particular file if a string value is NULL then that column doesn't have
the double quotes around it for that record.
Is there any way to handle this using BULK INSERT? I know that I can do
it in DTS, but can it be done with BULK INSERT?
Thanks!
-Tom.I should also point out that some of these files can be a couple
hundred million rows, so I would really prefer not to load them in with
the quotes and then use REPLACE() to remove those quotes.
Thanks,
-Tom.
Aardvark wrote:
> Hello,
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
> Thanks!
> -Tom.|||My suggestion since you do not want to use the REPLACE function is to
strip the " character using the Operating System or a text editor..
Maybe you could use cygwin and SED/AWK.
JD.|||Aardvark (tom_hummel@.hotmail.com) writes:
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
Unless there are some very fortunate circumstances with the file format,
the answer is no. The best suggestion I can give is to write a program
that reads the file, strips the quotes and the uses the bulk-copy API
to insert from variables. But you probably prefer to use DTS instead.
(And what those fortunate cicrumstances may be, I don't really know.)
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 and text fields

Hi,

I am trying to do a bulk insert using a text file that is comma delimited.

In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.

Is there a way to tell the BULK INSERT statement not to treat these as delimiters?

thanks,

sivilianyou need to set the field qualifiers.|||Do you mean using format files in order to do this?

If so I do not believe that you can do it even with that.

I found this excerpt from SQL Books Online.

Important Terminators must be chosen to ensure that their pattern does not appear in any of the data. For example, when using tab terminators with a field that contains tabs as part of the data, bcp does not know which tab represents the end of the field. The bcp utility always looks for the first possible character(s) that matches the terminator it expects. Using a character sequence with characters that do not occur in the data avoids this conflict.

If I am wrong and I hope I am; Can you elaborate as to how to set these field qualifers.

thanks,

sivilian

Bulk insert and Double Quotes

Hi,

i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

this command will import the files fine

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","')

but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

so my question is simple who do i do this with a format file ?

Code Snippet

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","'

, FORMATFILE = 'format_file_path')

|||

that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

I have found the solution my self by accident

you can use the following format file to do it

9.0
19
1 SQLCHAR 0 255 "\",\"" 1 Field1 Latin1_General_CI_AS
2 SQLCHAR 0 255 "\",\"" 2 Field2 Latin1_General_CI_AS
3 SQLCHAR 0 255 "\",\"" 3 Field3 Latin1_General_CI_AS
4 SQLCHAR 0 255 "\",\"" 4 Field4 Latin1_General_CI_AS
5 SQLCHAR 0 255 "\",\"" 5 Field5 Latin1_General_CI_AS
6 SQLCHAR 0 255 "\",\"" 6 Field6 Latin1_General_CI_AS
7 SQLCHAR 0 255 "\",\"" 7 Field7 Latin1_General_CI_AS
8 SQLCHAR 0 255 "\",\"" 8 Field8 Latin1_General_CI_AS
9 SQLCHAR 0 255 "\",\"" 9 Field9 Latin1_General_CI_AS
10 SQLCHAR 0 255 "\",\"" 10 Field10 Latin1_General_CI_AS
11 SQLCHAR 0 255 "\",\"" 11 Field11 Latin1_General_CI_AS
12 SQLCHAR 0 255 "\",\"" 12 Field12 Latin1_General_CI_AS
13 SQLCHAR 0 255 "\",\"" 13 Field13 Latin1_General_CI_AS
14 SQLCHAR 0 255 "\",\"" 14 Field14 Latin1_General_CI_AS
15 SQLCHAR 0 255 "\",\"" 15 Field15 Latin1_General_CI_AS
16 SQLCHAR 0 255 "\",\"" 16 Field16 Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 Field17 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 Field18 Latin1_General_CI_AS
19 SQLCHAR 0 255 "\r\n" 19 Field19 Latin1_General_CI_AS

BULK INSERT

Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
JohTo do this you will have to use a format file
For an example see
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

BULK INSERT

Hi,
I am trying to do a bulk insert using a text file that is comma delimited.
In the text file there are fields which are surrounded by quotes and contain commas; these commans should not be considered delimiters.
Is there a way to tell the BULK INSERT statement not to treat these as delimiters?
thanks,
John
To do this you will have to use a format file
For an example see
http://www.nigelrivett.net/BCP_quote...rmat_file.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!