How to read structured Excel Data into database ?

Question

Hi I am developing a webApp that will take data from an Excel workbook and store the info in the database. MY problem is that the Excel data is structured in the following way : Category | Course | Topics Now for one category there can be many courses and each course can have many topics. So its a 1:Many relationship between each of those columns. Now here is an Example : Category | Course | Topics Cat 1 Course1 Topic1 Topic2 Topic3 Topic4 Topic4 Cat 2 Course2 Topic1 Topic2 . . . . . . . . ETC/..... So how can I write a code or use some method of the DataTable which will read all the Courses that belong to one particular category ? Or all the topics in one particular course ?

sandeep ram · Answer

Hi all thanks for all the help I have got the data into the database now - I am using LINQ statements instead of passing data through a BO. Now my problem is that the excel file contains about 6 categories42 courses and some 845 topics in total. So thats 845 rows in total. And its taking about 20sec to upload this file to the database. Thats too long. How can I reduce the time lapse here ?

jinge · Answer

You are welcome it is a pity that this forum only allow one post as accepted.

sandeep ram · Answer

Ok I have got it now. I will develop this further to suit my needs. Thanks to both Jinge and Kirtan :)

jinge · Answer

The select method of the datatable only accpet fitler string. You can either use Linq query as above or use Select or Computer method of the DataTable or filter ways of the dataView.

Kirtan Patel · Answer

Here is LINQ Version private void Form1_Load( object sender EventArgs e) { DataTable dt = new DataTable () ; var query = from c in dt.AsEnumerable() select new { Week = c.Field string >( Week ) Person = c.Field string >( Person ) Actual = c.Field string >( Actual ) }; dataGridView1.datasource = query; } friend please mark do you like this answer if my answer helped you

jinge · Answer

use this: DataTable dt = new DataTable(); dt.Columns.Add(Name typeof(string)); dt.Columns.Add(Age typeof(int)); dt.Rows.Add(AA 22); dt.Rows.Add(BB 33); var query = from row in dt.AsEnumerable() where row.Field (Age) == 33 select row;

sandeep ram · Answer

Ok ..thanks a lot I am making progress now. I have the reqd data in a subtable. Just wondering how you can use LINQ in the select method ?

Kirtan Patel · Answer

Friend  Here is Logic you wanted to know (Code is Showing Just logic ) private void button1_Click( object sender EventArgs e) { DataTable dt = new DataTable (); // Assume thaat you have data From Excel to DataTable DataRow [] drCollection = dt.Select( Enter your Query Here ); //Do whatever you want to Do With Filtered Rows foreach ( DataRow r in drCollection) { string value1 = r[0].ToString(); string value2 = r[1].ToString(); // Code For inserting those Values into databse } }

jinge · Answer

Hello I think you can first read the data to a datatable then you can fitler it or its default view. The datatable and its view both have fitler method. In addition you can also cast the datatable to AsQuerable and then use linq to fitler the result set. At last you can push the final filtered result to the database.

sandeep ram · Answer

Hi Kirtan  Thanks for the reply. I know how to access Excel files to load data into the database. What I am asking is  how to develop a logic that can extract only the associated topics for a particular course. Ex - I need to develop an algorithm to get only those topic that belong to the Course 1 ...etc

Kirtan Patel · Answer

Friend Here are many techniques shown by Microsoft to achieve the result you want http://support.microsoft.com/kb/321686 All the Best :)