Read CSV File and Insert Into SQL Server using Bulk Insert

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