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

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

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

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

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

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

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 -   --- gt   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     --- gt   clsDatase PerformBulkInsert

[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