SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk load data conversion error, and more...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dimitris x
Starting Member

6 Posts

Posted - 07/13/2007 :  09:41:32  Show Profile  Reply with Quote
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


Edited by - dimitris x on 07/13/2007 11:31:32

wiltech
Yak Posting Veteran

USA
51 Posts

Posted - 07/13/2007 :  10:10:15  Show Profile  Reply with Quote
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

Edited by - wiltech on 07/13/2007 10:19:44
Go to Top of Page

dimitris x
Starting Member

6 Posts

Posted - 07/13/2007 :  11:30:10  Show Profile  Reply with Quote
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 - 07/13/2007 :  13:55:37  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 07/14/2007 :  00:29:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000