Saving a JSON array in an excel file

General
1

Currently I'm doing a project where a sensor captures the distance in relation to time. My current problem lies in saving this data to an excel table, I am currently using this node (https://flows.nodered.org/node/node-red-contrib-excel), and this is giving me the result of the image sent,tabela

It's just a value for each column, would i like to know how I can solve this problem? would require that the distance be in one column and the time in another respectively.

codigo

flow

2

If you mean you want something like?

     distancia    temp
     10               28
     20               38
     30               48

Then I think you need to store the data together and then send it to the excel node.

Another way of achieving an excel compatible file would be to write it as csv ( comma separated file)
where you can create format each line and then add it to an existing file using the FILE-out node

3

gonna try this, when i did i will answer you! thanks

4

The csv node will convert well formed JSON into CSV which you can then easily save to a spreadsheet if needed.

5

i tried your method but still can't get past on a single row

6

I have two solutions which on are you trying?

  1. store the data and send once
  2. Use the csv node

if it is 2 check that your file out node is configured to add/append data

7

i tried the second it doesn't create the accurate format .. as for the first one i don't really know how to build a flow that send the file all at once

8

What is inaccurate with the format?

9

Hi, I am new to node-red, can you give an example how to store the data together and send it to excel node, so that i can insert multiple rows into excel

10

@Ramya -
Where are you getting the data from?
What format is the date in?
What have you tried so far?
Show us your flow (read How to share code or flow json)

1 Like
11

I am hard-coding the json data in my function node,I can able to store the array of multiple json into excel , assume i have [{"key1":"a","key2":"b"},{"key3":"c","key4" : "d"}] but now can you help me how to strore the key1 and key2 into sheet1 of excel and key3 and key4 into sheet2 of excel.
now i am storing key1, key2, key3, key4 all in one sheet of excel, which i dont want to do

12

i doubt the excel node can do that as it isn't mentioned in the readme

13

You will need to rearrange the JSON somewhat.

Firstly, if you are using CSV, note that CSV files can only ever represent a single sheet. They have no concept of multiple sheets, you need multiple CSV files. You can use Excel to combine those files, especially if using newer versions of Excel. If your version of Excel has PowerQuery included then automatically combining a folder of CSV files into a single workbook is pretty easy. Even if not, it is relatively easy, especially with a bit of VBA for automation.

You could also do the automation using PowerShell if you are on a platform with support (it is available on Linux not just Windows these days). Because PowerShell supports .net natively, you can use the Excel libraries to combine data.

14

Thnaks a lot

15

2 posts were split to a new topic: Process spreadsheet data per-row?