Bulk Insert Data Into SQL Table

Transact-SQL
1

Hi everyone. I am working on SQL Server 2016.

I am trying to insert data from a csv file which has total 9056 rows.
I have a question

How do I tell my Bulk Insert query to only import data upto 9055 rows instead of complete 9056 rows?
I have manually entered the value 9055 but I want system to calculate the number of rows on csv file and only import up to second last row and avoid 9056 row as it contains End of file character which gives error.

BULK INSERT temp3
FROM 'E:...\sdn.csv'
WITH
(
FIRSTROW = 1, --COLUMN NAMES WILL BE AUTO
FIELDTERMINATOR = '|', --CSV FIELD DELIMETER
ROWTERMINATOR = '\n', --USE TO SHIFT TO CONTROL TO NEXT LINE
LASTROW =9055,
TABLOCK
)

2

The only thing you can do is to import first in a table with one row only. Count the rows and start the next load into the correct table. Another option is to use powershell and open the file to delete the last row.

I assume MAXERRORS doesn't work as the default is 10?