Import CSV rows with 2000 records from node into SQL Server

Question

I am using exceljs (https://www.npmjs.com/package/exceljs) node package to read excel file.

I am looking for fast import csv with 2000 records into SQL Server.

Table:

User [Id PK]
Role [UserId FK]
Unit [UserId FK]

Excel file:

UserName Role  Unit 
Jack      1    Unit1

score 1 · Accepted Answer · answered Feb 06 '18 at 07:02

1

@Furqan, did you try executing BULK INSERT command to import CSV file contents into SQL Server database table

Here is sample target table

create table UploadTable (
    [User] varchar(100),
    [Role] varchar(100),
    Unit varchar(100)
)

After you create your table try following SQL command

BULK INSERT UploadTable FROM 'c:\kodyaz\upload.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0A'
)

answered Feb 06 '18 at 07:02

Eralper

How can we rollback the transaction and get the information of specific row error ? – Furqan Misarwala Feb 06 '18 at 09:50
It is not possible to rollback as far as I know. But if you have used BATCHSIZE option that would divide the all data file into smaller row sizes and try to insert it in smaller chunks. Possible errors are data type conversions and/or longer text which will result trimming of original values. You can also validate the seperator character and end-of-line characters, too. Please refer to https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql – Eralper Feb 06 '18 at 10:43

1 Answers1