I am looking to try get a very long string of data into xlsx document. Up till recently
id been downloading battery/inverter data manually for a load of worksites for my
employer. last couple of weeks id managed to get node red to get our site reports
using the manufacturers API. issue is it comes in this long string which id managed
to stream into a csv .
But I am wanting to take the data from that and put it into our formatted xlsx document.
The data all goes into sheet 3 and the calculations and formatting is all done on sheet 2 and 1.
I am not sure whats better but my gut tells me there must be a way to go from the data source
of the get node and format it prior then go into a file node? i have had to filter the data to get rid of
(" as it made things pretty messy all over . I have tried using the csv node in multiple ways
and been browsing the examples i had found online. their was one that showed promis but
turns out that its a formated array from the example. which then puts the data exactly like i see
on the debug. not getting data in and then formatting it and putting it into its respective column
as each string line is seperated and each column is seperated by a comma.
There has got to be an easier way of going about this.....
The msg format required to build the output xlsx file seems pretty straight-forward... if you want to post some example data showing your "long strings" or existing csv data, we can give you some more specific ideas about how to wrestle them into the necessary payload.
Alternatively if you have any excel knowledge have a look at the new power query in there - it is pretty amazing, completely visual and vry easy to use.
If you have the URL for the Battery units you may even be able to suck that straight into an excel data stream i.e. bypass node red
I have just done some stuff for my wifes bookkeeping business and it was extremely easy
Sorry for the long delay. Id been out the whole of today and recently just got family settled for the night.
Anyhow, this very long string comes out from the http get node . passes through a change node which
removes all ( "; ) from the string which seems to clean up a bit of the output then goes into a file node
into a csv file , Here is an attached txt file output . new.txt (136.5 KB)
I have tried a few news but believe I may be using them incorrectly or somehow not giving them
parameters?
Here are some pictures of the file I am trying to get the data into.
.
I am going to try to work with the excelsheets node some more and see what i can figure out.
I can post my current flow. but its very messy whilst trying to understand how i can get this data
into the right place.
I have some excel knowledge and now how to hyperlink to different data stores.
but not worked with using HTTP get request within excel . Also not too sure how
Id authenticate then get token then have to use that token to fetch data?
thats still really crazy and i find it really cool .didnt know it had that level of flexibility.
It is a pretty basic excel functionality available since excel 97, may be even 95... Excel even does this type of task very well
set your flow to generate a file in csv format, and chose a save location that Excel will always be able to access.
use the get external data wizard on the ribbon that will appear if you click the data menu in the current excel version, to import the data into the data area in the worksheet used as a data source for your workbook:
. You can specify a cell where the imported data should start in the process, skip headers, change separators etc etc so that the data fits the required format.
save the workbook and close
The next time you open the workbook, if a new text file has been generated by your nodered flow, all you need to do is navigate to the data worksheet and chose the refresh button on the data ribbon, il will automatically load the new file and replace/update the data in the worksheet. You can play with the query settings in the refresh properties to chose the best behaviour.
You can also import data manually using the wizard each time of course...
Check with your provider if they provide an Odata feed (a lot of them do now particularly in Europe) the authentication etc is built in a part of setting all of that up - you essentially get given a user ID you embed in the worksheet.
If not you can just do a web query
Finally as @mrb32 has said you can just pull it straight out of a CSV - but that is less flexible as you are somewhat restricted to column placement etc.
But anyway - check the Power Query stuff in Excel if you havea recent version as it is extremely powerful and will only take a couple of hours to get on top of (and there are lots of Youtube videos on it
That is one idea I could put to use. certainly brings down the steps I
would normally have to do anycase. I have done something similar I
think but done within the cell's grabbing data from other excel sheets
cells .This is really pretty neat I reckon. I think ill optimize it so that
I have separate files for each site location then can just have each one
update with the new data. Can I schedule it by chance? I am now looking
at the options in excel
Yeah I think so. It may not be what I was conceptually thinking of but this to
already cuts down a bunch of monotonous work.
Not sure about scheduling, you might need to write a macro in excel for that. One thing you can do is set the data refresh to happen automatically every time the file is opened (under refresh, properties).
Nope sadly not hey, they say they provide mqtt,modbus and api access .
The prior 2 of mqtt and modbus arent actually available either as id checked
but found that on our sites we dont have the particular controller that enables
that sort of functionality.
The web query is how the data is coming through if my understanding of that
is right. Getting the data to my laptop was really difficult but I have the method
now sorted with HTTP requests or posts.
I will also give a check out on power query seeing what it can do
as it made things pretty messy all over . I have tried using the csv node in multiple ways
