Product:
Microsoft SQL server 2016 standard
Microsoft Windows 2016 server

Problem:
How load data from csv file into SQL server, where the table contains more columns than the text file?

The BULK INSERT command will fill the not used target columns with the next row of data, and you get a inconsistent table.

Solution:

Create a view of the table, where you have less columns shown, and then bulk insert to the view.

From inside SSMS script out a select of the table, and add create view to first line, remove the columns you do not want.

CREATE VIEW StorageView AS
SELECT [Organisation]
,[Version]
,[Belopp]
FROM StorageTable

Then in your BULK INSERT us the view instead, so the csv files number of columns match the (view) target tables number of columns.

BULK INSERT [StorageView]
FROM 'C:\temp\storagefile.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\r',
FIRSTROW = 2
);

 

FIELDTERMINATOR = set the separator for the columns in the csv file
ROWTERMINATOR = set the character to skip to next row/record
FIRSTROW = tell that first line in the csv file is headers and should not be read

If you get a error like this:

Msg 4832, Level 16, State 1, Line 16
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 16
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 16
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

Then the csv file have a blank line as the last line, edit your csv file to not contain any empty lines.

 

More Information:

https://www.sqlservertutorial.net/sql-server-administration/sql-server-bulk-insert/ 

https://www.w3schools.com/sql/sql_view.asp

https://www.w3schools.com/sql/sql_update.asp

https://www.mssqltips.com/sqlservertip/6109/bulk-insert-data-into-sql-server/