CODEWITHSUNDEEP
c#sqlexcel
Luke101
Luke101

Reputation: 65238

How to query excel document using sql

I would like to query an excel document using sql. I want to use c# and ado to perform the query. I don't want to install office on the server. Is there a way to do this?

Upvotes: 0

Views: 65

Answers (1)

Alfie Goodacre
Alfie Goodacre

Reputation: 2793

You could use an OleDB connection to access your Excel spreadsheet, here is an example using DataTables

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 12.0;", "myDocument.xlsx");

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM MyTable", connString);

DataSet ds = new DataSet();
adapter.Fill(ds, "TheData");
DataTable theTable = ds.Tables["TheData"];

Once you have done this you can access values like

theTable.Rows[indexOfRow].ItemArray[indexOfItem] //The items are stored as objects

This specific example is for .xlsx files

Upvotes: 1

Related Questions