Click here to Skip to main content
13,351,457 members (44,765 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 8 Feb 2010

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

, 11 Feb 2010
Rate this:
Please Sign up or sign in to vote.


This trick is a complete solution to the programmers who want a solution to read CSV file using Open file dialog, store the raw data in a Data Grid View control and lastly insert the raw data in a SQL table using SQLbulkcopy.

Using the Code

First u need to create a Database with a table named Company for example. The table creation SQL script is provided below.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]

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 

The sql table column names have to be similar to the column names that you have in the .CSV file .

You need to create a C#.Net Project and 
place the following controls on a form:
1.Three Textbox
2.Three Buttons 
3.One DataGridView
4.Lastly one OpenFileDialogControl

And paste the code and modify the above controls name as below
I am not going to indent the following code. ;P
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; 
//Include this name space inorder to use SqlBulkCopy
namespace FileReader
    public partial class Form1 : Form
        public Form1()
        private void btnSaveFile_Click(object sender, EventArgs e)
                if (string.IsNullOrEmpty(txtServerConfiguration.Text.Trim())) //Check if server exists
                    MessageBox.Show("Please supply Server Configuration");
                if (string.IsNullOrEmpty(txtTableName.Text.Trim())) 
                    //Check if Table name was provided 
                    MessageBox.Show("Please supply Table Name");

                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 grid view, 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 
                    //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.Close();//Release the resources
                MessageBox.Show("Saved Successfully");
            catch (Exception exp)
                MessageBox.Show(exp.Message);//Catch any exception if occurs

        private void btnLoadFile_Click(object sender, EventArgs e)
                //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;
                    return; //If Dialog result is other than ok then do nothing
            catch (Exception exp)
                MessageBox.Show(exp.Message);//Catch any exception if occurs

        private void btnLoadData_Click(object sender, EventArgs e)

                if (string.IsNullOrEmpty(txtFilePath.Text.Trim())) //Check if File was supplied
                    MessageBox.Show("Please supply file name");
                System.IO.StreamReader rdr = new System.IO.StreamReader(txtFilePath.Text.Trim()); 
                //Stream reader reads a file. File path 
                //and name is 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.
                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.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 grid view
                rdr.Close(); //release the stream reader
            catch (Exception)

Take a look on the code.
You need to do the follwing:
  1. Define a server configuration.
  2. Set a Table Name

That's it. Happy coding :-D


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


About the Author

Angsuman Chakraborty
Software Developer NA
Bangladesh Bangladesh
Graduated with IT in 2006.

Currently working as a software engineer.

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 12 Feb 2010
Article Copyright 2010 by Angsuman Chakraborty
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid