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