Import Excel Sheet datas into mysql
-
Hi, I am working in a Asp.Net Project in which i have to import an Excel sheet datas into a mysql database table and then i want to retrieve those datas into my dotnet application. pls note that i don't use any datagrid in my application. i just designed tables in my Form and get datas in text boxes. i'm new in using mysql database and i already worked in sql server. in sql we have import option and i don't find any of that option in mysql. i'm totally confused in how to import those datas into my database as there are some 50 rows in my excel sheet. I heard that we can extract those datas to database using C# code. pls provide me with a proper solution as i'm new to this concept and i'm struggling with this for the past 3 days(pls note that i don't need any tool to be used for exporting datas as no such tools will be provided for me). Thanks in Advance Vijay.:)
-
Hi, I am working in a Asp.Net Project in which i have to import an Excel sheet datas into a mysql database table and then i want to retrieve those datas into my dotnet application. pls note that i don't use any datagrid in my application. i just designed tables in my Form and get datas in text boxes. i'm new in using mysql database and i already worked in sql server. in sql we have import option and i don't find any of that option in mysql. i'm totally confused in how to import those datas into my database as there are some 50 rows in my excel sheet. I heard that we can extract those datas to database using C# code. pls provide me with a proper solution as i'm new to this concept and i'm struggling with this for the past 3 days(pls note that i don't need any tool to be used for exporting datas as no such tools will be provided for me). Thanks in Advance Vijay.:)
//------------------------------------------- //To Extract data from excel sheet and store it in Dataset. private DataSet PerformQueryIntoDataSet(string strFileName,string sheetName) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 8.0;HDR=NO; IMEX=1;" + Convert.ToChar(34).ToString(); //You must use the $ after the object you reference in the spreadsheet OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+ sheetName + "$]", strConn); DataSet myData = new DataSet(); myCommand.Fill(myData, sheetName); return myData; } //------------------------------------------- //To get number of records in the sheet [Datasetname].Tables[""].Rows.Count; //To extract data from table in dataset. DataRow RowName = (DataRow)[Datasetname].Tables[""].Select().GetValue(rowIndex); string strValue = RowName[0].ToString().ToUpper().Trim(); //Then check for validation and store it in Database.
There's never a Wronge Time To Do The Right Thing !!