title: Bulk load data conversion error with csv file in SQL Server 2016
body: <p>I have a <code>.csv</code> file. I want to import it into my SQL Server database called <code>Unisoft_kef</code>, but I get these errors :</p>
  
  <blockquote>
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (stQuant).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 45 (stFpaVal).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 10 (stPrice).  </p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 10 (stPrice).</p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 10 (stPrice). </p>
    
    <p>Msg 4864, Level 16, State 1, Line 79<br>
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 10 (stPrice).  </p>
    
    <p>Msg 4865, Level 16, State 1, Line 79<br>
    Cannot bulk load because the maximum number of errors (10) was exceeded.</p>
    
    <p>Msg 7399, Level 16, State 1, Line 79<br>
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.</p>
    
    <p>Msg 7330, Level 16, State 2, Line 79<br>
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".</p>
  </blockquote>
  
  <p>The exact query I use is : </p>
  
  <pre><code>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
  </code></pre>
  
  <p>and here is a sample of the csv i try to import :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help would be great.</p>