Best way to import excel/csv/txt file into MySQL

Question

HI All, I would like to know what are the best way to import EXCEL/CSV/TXT file into MySQL using c#.net Desktop app. Thanks in advance ..

Candy Brown · Answer

There are couple of ways you can do this. Although Imtiyaz's code does not show it, you can use an excel automation in .NET in order to create a DataTable object from the existing excel file, but this can be a rather slow approach and also it will require a MS Excel installed on the machine. A faster approach would be to use OLEDB for this. But yes I presume that a LOAD DATA INFILE would have the best performances, but you cannot use it with an excel file because it requires a text file. So if you would first convert your Excel file into a CSV format in C# with something like this .NET component for fast excel processing then you could use it.

Tulasi · Answer

Thanks for the reply . But my question is howto import excel/csv/txt data into MySql ? i possible way is Using LOAD DATA INFILE query . is there any other alternative and better approach to insert data into mysql?

Imtiyaz Ansari · Answer

namespace add firstly Microsoft.Office.Interop.Excel.dll gridview data create excel file :- try { string filename1 = "Damage Product List"; filename1 = "D:\Report\" + filename1 + ".xls"; Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (j = 0; j { xlWorkSheet.Cells[1, j + 1] = dataGridView1.Columns[j].Name; } for (i = 0; i { for (j = 0; j { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 2, j + 1] = cell.Value; } } xlWorkBook.SaveAs(filename1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); MessageBox.Show("Excel file created , you can find the file"); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); return; }

Best way to import excel/csv/txt file into MySQL

Answers (3)