OleDbDataReader Excel data to C#

Question

I would like to read data from an Excel table. At the moment, I select all of the data and then I select the part that I need. I'm wondering if this is a good approach, given that there will be about 1000 part numbers in the data file. If there is a better/more efficient way, what would that be? I tried directly selecting the correct column from the Excel table, but I got either the wrong output or the error System.IndexOutOfRangeException. At the bottom, there is the code that I would like to have in case my current method is not good.

Excel table:

Code:

using (OleDbConnection connection = new OleDbConnection(strConn))
{
    connection.Open();
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);

    string partnumber = "Part1"; // this value will change depending on the operator's choice.

    using (OleDbDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            Debug.WriteLine(rdr[partnumber].ToString());
        }
    }           

}

Output:

1 2 3 4 5 6 7 8 9 10

I would like to have something like: (this doesn't work)

using (OleDbConnection connection = new OleDbConnection(strConn))
{
    connection.Open();
    OleDbCommand cmd = new OleDbCommand("SELECT [@partNumber] FROM [Sheet1$]", connection);

    string partnumber = "Part1";

    cmd.Parameters.AddWithValue("@partNumber", partnumber);

    using (OleDbDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            Debug.WriteLine(rdr[xxx].ToString());
        }
    }           

}

[This post](https://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/5721521#5721521) might help you. — vasek, Aug 08 '17 at 07:47
Do you mean something like `"SELECT * FROM [Sheet1$A1:C4]"`? — Fruchtzwerg, Aug 08 '17 at 07:51
@vasek: in that post, they also select the entire data table and then search for it in the C# code, right? My current method is working, I just don't know if it is a good way to do it. — 10a, Aug 08 '17 at 08:27
@Fruchtzwerg: I want to directly select a specific column from Excel so that not all the data needs to be loaded into C#. I edited my code post. — 10a, Aug 08 '17 at 08:27
@Tina So your input is `Part1` and you need a query that finds corresponding `Part2` value but not loading whole sheet into `DataTable`, is that right? — vasek, Aug 08 '17 at 08:32
@vasek: no, my input is the part name (so Part1 or Part2 in my example) and I need to get as an output the numbers that are below that name. If Part1 is the input, then I need to get 1 to 10 as an output; if Part2 is the input, then I need 13 to 22 as an output. — 10a, Aug 08 '17 at 08:34

vasek · Accepted Answer · 2017-08-08T09:19:54.803

0

If you have a column name and you need the column data, you can modify your code as follows:

string columnName = "335610";
OleDbCommand cmd = new OleDbCommand($"SELECT [{columnName}] FROM [Sheet1$]", connection);

using (OleDbDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        Debug.WriteLine(rdr.GetValue(0).ToString());
    }
}

edited Aug 08 '17 at 09:19

answered Aug 08 '17 at 08:38

vasek

2,759
1
26
30

When I use this, it gives me 10 times (=length of the data below Part1) the header name. So: Part1 Part1 Part1 ... – 10a Aug 08 '17 at 08:44
@Tina can you please show me your connection string? – vasek Aug 08 '17 at 08:46
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\""; – 10a Aug 08 '17 at 08:49
@Tina that's strange, it works for me with your connection string! – vasek Aug 08 '17 at 08:56
It works now. I know what went wrong but not why it went wrong. When I tried your code, I changed "Part1" to the actual part number (as a string) "335610" and then I got the problem. When I use "Part1" (and also change the header name to Part1 of course) then it works perfectly. Do you know why it doesn't work with a number, even though it's a string? Putting an "R" in front of the number, so "R335610", seems to work as well. – 10a Aug 08 '17 at 09:16
@Tina if your column name is an integer (or anything that is interpreted as constant or keyword by SQL parser) then you need to use brackets. I updated my answer and tested that, it works perfectly. – vasek Aug 08 '17 at 09:20
@Tina glad to help! – vasek Aug 08 '17 at 09:24

OleDbDataReader Excel data to C#

1 Answers1