SQL Server 2005 - BULK INSERT

[Davor]

I'm trying to import data from flat file in table and have few
problems.

1. Field Delimiter is ',' (comma). If ',' occurs in quoted
string it is still treated as field delimiter. This is BUG or ?
2. In table I have datetime field that can be null, but bulk
insert reports error if in flat file is null or ''. It's OK only when
real date is specified.

Table:
create table AttachmentList (
Code integer not null,
ClassID integer null,
Description varchar(200) null,
ValidUntil datetime null,
constraint PK_ATTACHMENTLIST primary key (Code))

flat file.

1,13,'Naputak, CU 261098', ''
Thanks in advance

Davor

[Erland Sommarskog]

Davor (ds***@yahoo.com) writes:

I'm trying to import data from flat file in table and have few
problems.

1. Field Delimiter is ',' (comma). If ',' occurs in quoted
string it is still treated as field delimiter. This is BUG or ?

No, it is by design. BCP does try to guess what you are up to. If you
say comma is a delimiter, then comma is a delimiter. The quotes are then
part of the data.

You will need to specify a format file. Judging from the sample row you
posted, that format file should look like this:

8.0
4
1 SQLCHAR 0 0 "," 1 col1 ""
2 SQLCHAR 0 0 ",'" 2 col2 ""
3 SQLCHAR 0 0 "', '" 3 col3 ""
4 SQLCHAR 0 0 "'\r\n" 4 col4 ""

That is, any quote or space that serves as a delimiter must be specified
as such. BCP has no built-in rules. (Essentially BCP treats all input
as binary data.)

2. In table I have datetime field that can be null, but bulk
insert reports error if in flat file is null or ''. It's OK only when
real date is specified.

'' is not a legal date. If you consistently use it as a delimiter, you
should specify it in the format file. If you use it inconsistently, you
have a problem.

--
Erland Sommarskog, SQL Server MVP, es****@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

[John Bell]

Hi
BULK INSERT does not have a method to define quoted strings, therefore you
would also have the quotes within the column data. It is not a BUG it is
just that the functionality does not extend that far. If you want to import
this file try using the Import Wizard and DTS. If you want to request this
in a future version you may want to vote for it at
https://connect.microsoft.com/SQLSer...dbackID=127032

John

"Davor" <ds***@yahoo.comwrote in message
news:44**************@news.t-com.hr...

I'm trying to import data from flat file in table and have few
problems.

1. Field Delimiter is ',' (comma). If ',' occurs in quoted
string it is still treated as field delimiter. This is BUG or ?
2. In table I have datetime field that can be null, but bulk
insert reports error if in flat file is null or ''. It's OK only when
real date is specified.

Table:
create table AttachmentList (
Code integer not null,
ClassID integer null,
Description varchar(200) null,
ValidUntil datetime null,
constraint PK_ATTACHMENTLIST primary key (Code))

flat file.

1,13,'Naputak, CU 261098', ''
Thanks in advance

Davor

[Davor]

Thanks You for Your help !

Davor