1

Is there any way to implement a custom data source that works with the "Import External Data" feature?

Let's say I have data in a file format that Excel doesn't understand. I'd like to implement an add-in (or whatever) for Excel that allows it to understand this format through the "External Data" feature. Obviously, I could just convert the file to something Excel already understands but, for the sake of user friendliness, I'd rather not have this additional step.

Improve this question

3 Answers 3

Reset to default
4

I ended up creating a service that runs an HTTP server on localhost that serves up tab-delimited data. It was dead simple to develop and fits my needs perfectly. It integrates with the External Data feature with no need to develop an ODBC driver.

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

3 Comments

We are also facing same situation. Do you have any sample to see this in action. I am particularly interested in 1) What is the return type used in http response? Is it simple tab delimited string? 2) What data source in excel you used to connect to this service? 3) whether this works good when the user refresh data using RefreshAll button in Excel data tab?
I don't have a sample but I can answer your questions. 1) The HTTP response is content-type text/plain with a simple tab-delimited string (\r\n to end lines). The first line contains the column headings. It is exactly the same text format as if you exported as tab-delimited. 2) On the Data tab in Excel, click "From Web" and enter your URL. In my case, I just point to localhost:9000 which is where I have the web server running. 3) The Refresh All button works great with this technique.
Thanks very much @dave76...Our actual scenario is to fetch data from SSAS cube into excel which was earlier carried out by direct connection. As the security concerns raised due to connection string in excel we thought of exposing SSAS as our own web service. But later we could see there is inbuilt data pump feature in SSAS and we decided to use it.
2

An add-in (or whatever) here would be an ODBC driver (or an OLEDB driver). I think you don't want to write one.

That's the case if you want to use Import External Data, but you can, of course, create an add-in which would parse and load the file on it's own. It will have it's own menu item then and will not be related with Import External Data.

Improve this answer

1 Comment

Agreed +1. This is the way I would do it. Load the file yourself and then populate the sheet with automation.
0

You could try XLLoop. This lets you implement excel functions (UDFs) on an external server.

So your spreadsheet could contain a function called LoadWidgetData(widgettype, date), which would call your server.

There are server implementations in many languages (Java, Ruby, Python, PHP).

BTW, I work on the project so let me know if you have any questions.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.