How to Bulkcopy an excel file to an sql table

Question

I wanted to make a bulkcopy from an excel file to an sql table. But you’re giving me this error when I try to send to sql:

System.Invalidoperationexception: 'The Columnname indicated 'Transdate' does not match any column ma data source.'

The code I’m using to make the bulkcopy is as follows :

string ConecçãoDB = ConfigurationManager.ConnectionStrings["ConecçaoDB"].ConnectionString;
            string Table = ConfigurationManager.AppSettings["table"];

            string ssqltable = Table;

            string ssqlconnectionstring = ConecçãoDB;
            string sclearsql = "delete from " + ssqltable;


            var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
            var sql = "SELECT * FROM [" + comboBox1.Text + "$]";
            MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            MessageBox.Show(sql);
            OleDbConnection oledbconn = new OleDbConnection(connectionString);
            OleDbCommand oledbcmd = new OleDbCommand(sql, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
                using (SqlConnection con = new SqlConnection(ssqlconnectionstring))
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
                    {

                    bulkCopy.DestinationTableName = ssqltable;
                    bulkCopy.ColumnMappings.Add(comboBox2.Text, "TransDate");
                        con.Open();
                    bulkCopy.WriteToServer(dr);
                        con.Close();
                        try
                        {
                        bulkCopy.WriteToServer(dr);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            // Close the SqlDataReader. The SqlBulkCopy
                            // object is automatically closed at the end
                            // of the using block.
                            MessageBox.Show("");
                        }
                    }

                }

            oledbconn.Close();
            button2.Enabled = true;
            oledbconn.Close();
        }

This is my excel file :

This is my database :

If you need anything else or code just ask. Thank you.

Answer 1

I didn’t quite understand the reason for yours comboBox2.Text. Anyway, you need to map the worksheet columns with the BD table columns, as follows:.

Change the connection string with Excel by placing the parameter HDR=YES. This amendment is important for the DataReader dr contains the names of the columns.

// mapear coluna da planilha Excel com a coluna da tabela do BD
bulkCopy.ColumnMappings.Add("TransDate", "TransDate");
bulkCopy.ColumnMappings.Add("TransTime", "TransTime");
bulkCopy.ColumnMappings.Add("CardNo", "CardNo");
bulkCopy.ColumnMappings.Add("VoucherNo", "VoucherNo");
bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
bulkCopy.ColumnMappings.Add("TransactionValue", "TransactionValue");