How to read structured Excel Data into database ?

sandeep ram

17y4.2k.NET

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 ?

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

sandeep ram17y ago
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 categories,42 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 ?
0
jinge17y ago
You are welcome, it is a pity that this forum only allow one post as accepted.

0
sandeep ram17y ago
Ok I have got it now. I will develop this further to suit my needs. Thanks to both Jinge and Kirtan :)
0
jinge17y ago
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.
0
Kirtan Patel17y ago
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
0

jinge17y ago

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 ram17y ago
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 ?
0
Kirtan Patel17y ago
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
           }

        }
0
jinge17y ago
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.
0
sandeep ram17y ago
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
0
Kirtan Patel17y ago
Friend,

Here are many techniques shown by Microsoft to achieve the result you want

http://support.microsoft.com/kb/321686

All the Best :)
0