how to Upload a excel file to sql database table using c# windows form application

using System;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Windows.Forms;namespace IMPORT{public partial class Form1 : Form{    public Form1()    {        InitializeComponent();    }    String MyConString = "SERVER=******;" +           "DATABASE=db;" +           "UID=root;" +           "PASSWORD=pws;";private void btnSelectFile_Click(object sender, EventArgs e)    {        OpenFileDialog openfiledialog1 = new OpenFileDialog();        openfiledialog1.ShowDialog();        openfiledialog1.Filter = "allfiles|*.xls";        txtfilepath.Text = openfiledialog1.FileName;    }private void btnUpload_Click(object sender, EventArgs e){ string path = txtfilepath.Text;        string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties = Excel 8.0";        DataTable Data = new DataTable();        using (OleDbConnection conn =new OleDbConnection(ConnString))        {            conn.Open();            OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [dataGridView1_Data$]", conn);            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);            adapter.Fill(Data);            conn.Close();        }        string ConnStr = MyConString;        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnStr))        {            bulkCopy.DestinationTableName = "TABLE NAME";            bulkCopy.ColumnMappings.Add("userid", "userid");            bulkCopy.ColumnMappings.Add("password", "password");            bulkCopy.ColumnMappings.Add("first_name", "first_name");            bulkCopy.ColumnMappings.Add("last_name", "last_name");            bulkCopy.ColumnMappings.Add("user_group", "user_group");            bulkCopy.WriteToServer(Data);            MessageBox.Show("UPLOAD SUCCESSFULLY");        }     } }

An example found http://technico.qnownow.com/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/.AndERROR: Additional information: External table is not in the expected format

ஆர்த்தி,

Use the Below Connection String Format

 string File = sResponsedExcelFilePath; string result = Path.GetFileName(sFilePath);     ExcelReaderConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File +"\\"+ result + ";Extended Properties=Excel 12.0;");

Hope this works for you.

There is an awesome link that shows how to upload to c# datatable from excel...in case the link dies I am sharing the procedure....

The excel Connection Strings for diff versions:

private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";

The File select event:

private void BtnSelectFile_Click(object sender, EventArgs e)    {        DataTable dt;        OpenFileDialog dlg = new OpenFileDialog();        dlg.Filter = "Excel files | *.xls";        if (dlg.ShowDialog() == DialogResult.OK)        {            string filePath = dlg.FileName;            string extension = Path.GetExtension(filePath);            string conStr, sheetName;            conStr = string.Empty;            switch (extension)            {                case ".xls": //Excel 97-03                    conStr = string.Format(Excel03ConString, filePath);                    break;                case ".xlsx": //Excel 07 to later                    conStr = string.Format(Excel07ConString, filePath);                    break;            }            //Read Data from the Sheet.            using (OleDbConnection con = new OleDbConnection(conStr))            {                using (OleDbCommand cmd = new OleDbCommand())                {                    using (OleDbDataAdapter oda = new OleDbDataAdapter())                    {                        dt = new DataTable();                        cmd.CommandText = "SELECT * From [Sheet1$]";                        cmd.Connection = con;                        con.Open();                        oda.SelectCommand = cmd;                        oda.Fill(dt);                        con.Close();                    }                }            }            //Save the datatable to Database            string sqlConnectionString = MyConString;            if(dt != null)            {                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))            {                bulkCopy.ColumnMappings.Add("[userid]", "userid");                bulkCopy.ColumnMappings.Add("password", "password");                bulkCopy.ColumnMappings.Add("first_name", "first_name");                bulkCopy.ColumnMappings.Add("last_name", "last_name");                bulkCopy.ColumnMappings.Add("user_group", "user_group");                bulkCopy.DestinationTableName = "aster_users";                bulkCopy.WriteToServer(dt);                MessageBox.Show("Upload Successfull!");            }            }        }}

Then you can just save the datatable to mySql database which I hope you know how to do...If you can't then comment I'll try my best to help you. Thank You

Hope this helps....