0

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?

Luke101
  • 63,072
  • 85
  • 231
  • 359

1 Answers1

1

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

Alfie Goodacre
  • 2,753
  • 1
  • 13
  • 26
  • Hi thanks for your answer. Is there a way to do this without saving the file to disk? Perhaps, query excel that is loaded in memory. – Luke101 May 13 '16 at 14:02
  • @Luke101 care to expand, maybe give an example? :) – Alfie Goodacre May 13 '16 at 14:04
  • In the connection it has "myDocument.xlsx". This means I have to save the file to disk first. I have a byte array of the excel file. Is there a way to query the byte array instead? Without saving the excel file to disk. – Luke101 May 13 '16 at 14:21
  • @Luke101 Sorry, I'm not sure how to do that in all honesty, this link http://stackoverflow.com/questions/1490526/how-to-query-excel-data-which-is-in-memory-using-oledb has someone saying you can't and another saying that software they made can do it, but that's all I can help with there – Alfie Goodacre May 13 '16 at 14:27
  • I always thought Extended Properties had to be `Excel 12.0 Xml` for the openXML format? – SierraOscar May 13 '16 at 14:29