Bulk load data conversion error with csv file in SQL Server 2016

Question

I have a .csv file. I want to import it into my SQL Server database called Unisoft_kef, but I get these errors :

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (stQuant).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 45 (stFpaVal).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 10 (stPrice).

Msg 4864, Level 16, State 1, Line 79
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 10 (stPrice).

Msg 4865, Level 16, State 1, Line 79
Cannot bulk load because the maximum number of errors (10) was exceeded.

Msg 7399, Level 16, State 1, Line 79
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 79
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

The exact query I use is :

USE [Unisoft_KEF]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN
    DROP TABLE Poliseis_STRN

    CREATE TABLE [dbo].[Poliseis_STRN]
    (
        [stFileId] [INT] NULL ,
        [stDate] [NVARCHAR](50) NULL,
        [sFileId] [NVARCHAR](50) NULL,
        [stTransKind] [NVARCHAR](50) NULL,
        [stDoc] [NVARCHAR](50) NULL,
        [stLocation] [NVARCHAR](50) NULL,
        [stCustSuppl] [NVARCHAR](50) NULL,
        [stQuant] [FLOAT] NULL,
        [stQuant2] [FLOAT] NULL,
        [stPrice] [FLOAT] NULL,
        [stDiscount] [NVARCHAR](50) NULL,
        [stValue] [FLOAT] NULL,
        [stComment] [NVARCHAR](50) NULL,
        [stComment2] [NVARCHAR](50) NULL,
        [stSalesman] [NVARCHAR](50) NULL,
        [stForCncy] [NVARCHAR](50) NULL,
        [stFCPrice] [FLOAT] NULL,
        [stFCValue] [FLOAT] NULL,
        [stFromFile] [NVARCHAR](50) NULL,
        [stPrinted] [NVARCHAR](50) NULL,
        [stVATid] [NVARCHAR](50) NULL,
        [stFromGroup] [NVARCHAR](50) NULL,
        [stDocOrigin] [NVARCHAR](50) NULL,
        [stInsDate] [NVARCHAR](50) NULL,
        [stUserId] [NVARCHAR](50) NULL,
        [stGLUpdated] [NVARCHAR](50) NULL,
        [stLotDelete] [NVARCHAR](50) NULL,
        [stThirdPart] [NVARCHAR](50) NULL,
        [stFiller1] [NVARCHAR](50) NULL,
        [stLotCode] [NVARCHAR](50) NULL,
        [stLotDate] [NVARCHAR](50) NULL,
        [strcost] [NVARCHAR](50) NULL,
        [stTime] [NVARCHAR](50) NULL,
        [stOtherExp] [NVARCHAR](50) NULL,
        [stWeight] [NVARCHAR](50) NULL,
        [stVolume] [NVARCHAR](50) NULL,
        [stImpClass] [NVARCHAR](50) NULL,
        [stEFK] [NVARCHAR](50) NULL,
        [stDocLine] [NVARCHAR](50) NULL,
        [stDocOthEx] [NVARCHAR](50) NULL,
        [stFCDocOEx] [NVARCHAR](50) NULL,
        [stMarkupg] [NVARCHAR](50) NULL,
        [stMarkupr] [NVARCHAR](50) NULL,
        [stProdId] [NVARCHAR](50) NULL,
        [stFpaVal] [FLOAT] NULL,
        [strSearchCode] [NVARCHAR](50) NULL,
        [stTextHandle] [NVARCHAR](50) NULL,
        [stVM1Origin] [NVARCHAR](50) NULL,
        [stBM1Origin] [NVARCHAR](50) NULL,
        [stNmCostOrigin] [NVARCHAR](50) NULL,
        [stNmSpInvOrigin] [NVARCHAR](50) NULL,
        [stNtSpInvOrigin] [NVARCHAR](50) NULL,
        [stI nputQuantFlag] [NVARCHAR](50) NULL,
        [stInputValFlag] [NVARCHAR](50) NULL,
        [stOutputQuantFlag] [NVARCHAR](50) NULL,
        [stOutputValFlag] [NVARCHAR](50) NULL,
        [stCancelledFlag] [NVARCHAR](50) NULL,
        [stCancelFlag] [NVARCHAR](50) NULL,
        [FlagsSet] [NVARCHAR](50) NULL,
        [sdInsDate] [NVARCHAR](50) NULL,
        [Expression] [NVARCHAR](50) NULL,
        [Expression1] [NVARCHAR](50) NULL,
        [sdRelDoc] [NVARCHAR](50) NULL,
        [stVatId1] [NVARCHAR](50) NULL
    )

    BULK INSERT Poliseis_STRN
    FROM 'C:\MRP_Import_Files\Unisoft_KEF_TempFiles\2018.xlsm-STRN.csv'
    WITH
    (
        CODEPAGE = '1253',
        FIELDTERMINATOR = ';',
        CHECK_CONSTRAINTS
    ) 
END

and here is a sample of the csv i try to import :

stFileId;stDate;sFileId;stTransKind;stDoc;stLocation;stCustSuppl;stQuant;stQuant2;stPrice;stDiscount;stValue;stComment;stComment2;stSalesman;stForCncy;stFCPrice;stFCValue;stFromFile;stPrinted;stVATid;stFromGroup;stDocOrigin;stInsDate;stUserId;stGLUpdated;stLotDelete;stThirdPart;stFiller1;stLotCode;stLotDate;strcost;stTime;stOtherExp;stWeight;stVolume;stImpClass;stEFK;stDocLine;stDocOthEx;stFCDocOEx;stMarkupg;stMarkupr;stProdId;stFpaVal;strSearchCode;stTextHandle;stVM1Origin;stBM1Origin;stNmCostOrigin;stNmSpInvOrigin;stNtSpInvOrigin;stInputQuantFlag;stInputValFlag;stOutputQuantFlag;stOutputValFlag;stCancelledFlag;stCancelFlag;FlagsSet;sdInsDate;Expression;Expression1;sdRelDoc;stVatId1
1;2/1/2018;718;8;TΔ1000001;1;01C.000001739;500;100;0,36;0;180;DELIFRANCE HELLAS AE/;;;;0,36;180;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;168,08;00/01/1900 00:00;0;0;0;;0;1;0;0;0;0;0;23,40023442;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
2;2/1/2018;733;8;TΔ1000001;1;01C.000001739;480;80;0,69;0;331,2;DELIFRANCE HELLAS AE/;;;;0,69;331,2;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;288;00/01/1900 00:00;0;0;0;;0;2;0;0;0;0;0;43,05643194;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
3;2/1/2018;729;8;TΔ1000001;1;01C.000001739;420;70;0,605;0;254,1;DELIFRANCE HELLAS AE/;;;;0,605;254,1;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;263,34;00/01/1900 00:00;0;0;0;;0;3;0;0;0;0;0;33,03333363;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
4;2/1/2018;6017;8;TΔ1000001;1;01C.000001739;3;0;8;0;24;DELIFRANCE HELLAS AE/;;;;8;24;1;0;21;0;1;02/01/2018 00:00;27;0;0;0;0;;;19,07;00/01/1900 00:00;0;0;0;;0;4;0;0;0;0;0;5,76;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;21
5;3/1/2018;1152;132;ΣΔ1000001;1;99C.000000017;10;0;4,2;0;42;ΔIAΦOPOI/;;;;4,2;42;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;1;0;0;0;0;0;10,07994403;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21
6;3/1/2018;5000;132;ΣΔ1000001;1;99C.000000017;180;0;0,36;0;64,8;ΔIAΦOPOI/;;;;0,36;64,8;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;2;0;0;0;0;0;15,55191238;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21
7;3/1/2018;5001;132;ΣΔ1000001;1;99C.000000017;55;0;0,53;0;29,15;ΔIAΦOPOI/;;;;0,53;29,15;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;3;0;0;0;0;0;6,995960797;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21

I manage to import it successfully only when i use varchar or nvarchar on all table columns, but this is not correct because quantity and value fields are only numeric , and i must use then in sum queries after the import.

Any help would be great.

Not sure about differing code pages. I've been working with 1252 my whole life :). Sorry I can't offer help with your code page error, but it is clear that SQL is having issues with code pages. Are your server and data source using the same code page? — jamie
– jamie, Commented Sep 18, 2018 at 18:03
Beyond that, I've always found it better to load data first into all char/varchar column tables first (as you've stated you already can do). This allows for much more flexibility (with SQL Server/t-sql code) to validate, massage, prep data for later, official use. Just a suggestion to possibly bypass the code page fun and get it into SQL now so you can inspect more closely with sql string/text functions. — jamie
– jamie, Commented Sep 18, 2018 at 18:05
I get the same errors even without the codepage in query. With codepage and nvarchar or varchar , the import is successfull. How can i convert nvarchar or varchar to float for my numeric columns at a second step? — apollonion
– apollonion, Commented Sep 18, 2018 at 18:13
correction.....i had to use [varchar] (max) or [nvarchar] (max) instead of (50) to get the import succeed. — apollonion
– apollonion, Commented Sep 18, 2018 at 18:24
I think there is a misunderstanding, let me clarify. I am not saying that you need to eliminate the use of your codepage. if that is the code page that your incoming data set was created through, then you should use it. My point is just that your file and destination SQL Server table may disagree on codepage to use. — jamie
– jamie, Commented Sep 18, 2018 at 21:04

hamnis · Accepted Answer · 2018-09-18 19:33:26Z

1

Main issue is within a file itself. You use commas (",") for decimal separator. If you want to insert some data as float you should replace commas with points ("."). As far as I know you can't change decimal seperator for BULK INSERT from default point separator and you should follow default sql syntax rules.

Other way is to insert data as varchar and then copy them (for example by another INSERT function) to second table with correct types using REPLACE with CAST/CONVERT functions. For example:

CAST(REPLACE(stQuant,',','.') AS FLOAT)

The other issue with file you posted is column "FlagsSet". Data in your file are longer than 50 characters, so you should you redefine column definition. I just put max:

[FlagsSet] [nvarchar](MAX) NULL

After replacing all commas and changing definition of column "FlagsSet" everything runs smoothly.

Here is corrected file:

1;2/1/2018;718;8;TΔ1000001;1;01C.000001739;500;100;0.36;0;180;DELIFRANCE HELLAS AE/;;;;0.36;180;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;168.08;00/01/1900 00:00;0;0;0;;0;1;0;0;0;0;0;23.40023442;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
2;2/1/2018;733;8;TΔ1000001;1;01C.000001739;480;80;0.69;0;331.2;DELIFRANCE HELLAS AE/;;;;0.69;331.2;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;288;00/01/1900 00:00;0;0;0;;0;2;0;0;0;0;0;43.05643194;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
3;2/1/2018;729;8;TΔ1000001;1;01C.000001739;420;70;0.605;0;254.1;DELIFRANCE HELLAS AE/;;;;0.605;254.1;1;0;1;0;1;02/01/2018 00:00;27;0;0;0;0;;;263.34;00/01/1900 00:00;0;0;0;;0;3;0;0;0;0;0;33.03333363;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;1
4;2/1/2018;6017;8;TΔ1000001;1;01C.000001739;3;0;8;0;24;DELIFRANCE HELLAS AE/;;;;8;24;1;0;21;0;1;02/01/2018 00:00;27;0;0;0;0;;;19.07;00/01/1900 00:00;0;0;0;;0;4;0;0;0;0;0;5.76;;;1;;;;0;0;0;1;1;0;0;1100110111000100000100000000000000000000000000000000000000000000;02/01/2018 00:00;10;30;;21
5;3/1/2018;1152;132;ΣΔ1000001;1;99C.000000017;10;0;4.2;0;42;ΔIAΦOPOI/;;;;4.2;42;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;1;0;0;0;0;0;10.07994403;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21
6;3/1/2018;5000;132;ΣΔ1000001;1;99C.000000017;180;0;0.36;0;64.8;ΔIAΦOPOI/;;;;0.36;64.8;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;2;0;0;0;0;0;15.55191238;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21
7;3/1/2018;5001;132;ΣΔ1000001;1;99C.000000017;55;0;0.53;0;29.15;ΔIAΦOPOI/;;;;0.53;29.15;1;0;21;0;2;02/01/2018 00:00;25;0;0;0;0;;;0;00/01/1900 00:00;0;0;0;;0;3;0;0;0;0;0;6.995960797;;;2;;;;0;0;0;0;0;0;0;1100000000000000000000000000000000000000000000000000000000000000;02/01/2018 00:00;11;12;HΣ1000001;21

Hope it solves your problem.

answered Sep 18, 2018 at 19:33

hamnis

266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

apollonion Over a year ago

Thank you for the help. I had already corrected the FlagsSet error by using (max). I will create a batch file to replace ',' with '.' in .csv file , before importing it to sql table. I tried already to import the .csv file in sql table with all columns set to nvarchar(max) and after insert data to a new table , where i update the columns i want , using replace ',' with '.' and tried to convert nvarchar to float but i get the error "Msg 8114, Level 16, State 5, Line 106 Error converting data type nvarchar to float."

apollonion Over a year ago

After using a batch file to replace ',' with '.' in csv file created , i did the insert of .csv file succefully with numeric columns (like [stQuant] ) been float .

apollonion Over a year ago

So for me the solution was to first replace commas in .csv file and after import it to sql ,like you proposed. The convert / cast didn't work for me , except if i did something wrong (i 'll give it one more workaround tho , cause i like all the coding to be done on sql and not in batch files).Thanks a lot for your guidance and help. Much appreciated.

Jason Clark · Accepted Answer · 2018-09-19 07:10:31Z

0

The common mistake which is done by the user is, file gets saved locally (on their computer) and command is executed on remote instance of SQL Server. As a result, you get this error. Go through the following link, this may help you out: https://blog.sqlauthority.com/2014/11/13/sql-server-fix-msg-4864-level-16-state-1-bulk-load-data-conversion-error/

answered Sep 19, 2018 at 7:10

Jason Clark

1,4838 gold badges28 silver badges51 bronze badges

1 Comment

apollonion Over a year ago

The created .csv file is placed localy on c:\....... of the sql server where the import query is running.

Collectives™ on Stack Overflow

Bulk load data conversion error with csv file in SQL Server 2016

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related