How to bulkcopy an excel file into an sql table

3

I wanted to bulkcopy an excel file into an sql table. But it's giving me this error when I try to send to sql:

  

System.InvalidOperationException: 'The specified ColumnName' TransDate '   does not match any column of data source. '

The code I'm using to 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.

    
asked by anonymous 30.10.2017 / 12:09

1 answer

2

I did not really understand why your comboBox2.Text . Anyway, you need to map the columns of the spreadsheet to the columns of the DB table, as follows.

Change the connection string to Excel by setting the HDR = YES parameter. This change is important so that DataReader dr contains the column names.

// 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");
    
30.10.2017 / 13:19