As growth hacker for and previous as CTO for , I often face tasks in the form of “take this Excel file with user emails and retrieve column X, Y & Z from the database” (like name or total order value). Tessin.se Offerta.se I’ve been looking for a practical solution where this can be done in an efficient way without having to write custom code each time. After a few iterations, this is what I came up with: I’m a big fan of the .Net scratchpad . This application makes is super simple to query a database using C# and linq. And since I prefer C# over VBA this is exactly the tool I want to use to solve the problem. LINQPad A first attempt was to write a script in LINQpad that opens a XLS file and reads the input value (email) from one column and then outputs the results in other columns. This is not very flexible though. The holy grail for me was to have a way to create ad-hoc formulas inExcel that take an input such as an email and return a specified value from the database. To do this I created a small self hosted JSON REST service using the in a LINQPad script: Nancy Web Framework When running this script a small web service is started locally on your computer: Next step is to connect this REST API with Excel. To do this I’m using a tool that I wrote called . This is an Excel add-on (not free but with a 14 day trial) that has a bunch of features useful for growth hacking. For this solution, I’m using the function to fetch the JSON response from the REST service we wrote and with the query language get a piece of the structure: SeoTools for Excel JsonPathOnUrl JSONPath =JsonPathOnUrl(" "&A2;"firstName") http://localhost:8090/user-by-email/ Now we can easily get all the values we need. SeoTools takes care of caching so that the same request isn’t executed more than once. The Nancy REST service can easily be modified for whatever information you need to retrieve from the database. This takes a bit of setup, but once you have it up and running it works like a charm. If you don’t want to use SeoTools it’s not that difficult to find a VBA solution. I also used this technique if I have a SOAP service (there’s a SOAP driver for LINQPad 4) that I want to query using Excel. I’m not a VBA fan at all so if I have anything that needs scripting (string manipulations etc.) then it’s a lot easier for me to write this code in C# in LINQPad. This is definitely on my top-10 list of productivity hacks that saves me time every day! Please let me know what you think or how you would have solved this. LINQPad boilerplate code You also need to add the Nuget package to your LINQPad script and include it’s namespaces. Nancy.Hosting.Self

BUNCH

Fetch

Super

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

My holy grail: Connect Excel with LINQPad

Too Long; Didn't Read

Companies Mentioned

Niels Bosma

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

My holy grail: Connect Excel with LINQPad

Too Long; Didn't Read

Companies Mentioned

Niels Bosma

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES