|
|
Posted by Erland Sommarskog on 09/04/06 09:07
Davor (dsola@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, 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
Navigation:
[Reply to this message]
|