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 bitan 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.061 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_AS5 SQLCHAR 0 100 "\t" 5 Description Greek_CI_AS6 SQLCHAR 0 24 "\t" 6 StartDate ""
3. I run BULK INSERT BULK INSERT tablenameFROM dataFileWITH ( 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Bulk 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 1Cannot bulk load because the maximum number of errors (10) was exceeded.Msg 7399, Level 16, State 1, Line 1The 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 1Cannot 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 timeThank you.Dimitris Chrysomallis