Reputation: 3
Bulk load data conversion error with csv file in SQL Server 2016
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.
Upvotes: 0
Views: 5691
Answers (2)
Reputation: 1425
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/
Upvotes: 0
Reputation: 26
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.
Upvotes: 1
Related Questions
- Bulk load data conversion error (truncation)
- ("IID_IColumnsInfo") error with SQL Server BULK INSERT of CSV file
- Bulk Load Data Conversion Error - Can't Find Answer
- BULK INSERT from CSV into SQL Server causes error
- Bulk Insert Load Failed in SQL Server
- SQL Server Bulk Insert conversion error MSG 4864 HRESULT 0x80004005
- Bulk load data conversion error
- Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
- "BULK LOAD DATA CONVERSION ERROR for csv file
- Bulk load data conversion error from csv file to SQL