Hi, in this article, I am going to write SQL script to Import or Insert CSV file data into SQL Server using Bulk Insert and C# code to Read CSV file and Insert them into SQL Server using Bulk Insert.
SQL Script to Import CSV file into SQL Server using Bulk Insert
Here, we have considered the StudentsData table with three columns to read and store data from CSV file.
Use MorganDB GO Create Table StudentsData ( UserName VARCHAR(250), City VARCHAR(250), MailID VARCHAR(250), );
The data we are going to load into SQL Server using Bulk Insert is stored in the CSV File – UserData.CSV . The below image shows sample CSV content.
Now, to read the CSV file, you can use the following SQL Script with the Bulk Insert command
Use MorganDB GO BULK INSERT StudentsData FROM 'C:\UsersAdministratorDesktopUserData.csv' WITH ( FIRSTROW = 2, FIELDTERMINATOR =',', ROWTERMINATOR ='n' )
Here, we have set FIRSTROW = 2 to exclude the first row and start reading data from the second row in the CSV file. this is because as you know we have treated the first row as a column header in the CSV file and
FIELDTERMINATOR is used to separate column values by char ‘,’ and ROWTERMINATOR is used to split rows by the char ‘n’.
Imported CSV File Output in SQL Server
Use [MorganDB] Go SELECT [UserName],[City],[MailID] FROM [StudentsData]
Read or Import CSV file into SQL Server using Bulk Insert in C#
You can use the following C# function to Read or Import CSV file data into SQL Server.
static void Main(string[] args) { ImportCSVFileIntoSQLServer(); } private static void ImportCSVFileIntoSQLServer() { try { using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.SQLExpress; Initial Catalog=MorganDB; Integrated Security=SSPI;")) { sqlconnection.Open(); SqlCommand command = new SqlCommand(@"BULK INSERT StudentsData FROM 'C:\Users\Administrator\Desktop\UserData.csv' WITH ( FIRSTROW = 2, FIELDTERMINATOR =',', ROWTERMINATOR ='\n' )", sqlconnection); command.ExecuteNonQuery(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
Thanks,
Morgan
Software Developer
Thank You, very clean code I looked for c# bulk insert and almost all were bulk copy.
ROWTERMINATOR should be
‘\n’
or
‘0x0A’
Thanks updated.