473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

SQL Server 2005 - BULK INSERT

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

Sep 4 '06 #1
3 7646
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
Sep 4 '06 #2
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

Sep 4 '06 #3
Thanks You for Your help !

Davor
Sep 5 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
10
by: Thomas Richards | last post by:
I have two SQL Server 2000 machines. The same file is sent nightly to each server and a stored proc uses BULK INSERT to load it into a staging table for processing. Once I've bcp'ed it in, I put...
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
3
by: moonriver | last post by:
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those records one by one, but the performance is very...
18
by: mollyf | last post by:
I just installed SQL Server 2005 on my PC (the developer's edition) yesterday. I have some scripts written by one of my coworkers to create some tables and stored procedures in a database that...
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
19
by: dunleav1 | last post by:
I built a test job that loads data into the database. I get great performance with Oracle and I'm trying to tune Sql Server to get the same type of performance. Based on system monitoring it...
9
by: cabrenner | last post by:
I am new to SQL Server, and migrating part of an Access application to SSE. I am trying to insert a comma delimited file into SSE 2005. I am able to run a BULK INSERT statement on a simple file,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.