Virtual schema for Excel

Padawan

Hi.

I have a quick question I'm hoping that somweone could answer.

I was wondering if it is possible to connect live to an excel workbook through a virtual schema?
If not, is there some workaround to access live data from Excel that can be joined with Exasol tables.

 

Thanks,.

Andrée

2 REPLIES 2

Contributor

Hello Andrée, I also think the best choice in this scenario is to use an R-UDF and access the Excel as a data frame.

Another option that comes to my mind: if you want to access live Excel data (I imagine this is a "repository" that will be updated regularly), you could use Microsoft Power Query, so you can mix Excel and Exasol Data in queries.

Regarding the virtual schema scenario, I understand that a JDBC driver should be available for Excel. I do not know if the "Generic" dialect that is supported in Virtual Schemas should be of some help.

Hopefully, if you solve this use case, share the solution!

Best regards and success!

Hugo.

 

Xpert

Hi Andrée,

an idea just off the top of my head: if your excel resides on an (s)ftp you might use an R-UDF with the gdata package, go for read.xls("http://..... ") to get everything into a dataframe and ctx.emit that stuff back to Exasol where you
can join it with tables you already have ?

Haven´t tested this yet, hope I get around to it.

Regarding direct virtual schema integration....not sure on that one, maybe there is a way to put an adapter script on top of some unixODBC but I´m not aware of any MS-Ace-Driver that would work on unix/exa....

Cheers,
Malte