question

nevada1978 avatar image
nevada1978 asked

Bulk load Data Conversion error

First and foremost, I just want an understanding of this error so that when I encounter it in the future, I have a plan of action and can document something to someone who may not know SQL as well as I do. This is different from my past inquries. I slimmed it down in order to gain knowledge of concept before taking on the larger project. In this case, there are 47 columns and we are trying to perform a bulk insert (command below). IThe original text or flat file is delimted by the bar or shift+\. The format file used here is an xml version. It is the second snippet below this. For sake of not making this too long, I have paired it down to the first 13. When I run the bulk insert command, I get the buik load data conversion error for rows 5,8,11, and 13 amongst many errors. Sample data 32007|0|10|70|0|2017|1| Create table script create table countytest ( area_fips char(5), own_code char(1), industry_code char(6), agglvl_code char(2), size_code char(1), year char(4), qtr char(1), disclosure_code char(1), area_title char(50), own_title char(50), industry_title char(50), agglvl_title char(50), size_title char(50), Bulk insert countytest from 'Q:\ countytest.txt' WITH (FIRSTROW=2,FORMATFILE = 'Q:\countyxml.xml' ); - - 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 (size_code).
xmlssmsbulk-insertconversion
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
nevada1978 avatar image
nevada1978 answered
Here is the answer. It is painful to admit that it was this simple BULK INSERT dbo.countytest FROM 'Q:\ countytest.txt' WITH ( FIRSTROW=2, FIELDTERMINATOR = '|' ); I was under the impression that the presence of items that needed to retain the comma (monsters, inc.) and decimals (123.45) necessitated a format file. Well, that is not the case. Also, the fact that the data came from notepad++ and had lines that end in CR-LF made the inclusion of a row terminator unnecessary.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.