title: Bulk Insert Varying Column Numbers in CSV File
body: <p>I am trying to use a BULK INSERT to import data from a CSV file into a table. Below is my working Stored Procedure to accomplish this:</p>
  
  <pre><code>USE [Database]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[delete_fill]
      AS
  
      TRUNCATE TABLE dataImport
  
      BULK
      INSERT dataImport
      FROM 'c:\fileshare\temp.CSV'
      WITH
      (
      FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
      )
  
      SELECT * FROM dataImport ORDER BY StoreID, ItemID
      RETURN
  </code></pre>
  
  <p>Now I have potentially added 2 new columns to the CSV file. How do I modify my stored procedure in order to account for the two new columns while accepting files with only three columns, leaving the two new columns null in the database.</p>
  
  <p>Simply adding two columns to the table dataImport and running the procedure does not work. I receive this error: "Msg 4864, Level 16, State 1, Procedure delete_fill, Line 7
  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Price)."</p>
  
  <p>dataImport structure and csv data example as requested:</p>
  
  <pre><code>Create table dataImport(
  StoreID BIGINT not null,
  ItemID BIGINT not null,
  Price money not null,
  TwoforQuantity Int,
  TwoforPrice money
  )
  </code></pre>
  
  <p>3 Columns:</p>
  
  <pre><code>StoreID, ItemID, Price
  133,51425,11.79
  133,80277,1.49
  133,15544,5.28
  </code></pre>
  
  <p>5 Columns:</p>
  
  <pre><code>StoreID, ItemID, Price, IntField, MoneyField
  133,51425,11.79,2,20.99
  133,80277,1.49,2,2.00
  133,15544,5.29,3,14.99
  </code></pre>