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
 General SQL Server Forums
 New to SQL Server Programming
 Bulk load data conversion error, and more...

Author  Topic 

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 09:41:32
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

wiltech
Yak Posting Veteran

51 Posts

Posted - 2007-07-13 : 10:10:15
Since the ID column isn't included in your data file, and you have the number of columns set to 8, but only 5 in the data file.

Try changing your format file to:

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


Tony
Go to Top of Page

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 11:30:10
Well, the original file is fine. I modified it in order to post a reduced version. thanks for the tip.
Now it should look right.

I still have not found a solution...
Go to Top of Page

dimitris x
Starting Member

6 Posts

Posted - 2007-07-13 : 13:55:37
Problem found: Order of date (dd/mm/yyyy) )is not recognized. Have to find if this can be set through bcp. Probably not...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-14 : 00:29:51
If you cant change the date format to YYYYMMDD at the file, try importing the data in staging table where datecol is of varchar datatype and then convert that back to proper date and copy to target table

Refer proper date function here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -