Bulk upload for .xlsx and .csv format

We have a requirement in the project in which we want to upload data from file having .xlsx format (or.csv format) into database table. But we want to do bulk upload rather than row by row upload, because we have over 1 lakh records in the file.

 

We have to do that using 2 methods

1.   .net code

2.   SSIS package

 

In .net code we are using SQLBulkCopy class for doing bulk upload for excel file(i.e. file with .xls and.xlsx extension). This approach works fine when we have excel file in .xls format, but when I use .xlsx format I am not able to do that because it requires some component to be installed on the machine where application is deployed. And this installation thing can create issues when we go to higher environments.

 

But in case of csv format the processing is done on row by row format which has a performance impact on an application.

 

Using SSIS package we can use Data flow task. But we have following issues in this approach.

-          Rows get inserted one by one

-          Lot of performance issues faces due to large amount of data.

 

 

So my question is

1.   Can we do bulk upload the data from excel file(both .xls and .xlsx extension) and .csv file using .net code?

2.   Can we do bulk upload the data from excel file(both .xls and .xlsx extension) and .csv file using SSIS package?

SOLUTION'S 1: 

Try using Linq to Excel functionality. It will load the excel file data into linq object and at one go you can save it in database.

SOLUTION'S 2: 

You can use bulk copy as mentioned below:

 

 public void GetinsertADValuetoSql()
        {
            //throw new NotImplementedException();
            DataTable dtSQLvalue = new DataTable();

            dtSQLvalue = ReadValueFromExcel();

            int count = dtSQLvalue.Rows.Count;
            if (count > 0)
            {
                //Get connection string from web.config file
                string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                TraceService("Another entry at " + strcon);
                int iSucess = 0;
                int ItemID = 0;
                for (int irow = 0; irow < count; irow++)
                {
                    //create new sqlconnection and connection to database by using connection string from web.config file
                    using (SqlConnection openCon = new SqlConnection(strcon))
                    {
                                               string saveADValue = "INSERT INTO [PWR].[dbo].[Adopt Decision]([XYZID],[Asset],[Adopted],[Remarks] ,[Savings Realized],[Date of Decision])      VALUES   (@XYZID,@Asset,@Decision,@DecidersComments,@Anticipatedsavings,@DateofDecision)";
                        using (SqlCommand querySaveADValue = new SqlCommand(saveADValue))
                        {
                            querySaveADValue.Connection = openCon;
                            openCon.Open();

/// Bulk copy operation
                              using (SqlBulkCopy copy = new SqlBulkCopy(openCon))
                              {
                                  copy.ColumnMappings.Add("XYZID", "XYZID");
                                  copy.ColumnMappings.Add("Asset", "Asset");
                                  copy.ColumnMappings.Add("Adopted", "Decision");
                                  copy.ColumnMappings.Add("Remarks", "Deciders Comments");
                                  copy.ColumnMappings.Add("Savings Realized", "Anticipated savings");
                                  copy.ColumnMappings.Add("Date", "Date of Decision");
                            
                                  copy.DestinationTableName = "[Adopt Issue]";
                                  copy.WriteToServer(dtSQLvalue);
                              }
                              openCon.Close();

                                                       

                }
            }
        }