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

Question

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

User · Answer

Added MSSQLSERVER full access to the folder, diskadmin and bulkadmin server roles.

In my c# application, when preparing for the bulk insert command,

string strsql = "BULK INSERT PWCR_Contractor_vw_TEST FROM '" + strFileName + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\\n')";

And I get this error - Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (STATUS).

I looked at my logfile and found that the terminator becomes ' ' instead of '\n'. The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error:

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". Query :BULK INSERT PWCR_Contractor_vw_TEST FROM 'G:\NEWSTAGEWWW\CalAtlasToPWCR\Results\parsedRegistration.csv' WITH (FIELDTERMINATOR = ',', **ROWTERMINATOR = ''**)

So I added extra escape to the rowterminator - string strsql = "BULK INSERT PWCR_Contractor_vw_TEST FROM '" + strFileName + "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\\n')";

And now it inserts successfully.

Bulk Insert SQL -   --->  BULK INSERT PWCR_Contractor_vw_TEST FROM 'G:\\NEWSTAGEWWW\\CalAtlasToPWCR\\Results\\parsedRegistration.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
Bulk Insert to PWCR_Contractor_vw_TEST successful...  --->  clsDatase.PerformBulkInsert

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

My guess is it s an encoding problem  for instance your file is UTF-8 but SQL will not read it the way it should  so it attempts to insert 100   or something along these lines into your table  Possible fixes   Specify Codepage Change the Encoding of the source using Powershell  Code samples  1  BULK INSERT myTable FROM  c  Temp myfile csv  WITH     FIELDTERMINATOR           ROWTERMINATOR     n     CODEPAGE    ACP  -- ACP corresponds to ANSI  also try UTF-8 or 65001 for Unicode     2  get-content  quot myfile csv quot    Set-content -Path  quot myfile csv quot  -Encoding String   String   ANSI  also try Ascii  Oem  Unicode  UTF7  UTF8  UTF32

User · Answer

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                source  microsoft com     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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql