Bulk load data conversion error. and more...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Bulk load data conversion error. and more...

Author

Topic

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 11:43:30

Hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:


CREATE TABLE [dbo].[counties](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[InternalID] [int] NULL,
	[Active] [bit] NULL,
	[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
	[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
	[StartDate] [datetime] NULL
)

An example data file looks like this:


InternalID	Active	Code	Description	Date	
1	        1	01	Î‘Î¹Ï„Ï‰Î»Î¿Î±ÎºÎ±Ï�Î½	16/11/1909	
2        	1	02	Î‘Ï�Î³Î¿Î»Î¯Î´Î¿Ï‚	29/04/1949	
3           	1	03	Î‘Ï�ÎºÎ±Î´Î¯Î±Ï‚	16/11/1909	
4         	1	04	Î†Ï�Ï„Î·Ï‚	        16/11/1909	
5	        1	05	Î‘Ï„Ï„Î¹ÎºÎ®Ï‚	        26/07/1943

So, what I do is:
1. for each table I generate a character format file with the following command:

bcp mydb..table format nul -f tableformat.fmt -c -T -S host\sqlexpress

2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:


9.0
6
1  SQLCHAR  0  0   ""    0  id          ""
2  SQLCHAR  0  12  "\t"  2  InternalID  ""
3  SQLCHAR  0  3   "\t"  3  Active      ""
4  SQLCHAR  0  20  "\t"  4  Code        Greek_CI_AS
5  SQLCHAR  0  100 "\t"  5  Description Greek_CI_AS
6  SQLCHAR  0  24  "\t"  6  StartDate   ""

3. I run BULK INSERT

BULK INSERT tablename
FROM dataFile
WITH (
      FIRSTROW=2,
      FORMATFILE = formatFile,
      DATAFILETYPE = 'char',
      FIELDTERMINATOR='\t',
      ROWTERMINATOR='\r\n',
      KEEPNULLS
)

As a result of the above configuration I get this:


Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.

Dimitris Chrysomallis

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-13 : 12:28:47

I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.

Thanks
Karunakaran

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 12:59:42

quote:
Originally posted by karuna

I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.

Well, when i run a dts package on the same data and by explicitly setting the date column/field mappings, the import works succesfuly, and the dates values are in dd/mm/yyyy order. I suppose that SET DATEFORMAT might be automaticaly called during conversion, but i am not sure.
Still, how am i supposed to correctly import these data files, given that I can not modify them? Should not the format file handle the mapping in some way? Ok, I can do it with dts manually, but it has to be done programatically.
Thank you for your response

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 13:15:57

quote:
Originally posted by karuna

I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.

Well, I have to say you were absolutely right! I modified the date order and it worked!! Thank you.
But still, is there a way to keep the data as it is and let the server do the conversion? you see, i can not modify the data files.

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-13 : 13:21:14

Iam not sure if there is a way to specify the format of date in the format file. If there is one, it should solve the issue.
Let me try searching and see if I find something on format for date in the fmt file.

Thanks
Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-13 : 17:25:10

How about inserting the data to a staging table with varchar datatype and then populate your original destination table?

Thanks
Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources