Monday, 24 March 2014

Read CSV File and Insert Into SQL Server using Bulk Insert

Description:

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 into SQL Server using Bulk Insert.

SQL Script to Import CSV file into SQL Server using Bulk Insert

Here, we have considered 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.

Import CSV File Into SQL Server using Bulk Insert

Now, to read CSV file, you can use following SQL Script with Bulk Insert command
Use MorganDB
GO
BULK INSERT StudentsData
FROM 'C:\Users\Administrator\Desktop\UserData.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)

Here, we have set FIRSTROW = 2 to exclude first row and start read data from second row in CSV file. this is because as you know we have treated first row as column header in 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#


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

Advertisements
Advertisements

No comments:

Post a Comment