Home

Daily changing file to export data into excel

Mark Le Lay
Occasional Visitor

I would like to import data every day from a device connected to my home network, i have access through port forwarding to the device which provides the data stored in the following format:

 

http://NN.NNN.NN.NN:8080/logfiles/pelletronic/touch_20180315.csv

 

you can see that the file is saved every day by the device and has the date built into the file name.

 

I would like to import this data into excel and create some graphs of the data.

 

Secondly i would like to go in the following day and view the next days worth of data which will be stored and either import this into the same excel and underlying graphs, or add the data to existing.

 

Is this possible please?

 

 

1 Reply

Hi Mark,

 

As an idea with using Power Query- you may generate calendar table with only one column with dates for which you'd like to pick up your data (could be done by power query with any start date and today as end date). To this table add one more column with function which returns table with data from your file for each row.

Parameter for the function is the date in the row, it generates URL based on it and return data. Basic formula will be Excel.Workbook(Web.Content("URL"), null, true).

 

After that expand this column, make additional data transformation if needed and return result to the Excel table, generate the chart based on it.

 

Refresh the query on file opening or manually.