Importing excel file into SQL with different schema (asp.net C#)

Question

I'm building a program where the user imports an excel file to a database (SQLServer)... But I Do not want to specify the columns name cause it makes my program very limit, to those column names.... I don't know how to work with datatable and rows very well, but I think its the only way right? (im a newbie, sorry)

Here's the code Guys:

rotected void Upload_Click(object sender, EventArgs e)
        {
            string sSheetName;
            DataTable dtTablesList = new DataTable();

            string excelPath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            string filepath = Server.MapPath("~/Nova pasta/") + Path.GetFileName(FileUpload1.FileName);
            string filename = Path.GetFileName(filepath);
            FileUpload1.SaveAs(excelPath);
            string ext = Path.GetExtension(filename);
            String strConnection = @"Data Source=PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Persist Security Info=True;User ID=sa;Password=1234";
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";

            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

            excelConnection.Open();
            dtTablesList = excelConnection.GetSchema("Tables");

            if (dtTablesList.Rows.Count > 0)
            {
                sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();


                for (int j = 0; j < dtTablesList.Rows.Count; j++)
                {
                    for (int i = 0; i < dtTablesList.Columns.Count; i++)
                    {
                        Debug.Write(dtTablesList.Columns[i].ColumnName + " ");
                        Debug.WriteLine(dtTablesList.Rows[j].ItemArray[i]);

                    }
                }

                Debug.WriteLine(dtTablesList.Rows.Count);
                foreach (DataRow dataRow in dtTablesList.Rows)
                {
                    foreach (var item in dataRow.ItemArray)
                    {
                        Debug.WriteLine(item);
                    }
                }

                    OleDbCommand cmd = new OleDbCommand("Select * from ["  + sSheetName + "]", excelConnection);

                    cmd.ExecuteNonQuery();
            }

You can use [bcp utility](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017) command line utility from Microsoft - save your excel as CSV, create a destination table in SQL Server and you should be good to go. — knyazs, May 09 '19 at 10:04
@knyazs I want to import excel files to a database with different columns names — , May 09 '19 at 10:07
@OverKnown - bcp utility can ignore first row (i assume with header names) and just use column in the same order to load it from file to a table. — knyazs, May 09 '19 at 10:23
@knyazs You are not getting... I want to be able to import excel data to sql database but excel having different column name.. exemplo: 'Products'-database 'ProductsName'- excel but they have the saem value — , May 09 '19 at 10:31
If that's the case, then you are looking at something like EPPlus to read the excel and then present some sort of mapping options to the user for them to choose and validate. — Richard Housham, May 09 '19 at 11:28
So you see this is far more complicated than it looks - maybe beyond your skills at this point. You did not really ask a question and that is one reason why this is off-topic for SO. You need to define your goals with this program first. Look at the Import wizard in SSMS to see what it does and determine how you intend to "improve" (or at least duplicate) its functionality. — SMor, May 09 '19 at 13:00

gen · Answer 1 · 2019-05-09T11:59:32.600

I am not sure what exactly you are looking for. In the past I have used a component called EPPlus. I used this solution to turn a worksheet into a datatable. See here: Excel to DataTable using EPPlus - excel locked for editing

Edit: When you have the datatable of your excel worksheet you can do something like this:

        using (SqlConnection con = new SqlConnection("connectionstring"))
        {
            try
            {
                con.Open();
                foreach (DataRow dr in worksheetTable.Rows)
                {
                    using (SqlCommand myCommand = new SqlCommand("insert into myTable (Products, column2) values (@prod, @col2)", con))
                    {
                        myCommand.CommandType = CommandType.Text;

                        myCommand.Parameters.AddWithValue("prod", dr[0]);
                        myCommand.Parameters.AddWithValue("col2", dr[1]);

                        int result = myCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                //handle errors here
            }
            catch (Exception ex)
            {
                //handle errors here
            }
            finally
            {
                con.Close();
            }
        }

(Code might not be perfect, I did not test it)

It depends a lot of the structure of your excel and database. I hope this helps you.

I want to import excel files to a database with different columns names — , May 09 '19 at 10:07

Importing excel file into SQL with different schema (asp.net C#)

1 Answers1