Click here to Skip to main content
Click here to Skip to main content
Go to top

Reading .CSV File and Inserting Data to SQL Table using SqlBulkCopy

, 11 Feb 2010
Rate this:
Please Sign up or sign in to vote.
In this article, we will discuss how to use sqlbulkcopy.

Introduction

It’s a common scenario for programmers/users to have situations like they have to insert data in a sqlserver database table from a different data source. It's easy to do so using sqlserver’s integrated data import facility. But what if you’re using sqlexpress which is a light version, which doesn’t have support for data import.

And also data import feature sometimes may not fulfill business requirements. You might not want the user to access the database directly.

What if you have a file which contains 10000000 rows, and you have some validation after reading each single line, and need to parse using certain programming logic. Direct data import won’t work at that time.

It's better to implement your business logic in your code rather than in database using stored procedures, views and functions. It’s not a better idea to write a stored procedure which has 3000 lines. You will definitely suffer from this when the time for debugging this kind of stored procedure will come. Insert, Delete, Update, and Select statement best fit in the database without any validation.

Solution to this Problem

There are many ways to solve this kind of problem. But the most common approach is:

  1. You can use SQL Command
  2. You can use SQLBulkCopy (faster than sqlcommand, but doesn’t fire database triggers)

In this article, we will discuss how to use sqlbulkcopy.

The step by step solution is given below:

  1. First we will create a sqltable using the following lines of code. You need to execute this in the database query window:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') 
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Company]
    GO
    CREATE TABLE [dbo].[Company] (
    [CompanyName] [varchar] (500) 
    COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Volume] [varchar] (500) 
    COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
    ) ON [PRIMARY]
    GO
  2. Second we will create a project. And place the following controls in the default Form1 Form, and place the codes at the same time.
    1. 1 Text Box to specify the server configuration.
    2. 1 Text box to specify the file path of the file that will be read.
    3. 1 Text box to specify where the data will be stored.
    4. 1 Open file dialog to open the specified file.
    5. One button, name it btnLoadFile to load the file path
      private void btnLoadFile_Click(object sender, EventArgs e)
      {
          try
          {
              //I have used a open file Dialog to let the 
              //program know where the file is located
              DialogResult dResult = new DialogResult(); 
              //Dialog result sets the openfile dialog result
              dResult = openFileDialog1.ShowDialog();
              if (dResult == DialogResult.OK)
              //If dialog result is ok, then show the file 
              //name and path in the Text box
              { 
                  txtFilePath.Text = openFileDialog1.FileName;
              }
              else
              {
                  return; //If Dialog result is other than ok, then do nothing
              }
          }
          catch (Exception exp)
          {
              MessageBox.Show(exp.Message);//Catch any exception if occurs
          }
      }
    6. One DataGridView control to show the data
    7. One button, name it btnLoadData to load the data from the file to the gridview
      private void btnLoadData_Click(object sender, EventArgs e)
      {
          try
          {
          if (string.IsNullOrEmpty(txtFilePath.Text.Trim())) //Check if 
      						//file was supplied
          {
              MessageBox.Show("Please supply file name");
              return;
          }
          System.IO.StreamReader rdr = 
          new System.IO.StreamReader(txtFilePath.Text.Trim());
          
          //Stream reader reads a file. File path 
          //and name are supplied from where to read the file. 
          string inputLine = "";
          DataTable dt = new DataTable(); 
          //A data table is similar to a Database table. 
          //Define the columns.
          dt.Columns.Add("CompanyName");
          dt.Columns.Add("Volume");
          DataRow row; //Declare a row, which will be added to the above data table
          while ((inputLine = rdr.ReadLine()) != null) 
          //Read while the line is not null
          {
              string[] arr;
              arr = inputLine.Split(','); 
              //splitting the line which was read by the stream reader object
              row = dt.NewRow();
              row["Companyname"] = arr[0];
              row["Volume"] = arr[1];
              dt.Rows.Add(row);
          }
          dt.Rows.RemoveAt(0); //Remove the first column since its 
          //the column name not necessary to insert in the database table
          dataGridView1.DataSource = dt; //setting the data source to the gridview
          rdr.Close(); //release the stream reader
          }
          catch (Exception)
          {
              throw;
          }
      }
    8. One button, name it btnSaveFile to save the raw data to the database.
      private void btnSaveFile_Click(object sender, EventArgs e)
      {
          try
          {
              if (string.IsNullOrEmpty(txtServerConfiguration.Text.Trim()))
               //Check if server exists
              {
                  MessageBox.Show("Please supply Server Configuration");
                  return;
              }
              if (string.IsNullOrEmpty(txtTableName.Text.Trim())) 
              //Check if Table name was provided 
              {
                  MessageBox.Show("Please supply Table Name");
                  return;
              }
              SqlBulkCopy sqlbulkCopy = new 
              SqlBulkCopy(txtServerConfiguration.Text.Trim()); 
              //Define the Server Configuration
              sqlbulkCopy.DestinationTableName = txtTableName.Text.Trim(); 
              //You need to define the target table name where 
              //the data will be copied
              //Since the requirement is not to read data directly from the file
              //I have included a gridview, where the 
              //raw data will be shown to the user.
              //So that the user can change the data if he wants
              for (int i = 0; i < dataGridView1.ColumnCount; i++) 
              //reading the column names 
              {
                  sqlbulkCopy.ColumnMappings.Add(
                  dataGridView1.Columns[i].Name, 
                  dataGridView1.Columns[i].Name);
                  //Column mapping , supplying source column 
                  //and destination column
              }
              DataTable dt = new DataTable();
              dt = (DataTable)dataGridView1.DataSource;
              //Storing the data in a data table, though you can do it by 
              //directly passing Grid View Data Source to the following function
              sqlbulkCopy.WriteToServer(dt);
              sqlbulkCopy.Close();//Release the resources
              MessageBox.Show("Saved Successfully");
          }
          catch (Exception exp)
          {
              MessageBox.Show(exp.Message);//Catch any exception if occurs
          }
      }

Points of Interest

I could not find a complete solution like this anywhere. I learned how to read file and write it to a database table using SQL bulk copy. Since mistakes happens in life, any comments/information/violent reactions are always welcome.

History

  • 11th February, 2010: Initial post

I will keep this article updated from time to time.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Angsuman Chakraborty
Software Developer NA
Bangladesh Bangladesh
Graduated with IT in 2006.
 
Currently working as a software engineer.

Comments and Discussions

 
Questioncsv to sql Pinmembergspatel4-Apr-14 2:07 
QuestionAdded dynamic Columns from CSV Header Pinmembertaxnology3-Oct-11 21:40 
GeneralAngsuman, Hoping you can help Pinmemberjakekp15-Mar-11 11:12 
GeneralRe: Angsuman, Hoping you can help PinmemberAngsuman Chakraborty15-Mar-11 20:58 
GeneralRe: Angsuman, Hoping you can help PinmemberAngsuman Chakraborty15-Mar-11 21:11 
GeneralRe: Angsuman, Hoping you can help Pinmemberjakekp16-Mar-11 3:15 
GeneralRe: Angsuman, Hoping you can help PinmemberAngsuman Chakraborty16-Mar-11 21:04 
GeneralRe: Angsuman, Hoping you can help Pinmemberjakekp17-Mar-11 4:33 
GeneralMy vote of 5 Pinmembercharlesona24-Jan-11 2:48 
Generalgood article Pinmemberenexooone27-Sep-10 1:44 
QuestionWhat about some code for VB2008 PinmemberBIG_Cookster31-May-10 10:09 
AnswerRe: What about some code for VB2008 PinmemberAngsuman Chakraborty31-May-10 19:39 
GeneralRe: What about some code for VB2008 PinmemberBIG_Cookster1-Jun-10 22:28 
AnswerRe: What about some code for VB2008 PinmemberBIG_Cookster1-Jun-10 22:40 
GeneralRe: What about some code for VB2008 PinmemberAngsuman Chakraborty2-Jun-10 0:51 
GeneralRe: What about some code for VB2008 PinmemberMordred695-Nov-10 8:04 
GeneralRe: What about some code for VB2008 PinmemberAngsuman Chakraborty6-Nov-10 18:56 
AnswerRe: What about some code for VB2008 Pinmembercharlesona24-Jan-11 2:41 
Generalreading .CSV file And Inserting Data to SQL Table using SqlBulkCopy Pinmemberseun adebayo26-Mar-10 5:56 
GeneralRe: reading .CSV file And Inserting Data to SQL Table using SqlBulkCopy PinmemberAngsuman Chakraborty27-Mar-10 18:37 
Generalreading CSV file PinmemberMember 468157115-Feb-10 0:01 
GeneralMy vote of 2 [modified] PinmemberAlaric Dailey11-Feb-10 9:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 11 Feb 2010
Article Copyright 2010 by Angsuman Chakraborty
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid