How to query excel document using sql

Question

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?

Use a csv instead of the excel file. Mysql and sqlserver both have ways to use a csv file and read the data. — Nived
– Nived, Commented May 13, 2016 at 13:51
Could you create a dataTable from the byte array and query that? — SierraOscar
– SierraOscar, Commented May 13, 2016 at 14:31

Alfie Goodacre · Accepted Answer · 2016-05-13 13:54:14Z

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

answered May 13, 2016 at 13:54

Alfie Goodacre

2,7931 gold badge16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Luke101 Over a year ago

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.

Alfie Goodacre Over a year ago

@Luke101 care to expand, maybe give an example? :)

Luke101 Over a year ago

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.

Alfie Goodacre Over a year ago

@Luke101 Sorry, I'm not sure how to do that in all honesty, this link stackoverflow.com/questions/1490526/… 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

SierraOscar Over a year ago

I always thought Extended Properties had to be Excel 12.0 Xml for the openXML format?

Collectives™ on Stack Overflow

How to query excel document using sql

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related