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);
}
Dennis is an independent consultant and software architect who loves building distributed systems and the challenges they bring. What he learns he tries to share via numerous articles, blogposts, presentations and workshops.
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.
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
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?
@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?
Excellent, been looking for an example of this!
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
Thanks Andy, that’s awesome!
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);
@Paul : That’s a cool project! Thanks for the info!
where is the parameter “ExcelInfo” coming from?
Anyone have references for this code?
@slabo: It’s just a name, could be anything, but it specifies what name the table should have in the DataSet. That’s all.
Hi,
what should I change to get data from an *.xlsx-file?
Try this:
http://exceldatareader.codeplex.com/
It works.. awesome post..
Thanks 🙂
how can i change “RelationNumber” with the column number along with the row number like [H3]