How to upload excel file to sql database table using c # form application

Question

I want to upload an excel file through a windows form application in C # and want to import data into a database (Mysql server). How can I do it??? I created a form that requires me to upload an excel file to the mysql database. its voluminous insert data into the database table.

My Excel file Contains columns such as userid, password, first_name, last_name, user_group AND MySql database table (aster_users) Contains many columns such as userid, password, first_name, last_name, user_group, queue, active, created_date, created_by, role ..

I need to upload this excel file to my database, and other columns will become empty or null, which is not an issue.

My shape design

Here is my C # code:

using MySql.Data.MySqlClient; using System; using System.Data.SqlClient; using System.Windows.Forms; namespace UploadFileToDatabase { public partial class Form1 : Form { public Form1() { InitializeComponent(); } String MyConString = "SERVER=******;" + "DATABASE=dbs;" + "UID=root;" + "PASSWORD=pwsd;" + "Convert Zero Datetime = True"; private void BtnSelectFile_Click(object sender, EventArgs e) { OpenFileDialog dlg = new OpenFileDialog(); dlg.Filter = "Text files | *.csv"; if (dlg.ShowDialog() == DialogResult.OK) { string fileName; fileName = dlg.FileName; txtfilepath.Text = fileName; } } private void btnUpload_Click(object sender, EventArgs e) { string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtfileparth.Text + ";Extended Properties=\"Excel 12.0;HDR=YES;\""; using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand ("Select * FROM [Sheet1$]", connection); connection.Open(); using (DbDataReader dr = command.ExecuteReader()) { string sqlConnectionString = MyConString; 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(dr); MessageBox.Show("Upload Successfull!"); } } } } 

This is how I tried .i got an error message like this

Additional information: the external table is not in the expected format.

in this line connection.Open (); . How can I do that?

Answer 1

 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"); } } } 

The above example is http://technico.qnownow.com/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/ . AND ERROR: Additional Information: The external table is not in the expected format

Answer 2

ஆர்த்தி,

Use the following 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.

Answer 3

There is an amazing link that shows how to load in C # datatable from excel ... in case link dies I share the procedure ....

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'"; 

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 database to mySql database, which I hope you know how to do it ... If you can’t comment, I will try my best to help you. Thank you.

Hope this helps ...

Answer 4

 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"); } } }