How can we import excel file to Sql server table where excel table columns are not in order as Sql table

Question

I have an excel file whose worksheets name same as SQL table name but the column mapping is failing as both have same columns but the order of columns are different , please help me on this.

Answer 1

Edited: Added another example at the end.

One of many ways of implementing what you ask is to import worksheet as a datatable in c# and then Insert the data with SqlBulkCopy (SqlBulkCopy (MSDN)). This method is better for large files because SqlBulkCopy uses bulk insert command.

For the first step (import file as datatable) you have many options such as using OLEDB for xls or xlsx (you can use my example or others such as this link or this), using third-party libraries such as easyxls.

using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection ;
                System.Data.DataSet DtSet ;
                System.Data.OleDb.OleDbDataAdapter MyCommand ;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
                MyCommand.TableMappings.Add("Table", "TestTable");
                DtSet = new System.Data.DataSet();
                MyCommand.Fill(DtSet);
                dataGridView1.DataSource = DtSet.Tables[0];
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
   }
}

After that for second step you can use SQLBulkCopy with column mapping to map the dataTable columns to your database table columns.

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM " +
                "dbo.BulkCopyDemoDifferentColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, " +
                "ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            // Set up the bulk copy object.
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(connectionString))
            {
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoDifferentColumns";

                // Set up the column mappings by name.
                SqlBulkCopyColumnMapping mapID =
                    new SqlBulkCopyColumnMapping("ProductID", "ProdID");
                bulkCopy.ColumnMappings.Add(mapID);

                SqlBulkCopyColumnMapping mapName =
                    new SqlBulkCopyColumnMapping("Name", "ProdName");
                bulkCopy.ColumnMappings.Add(mapName);

                SqlBulkCopyColumnMapping mapMumber =
                    new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
                bulkCopy.ColumnMappings.Add(mapMumber);

                // Write from the source to the destination.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the using block.
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static string GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

another example of SqlBulkCopy usage:

public bool CopyTransactionDataToTable(DataTable Dt, long ProductID)
    {

    try
    {
        SqlBulkCopy copy = new SqlBulkCopy(Adapter.GetActiveConnection().ConnectionString);
        Collection = mapping.LoadMappedNameEntityByProductID(ProductID);

        copy.ColumnMappings.Add("ProductID", "ProductID");
        copy.ColumnMappings.Add("ResellerID", "ResellerID");
        copy.ColumnMappings.Add("Status", "Status");
        copy.ColumnMappings.Add("PK_ID", "TxID");
        copy.DestinationTableName = "TBLProdect";
        copy.BulkCopyTimeout = ConfigurationSettings.AppSettings.Get(UIConstants.SQLTimeOut).ToInt32();
        copy.WriteToServer(Dt);
        Adapter.CommandTimeOut = copy.BulkCopyTimeout;
        return true;
    }
    catch (Exception ex)
    {
        Log.Error(ex);
        return false;
    }
}

Answer 2

You didn't indicate if you were looking to make this repetitive, or programmatic, etc - but two other options would be to use the SQL Server Data Import/Export Wizard

https://msdn.microsoft.com/en-us/library/ms141209.aspx

Another option would be to use SQL Server Integration Services (SSIS)

http://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/