Getting data from Excel the fast way, using LINQ

David, Alex and me just needed to get a load of data from some Excel sheets and work with the data. What’s better than to load the data into a DataSet using OleDB and process it using LINQ to DataSets?

Make sure you know what the format of your columns is and that the first row in your sheet holds the name of the column. Then below would be what you need for code.

string filename = @”C:myfile.xls”;

 

string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” +

                “Data Source=” + filename + “;” +

                “Extended Properties=Excel 8.0;”;

 

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, strConn);

DataSet myDataSet = new DataSet();

 

dataAdapter.Fill(myDataSet, “ExcelInfo”);

 

DataTable dataTable = myDataSet.Tables[“ExcelInfo”];

 

var query = from r in dataTable.AsEnumerable()

            select new

            {

                RelationNr = r.Field<double>(“RelationNumber”),

                ClientName = r.Field<string>(“ClientName”),

            };

 

foreach (var item in query)

{

    Console.WriteLine(item.ClientName);             

}

You may also like...

15 Responses

  1. Mischa Kroon says:

    Als de data al in de DataTable zit dan zie ik eigenlijk niet echt de meerwaarde van LINQ niet.

    Maar dat kan aan mij liggen.

    foreach DataRow dbrow in dataTable.Rows
    Console.WriteLine (dbrow(“ClientName”));

    of iets dergelijks.

  2. Great example, but I think you have a small error in this line:

    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, strConn);

    I think “strConn” should be “connectionString”.

    Thanks again,

    pt

  3. Niels Olsen says:

    How to get data the other way around? I mean from SQL Server using Linq to Excel. I have tried using a web page to publish the result of the Linq query, and then import using Excel’s import from web page. It works but the performance is poor. Is there a way to “convert” the Linq query to a ODBC connection to avoid all the ASCII conversions?

  4. Dennis van der Stelt says:

    @Niels : Could you be a little more specific, because I don’t really understand what you’re doing.

    If you want to exchange OLEDB for ODBC, I wouldn’t recommend that, because normally (I said : normally 🙂 OLEDB is always faster.

    But I don’t understand from where to where you’re importing and exporting stuff. Are you getting info from Excel into a dataset or vice-versa?

  5. Chris Lomax says:

    Excellent, been looking for an example of this!

  6. Andy S. says:

    This example was very helpful. Here is my VB.NET version:

    ‘ Imports System.Data.OleDb
    Dim filename As String = “C:myfile.xls”

    Dim connectionString As String = String.Format(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;”, filename)

    Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connectionString)

    Dim myDataSet As DataSet = New DataSet()

    dataAdapter.Fill(myDataSet, “ExcelInfo”)

    Dim dataTable As DataTable = myDataSet.Tables(“ExcelInfo”)

    Dim query = From r In dataTable.AsEnumerable() _
    Select New With _
    { _
    .RelationNr = r.Field(Of Double)(“RelationNumber”), _
    .ClientName = r.Field(Of String)(“ClientName”) _
    }

    For Each item In query
    Console.WriteLine(item.ClientName)
    Next

  7. Dennis van der Stelt says:

    Thanks Andy, that’s awesome!

  8. Paul says:

    You can also use the Linq to Excel open source project (http://code.google.com/p/linqtoexcel/) to easily get data from Excel. It takes care of the OleDB connection for you and you can use the where clause. All you have to do is create a class with properties corresponding to the excel column names. Here’s an example:

    IExcelRepository repo = new ExcelRepository(@”C:myfile.xls”);
    var largeClients = from c in repo.Worksheet
    where c.Employees > 200
    select c;
    foreach (Client client in largeClients)
    Console.WriteLine(client.ClientName);

  9. Dennis van der Stelt says:

    @Paul : That’s a cool project! Thanks for the info!

  10. slabo says:

    where is the parameter “ExcelInfo” coming from?
    Anyone have references for this code?

  11. Dennis van der Stelt says:

    @slabo: It’s just a name, could be anything, but it specifies what name the table should have in the DataSet. That’s all.

  12. otti says:

    Hi,
    what should I change to get data from an *.xlsx-file?

  13. Vijay says:

    It works.. awesome post..

    Thanks 🙂

  14. jmcoke says:

    how can i change “RelationNumber” with the column number along with the row number like [H3]

Click on a tab to select how you'd like to leave your comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.