Saturday, 19 October 2013

Import CSV File Into SQL Server Using SQL Bulk Copy

Description:

This article contain C# code example to import CSV file data into DataTable and insert bulk records into SQL server database using SQL Bulk Copy.

Summary:

1. Import CSV file into DataTable C#.
2. Insert Bulk records into SQL Server using SQL Bulk Copy.

Import CSV file into DataTable C#

 We can read data from CSV file in many of custom ways. In this article, I am going to write the C# code to read data from CSV file into .NET DataTable by using TextFieldParser. don't try to search this class in C# library because which is not available in C#. TextFieldParser is the Visual basic class. So we need to add reference dll Microsoft.VisualBasic.

  • Open Visual Studio
  • Go to File ->New ->Project.
  • Then go to Visual C# ->Windows and select Console Application
  • Rename the project name as ReadCSVFile.
  • Right-click the Reference, click Add Reference,select Microsoft.VisualBasic, and click OK button


You can use the following code example to read data from CSV file in C# and pass the DataTable object to the function InsertDataIntoSQLServerUsingSQLBulkCopy  which is available in below section (Insert Bulk records into SQL Server using SQL Bulk Copy).


using System;
using System.Data;
using Microsoft.VisualBasic.FileIO;

namespace ReadDataFromCSVFile
  {
    static class Program
      {
        static void Main()
        {
            string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";
            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
            Console.WriteLine("Rows count:" + csvData.Rows.Count);            
            Console.ReadLine();
        }
    private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();
            try
            {
              using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                 {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }
      }
    }

Insert Bulk Records into SQL Server using SQL Bulk Copy

   You can import CSV File into C# DataTable using the function ReadDataFromCSVFile which is available in above section (Import CSV file into DataTable C#). You can pass this databale object to the following function to insert into SQL Server using SQL Bulk Copy.

            // Copy the DataTable to SQL Server using SqlBulkCopy
function static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
   using(SqlConnection dbConnection = new SqlConnection("Data Source=.\SQLEXPRESS; Initial Catalog=MorganDB; Integrated Security=SSPI;"))
        {
          dbConnection.Open();
          using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
            {
                s.DestinationTableName = "Your table name";

                foreach (var column in csvFileData.Columns)
                s.ColumnMappings.Add(column.ToString(), column.ToString());

                s.WriteToServer(csvFileData);
             }
         }
  }


Related Articles:

How to read data from csv file in c# 
Bulk Insert into SQL Server using SqlBulkCopy in C#
Convert DateTime to Ticks and Ticks to DateTime in C#
Convert Object To Byte Array and Byte Array to Object in C#
Add or Remove programs using C# in Control Panel 
Show balloon tooltip c#


Thanks,
Morgan
Software Developer

Advertisements
Advertisements

9 comments:

  1. Hi

    Just a few corrections. In C# you dont say function when you declare a method and the inputs csvData and csvFileData do not match. Were you high when you wrote this article?

    Stewart

    ReplyDelete
  2. hi friend,
    i am working on import functionality from CSV file.
    i have below fields in file
    SR.No,Name,Age,Gender,Mobile,Address

    here are the data for it.
    1212, -- SR.No
    SampleName, -- Name
    25, -- Age
    Male, -- Gender
    9898525241,9562352521 --Mobile
    AddressFiled -- Address

    Mobile field contains "," delimiter so second mobile number goes to Address Field.

    can you please tell me how to restrict this type of value truncation..

    Thanks in advance.

    ReplyDelete
    Replies
    1. Hi jayesh, in your case, your mobile field value should be enclosed with char " like "9898525241,9562352521" . then the setting csvReader.HasFieldsEnclosedInQuotes = true; will handle your need

      Delete
  3. Hi

    i want to ask from you that there is any method to assign this csv file data to their data types when importing csv file to sql server?

    ReplyDelete
    Replies
    1. no it is not possible ...becoz CSV file is just a text file..so you can't get data types from CSV file....but you can have column and datatype mapping in other source (ex: xml) then you can map it when import csv file

      Delete
  4. hi

    The code above is working where my data from datatable is added into sql DB but only in the 1st column of database table.
    I need datatable to match with 1st row from Csvfile to the columns of database table then add according data to that column.

    ReplyDelete
  5. Hi my name is Kunal I am trying to import a csv file of 4.8 gb but after importing certain records it is giving stat value error of name column which is in hindi plz help me I am in a gr8 mess

    ReplyDelete
  6. Hello Morgan,

    Your code rock! Thank you.

    Is there a way you can help me also on how to import mulitple CSV file?

    Thank you!

    ReplyDelete