How to import bulk data to sql server from excel/csv

Question

My requirement is that i have 2l data in excel/CSV file which has email id in each row, I have to import those data at one shot, i.e bulk copy to SQL server by verifying one data(email) at a time.

Answer 1

You could also use Microsoft Visual Studio's Business Intelligence tools. By creating a SSIS (SQL Server Integration Services) project you can use various drag and drop tools to create "packages" (or jobs if you wish) which you can execute to perform jobs like these.

You can import data from a wide variety of data sources including Excel, CSV, MySQL, SQL Server and Hadoop to name a few.

You can also write that data from those sources to not only SQL Server but a wide variety of other data destinations as well.

I am using Visual Studio 2015 with the Business Intelligence packages installed.

What I would recommend is:

1. Start Visual Studio and open a new SSIS (SQL Server Integration Services) project.
2. Under the control flow tab. Add a new Data Flow task to the control flow area.
3. Double click on the control flow item or navigate to the Data Flow tab.
4. Make sure you data flow item is selected. (Should be if you double clicked it.)
5. From there you can use the Source and Destination Assistants to transport your data.
6. Once done setting up you source, destination, data transformations and checks. You can hit Start and it will execute the package.

P.S: You can also use the script component in the data flow tab to write custom C# script if you want to.

If we had an example of the Schema (Table structure) you were transporting from and to it would have helped with providing an example.

Best of luck

Answer 2

I have specified the connection strings for the Excel files of both 2003 and 2007 or higher formats in the Web.Config file.

<add name = "Excel03ConString" connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name = "Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

You will need to import the following namespaces.

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

Add the following code :

//Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);

    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Salary",typeof(decimal)) });

        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }