Bulk load data conversion error (type mismatch or

问题:

I'm getting the conversion error when I try to import a text file to my database. Below is the error message I received:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year).

Here is my query code:

CREATE TABLE Students
(
    StudentNo    Integer NOT NULL Primary Key,
    FirstName    VARCHAR(40) NOT NULL,
    LastName     VARCHAR(40) NOT NULL,
    Year         Integer,
    GPA          Float NULL
);

Here is the sample data from text file:

100,Christoph,Van Gerwen,2011
101,Anar,Cooke,2011
102,Douglis,Rudinow,2008

I think I know what the problem is..Below is my bulk insert code:

use xta9354
bulk insert xta9354.dbo.Students
from 'd:\userdata\xta9_Students.txt' 
with (fieldterminator = ',',rowterminator = '\n') 

With the sample data, there is no ',' after the Year attribute even tho there is still another attribute Grade after the Year which is NULL

Can someone please tell me how to fix this?

回答1:

Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table.

myTestFormatFiles.Fmt may look like:

9.0
4
1       SQLINT        0       3       ","      1     StudentNo      ""
2       SQLCHAR       0       100     ","      2     FirstName      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     LastName       SQL_Latin1_General_CP1_CI_AS
4       SQLINT        0       4       "\r\n"   4     Year           "

http://i.msdn.microsoft.com/dynimg/IC45684.gif

This tutorial on skipping a column with BULK INSERT may also help.

Your statement then would look like:

USE xta9354
GO
BULK INSERT xta9354.dbo.Students
    FROM 'd:\userdata\xta9_Students.txt' 
    WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt')

回答2:

In my case, I was dealing with a file that was generated by hadoop on a linux box. When I tried to import to sql I had this issue. The fix wound up being to use the hex value for 'line feed' 0x0a. It also worked for bulk insert

bulk insert table from 'file' 
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a')

回答3:

We use the bulk insert as well. The file we upload is sent from an external party. After a while of troubleshooting, I realized that their file had columns with commas in it. Just another thing to look for...

回答4:

The above options works for Google big query file also. I exported a table data to goodle cloud storage and downloaded from there. While loading the same to sql server was facing this issue and could successfully load the file after specifying the row delimiter as

ROWTERMINATOR = '0x0a' 

Pay attention to header record as well and specify

FIRSTROW = 2

My final block for data file export from google bigquery looks like this.

BULK INSERT TABLENAME
        FROM 'C:\ETL\Data\BigQuery\In\FILENAME.csv'
        WITH
        (
         FIRSTROW = 2,
         FIELDTERMINATOR = ',',  --CSV field delimiter
         ROWTERMINATOR = '0x0a',--Files are generated with this row terminator in Google Bigquery
         TABLOCK
        )