HTTP --> XLSX --> Dashboard

General
1

Hi everyone,
I need to download and parse information that would then be used for dashboarding reasons.
All is good from dashboarding point of view; it is up qnd running and well set-up.

From an HTTP node, I am able to call the website http://www.xyz.com/data.xlsx and I put as an output a JSON. However it is not returning me a payload which is usable.

What shall I do to parse this xlsx within nodered. I tried already with a "read file" node without success

Many thanks,
Tom

2

Show us what the start of the http request node output looks like as a string.

3

This is the link to the file: https://theshiftproject.org/wp-content/uploads/2018/10/Lean-ICT-Materials-Residential-Router-Electricity-Consumption-2018.xlsx

When I copy the value out of the debug it gives me only: PK

image

but it looks different in the debug

image
image267×219 3.45 KB

and the http request node provides an error message: JSON parse error

Same output PK... when I put the output format to UTF8

4

an xls is a binary file, no string.

Install node-red-contrib-spreadsheet-in

See the example in that page. Note for the 'sheet' node - it needs to match the workbook sheet name.

5

Thanks Bakman2.

image
image714×263 22.1 KB

I tried that, put the output of the HTTP request as binary; and got this error message.

image
image271×178 2.08 KB

6

Try removing the file read node and send the binary data straight to the book node.

7

and set the http request output to buffer (not utf-8)

8

Seems to provide the excel details, thanks guys !
I will have a further look to see if I can extract the relevant data, and keep you posted.
Best, Tom

9

Dear all,
Making progress with the solution you proposed :+1:.
The next challenge is to be able to capture the data for each line of the excel sheet.
I am not that experienced in creating loops, may I ask for your support ?
for each line I need to capture the date in column A then the data in column B, C, D, E, F (value under "v") so that I can insert them via SQL.
Could I do this with the node itself ( I have not found something on the GitHub) or shall I go for a function ?
Many thanks,

Please find below the payload out of the node "book"

{"!ref":"A1:G33","A1":{"t":"s","v":"Date et heure","r":"Date et heure","h":"Date et heure","z":"General","w":"Date et heure","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"B1":{"t":"s","v":"Production totale","r":"Production totale","h":"Production totale","z":"General","w":"Production totale","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"C1":{"t":"s","v":"Consommation totale","r":"Consommation totale","h":"Consommation totale","z":"General","w":"Consommation totale","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"D1":{"t":"s","v":"Auto-consommation","r":"Auto-consommation","h":"Auto-consommation","z":"General","w":"Auto-consommation","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"E1":{"t":"s","v":"Énergie injectée dans le réseau","r":"Énergie injectée dans le réseau","h":"Énergie injectée dans le réseau","z":"General","w":"Énergie injectée dans le réseau","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"F1":{"t":"s","v":"Énergie provenant du réseau","r":"Énergie provenant du réseau","h":"Énergie provenant du réseau","z":"General","w":"Énergie provenant du réseau","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"A2":{"t":"s","v":"[dd.MM.yyyy]","r":"[dd.MM.yyyy]","h":"[dd.MM.yyyy]","z":"General","w":"[dd.MM.yyyy]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"B2":{"t":"s","v":"[Wh]","r":"[Wh]","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"C2":{"t":"s","v":"[Wh]","r":"[Wh]","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"D2":{"t":"s","v":"[Wh]","r":"[Wh]","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"E2":{"t":"s","v":"[Wh]","r":"[Wh]","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"F2":{"t":"s","v":"[Wh]","r":"[Wh]","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"A3":{"t":"s","v":"01.09.2024","r":"01.09.2024","h":"01.09.2024","z":"General","w":"01.09.2024","s":{"patternType":"none"}},"B3":{"t":"n","v":26816.5844,"z":"???0.00???","s":{"patternType":"none"}},"C3":{"t":"n","v":31440.8069,"z":"???0.00???","s":{"patternType":"none"}},"D3":{"t":"n","v":23813.862,"z":"???0.00???","s":{"patternType":"none"}},"E3":{"t":"n","v":3712,"z":"???0.00???","w":"3712.00 ","s":{"patternType":"none"}},"F3":{"t":"n","v":8008,"z":"???0.00???","w":"8008.00 ","s":{"patternType":"none"}},"G3":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A4":{"t":"s","v":"02.09.2024","r":"02.09.2024","h":"02.09.2024","z":"General","w":"02.09.2024","s":{"patternType":"none"}},"B4":{"t":"n","v":23431.0544,"z":"???0.00???","s":{"patternType":"none"}},"C4":{"t":"n","v":25668.4373,"z":"???0.00???","s":{"patternType":"none"}},"D4":{"t":"n","v":20057.2475,"z":"???0.00???","s":{"patternType":"none"}},"E4":{"t":"n","v":1502,"z":"???0.00???","w":"1502.00 ","s":{"patternType":"none"}},"F4":{"t":"n","v":5993,"z":"???0.00???","w":"5993.00 ","s":{"patternType":"none"}},"G4":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A5":{"t":"s","v":"03.09.2024","r":"

10

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path/value for any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

If you select Copy Value for the payload out of the book node then you can paste it here, in a form that can be copied and pasted into an inject node for testing, and so that we can see exactly what you have.

When you paste it, in order to make code readable and usable it is necessary to surround it with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

See this post for more details - How to share code or flow json

11

Thanks Colin.
I was able to access the data and copy, but I do not know how to do a loop able to summarise the data in the right format.
Do you know ?

This is the output of the debug node, after the node called "Sheet" from node-red-contrib-spreadsheet-in:

{"!ref":"A1:G33","A1":{"t":"s","v":"Date et heure","r":"<t>Date et heure</t>","h":"Date et heure","z":"General","w":"Date et heure","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"B1":{"t":"s","v":"Production totale","r":"<t>Production totale</t>","h":"Production totale","z":"General","w":"Production totale","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"C1":{"t":"s","v":"Consommation totale","r":"<t>Consommation totale</t>","h":"Consommation totale","z":"General","w":"Consommation totale","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"D1":{"t":"s","v":"Auto-consommation","r":"<t>Auto-consommation</t>","h":"Auto-consommation","z":"General","w":"Auto-consommation","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"E1":{"t":"s","v":"Énergie injectée dans le réseau","r":"<t>Énergie injectée dans le réseau</t>","h":"Énergie injectée dans le réseau","z":"General","w":"Énergie injectée dans le réseau","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"F1":{"t":"s","v":"Énergie provenant du réseau","r":"<t>Énergie provenant du réseau</t>","h":"Énergie provenant du réseau","z":"General","w":"Énergie provenant du réseau","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"A2":{"t":"s","v":"[dd.MM.yyyy]","r":"<t>[dd.MM.yyyy]</t>","h":"[dd.MM.yyyy]","z":"General","w":"[dd.MM.yyyy]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"B2":{"t":"s","v":"[Wh]","r":"<t>[Wh]</t>","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"C2":{"t":"s","v":"[Wh]","r":"<t>[Wh]</t>","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"D2":{"t":"s","v":"[Wh]","r":"<t>[Wh]</t>","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"E2":{"t":"s","v":"[Wh]","r":"<t>[Wh]</t>","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"F2":{"t":"s","v":"[Wh]","r":"<t>[Wh]</t>","h":"[Wh]","z":"General","w":"[Wh]","s":{"patternType":"solid","fgColor":{"tint":0,"rgb":"C0C0C0"}}},"A3":{"t":"s","v":"01.09.2024","r":"<t>01.09.2024</t>","h":"01.09.2024","z":"General","w":"01.09.2024","s":{"patternType":"none"}},"B3":{"t":"n","v":26816.5844,"z":"???0.00???","s":{"patternType":"none"}},"C3":{"t":"n","v":31440.8069,"z":"???0.00???","s":{"patternType":"none"}},"D3":{"t":"n","v":23813.862,"z":"???0.00???","s":{"patternType":"none"}},"E3":{"t":"n","v":3712,"z":"???0.00???","w":"3712.00   ","s":{"patternType":"none"}},"F3":{"t":"n","v":8008,"z":"???0.00???","w":"8008.00   ","s":{"patternType":"none"}},"G3":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A4":{"t":"s","v":"02.09.2024","r":"<t>02.09.2024</t>","h":"02.09.2024","z":"General","w":"02.09.2024","s":{"patternType":"none"}},"B4":{"t":"n","v":23431.0544,"z":"???0.00???","s":{"patternType":"none"}},"C4":{"t":"n","v":25668.4373,"z":"???0.00???","s":{"patternType":"none"}},"D4":{"t":"n","v":20057.2475,"z":"???0.00???","s":{"patternType":"none"}},"E4":{"t":"n","v":1502,"z":"???0.00???","w":"1502.00   ","s":{"patternType":"none"}},"F4":{"t":"n","v":5993,"z":"???0.00???","w":"5993.00   ","s":{"patternType":"none"}},"G4":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A5":{"t":"s","v":"03.09.2024","r":"<t>03.09.2024</t>","h":"03.09.2024","z":"General","w":"03.09.2024","s":{"patternType":"none"}},"B5":{"t":"n","v":33074.674,"z":"???0.00???","s":{"patternType":"none"}},"C5":{"t":"n","v":25112.6057,"z":"???0.00???","s":{"patternType":"none"}},"D5":{"t":"n","v":21712.6421,"z":"???0.00???","s":{"patternType":"none"}},"E5":{"t":"n","v":11812,"z":"???0.00???","w":"11812.00   ","s":{"patternType":"none"}},"F5":{"t":"n","v":3781,"z":"???0.00???","w":"3781.00   ","s":{"patternType":"none"}},"G5":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A6":{"t":"s","v":"04.09.2024","r":"<t>04.09.2024</t>","h":"04.09.2024","z":"General","w":"04.09.2024","s":{"patternType":"none"}},"B6":{"t":"n","v":16699.0877,"z":"???0.00???","s":{"patternType":"none"}},"C6":{"t":"n","v":23414.4313,"z":"???0.00???","s":{"patternType":"none"}},"D6":{"t":"n","v":16893.9477,"z":"???0.00???","s":{"patternType":"none"}},"E6":{"t":"n","v":362,"z":"???0.00???","w":" 362.00   ","s":{"patternType":"none"}},"F6":{"t":"n","v":6995,"z":"???0.00???","w":"6995.00   ","s":{"patternType":"none"}},"G6":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A7":{"t":"s","v":"05.09.2024","r":"<t>05.09.2024</t>","h":"05.09.2024","z":"General","w":"05.09.2024","s":{"patternType":"none"}},"B7":{"t":"n","v":16115.3419,"z":"???0.00???","s":{"patternType":"none"}},"C7":{"t":"n","v":19769.3425,"z":"???0.00???","s":{"patternType":"none"}},"D7":{"t":"n","v":14627.8106,"z":"???0.00???","s":{"patternType":"none"}},"E7":{"t":"n","v":1217,"z":"???0.00???","w":"1217.00   ","s":{"patternType":"none"}},"F7":{"t":"n","v":5616,"z":"???0.00???","w":"5616.00   ","s":{"patternType":"none"}},"G7":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A8":{"t":"s","v":"06.09.2024","r":"<t>06.09.2024</t>","h":"06.09.2024","z":"General","w":"06.09.2024","s":{"patternType":"none"}},"B8":{"t":"n","v":28643.4683,"z":"???0.00???","s":{"patternType":"none"}},"C8":{"t":"n","v":30603.1648,"z":"???0.00???","s":{"patternType":"none"}},"D8":{"t":"n","v":26085.3921,"z":"???0.00???","s":{"patternType":"none"}},"E8":{"t":"n","v":3030,"z":"???0.00???","w":"3030.00   ","s":{"patternType":"none"}},"F8":{"t":"n","v":4903,"z":"???0.00???","w":"4903.00   ","s":{"patternType":"none"}},"G8":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A9":{"t":"s","v":"07.09.2024","r":"<t>07.09.2024</t>","h":"07.09.2024","z":"General","w":"07.09.2024","s":{"patternType":"none"}},"B9":{"t":"n","v":32259.6214,"z":"???0.00???","s":{"patternType":"none"}},"C9":{"t":"n","v":35658.2928,"z":"???0.00???","s":{"patternType":"none"}},"D9":{"t":"n","v":27900.4419,"z":"???0.00???","s":{"patternType":"none"}},"E9":{"t":"n","v":5377,"z":"???0.00???","w":"5377.00   ","s":{"patternType":"none"}},"F9":{"t":"n","v":8170,"z":"???0.00???","w":"8170.00   ","s":{"patternType":"none"}},"G9":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A10":{"t":"s","v":"08.09.2024","r":"<t>08.09.2024</t>","h":"08.09.2024","z":"General","w":"08.09.2024","s":{"patternType":"none"}},"B10":{"t":"n","v":13456.1764,"z":"???0.00???","s":{"patternType":"none"}},"C10":{"t":"n","v":26929.0981,"z":"???0.00???","s":{"patternType":"none"}},"D10":{"t":"n","v":13812.6386,"z":"???0.00???","s":{"patternType":"none"}},"E10":{"t":"n","v":313,"z":"???0.00???","w":" 313.00   ","s":{"patternType":"none"}},"F10":{"t":"n","v":13696,"z":"???0.00???","w":"13696.00   ","s":{"patternType":"none"}},"G10":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A11":{"t":"s","v":"09.09.2024","r":"<t>09.09.2024</t>","h":"09.09.2024","z":"General","w":"09.09.2024","s":{"patternType":"none"}},"B11":{"t":"n","v":23866.7495,"z":"???0.00???","s":{"patternType":"none"}},"C11":{"t":"n","v":30794.8372,"z":"???0.00???","s":{"patternType":"none"}},"D11":{"t":"n","v":23819.1201,"z":"???0.00???","s":{"patternType":"none"}},"E11":{"t":"n","v":2305,"z":"???0.00???","w":"2305.00   ","s":{"patternType":"none"}},"F11":{"t":"n","v":9929,"z":"???0.00???","w":"9929.00   ","s":{"patternType":"none"}},"G11":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A12":{"t":"s","v":"10.09.2024","r":"<t>10.09.2024</t>","h":"10.09.2024","z":"General","w":"10.09.2024","s":{"patternType":"none"}},"B12":{"t":"n","v":22610.5998,"z":"???0.00???","s":{"patternType":"none"}},"C12":{"t":"n","v":24589.0661,"z":"???0.00???","s":{"patternType":"none"}},"D12":{"t":"n","v":19139.5859,"z":"???0.00???","s":{"patternType":"none"}},"E12":{"t":"n","v":4215,"z":"???0.00???","w":"4215.00   ","s":{"patternType":"none"}},"F12":{"t":"n","v":8402,"z":"???0.00???","w":"8402.00   ","s":{"patternType":"none"}},"G12":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A13":{"t":"s","v":"11.09.2024","r":"<t>11.09.2024</t>","h":"11.09.2024","z":"General","w":"11.09.2024","s":{"patternType":"none"}},"B13":{"t":"n","v":10747.6592,"z":"???0.00???","s":{"patternType":"none"}},"C13":{"t":"n","v":28373.3808,"z":"???0.00???","s":{"patternType":"none"}},"D13":{"t":"n","v":15515.7745,"z":"???0.00???","s":{"patternType":"none"}},"E13":{"t":"n","v":173,"z":"???0.00???","w":" 173.00   ","s":{"patternType":"none"}},"F13":{"t":"n","v":15812,"z":"???0.00???","w":"15812.00   ","s":{"patternType":"none"}},"G13":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A14":{"t":"s","v":"12.09.2024","r":"<t>12.09.2024</t>","h":"12.09.2024","z":"General","w":"12.09.2024","s":{"patternType":"none"}},"B14":{"t":"n","v":24655.546,"z":"???0.00???","s":{"patternType":"none"}},"C14":{"t":"n","v":27297.2695,"z":"???0.00???","s":{"patternType":"none"}},"D14":{"t":"n","v":20434.4327,"z":"???0.00???","s":{"patternType":"none"}},"E14":{"t":"n","v":3475,"z":"???0.00???","w":"3475.00   ","s":{"patternType":"none"}},"F14":{"t":"n","v":9816,"z":"???0.00???","w":"9816.00   ","s":{"patternType":"none"}},"G14":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A15":{"t":"s","v":"13.09.2024","r":"<t>13.09.2024</t>","h":"13.09.2024","z":"General","w":"13.09.2024","s":{"patternType":"none"}},"B15":{"t":"n","v":22039.1552,"z":"???0.00???","s":{"patternType":"none"}},"C15":{"t":"n","v":30789.804,"z":"???0.00???","s":{"patternType":"none"}},"D15":{"t":"n","v":26745.3911,"z":"???0.00???","s":{"patternType":"none"}},"E15":{"t":"n","v":432,"z":"???0.00???","w":" 432.00   ","s":{"patternType":"none"}},"F15":{"t":"n","v":6997,"z":"???0.00???","w":"6997.00   ","s":{"patternType":"none"}},"G15":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A16":{"t":"s","v":"14.09.2024","r":"<t>14.09.2024</t>","h":"14.09.2024","z":"General","w":"14.09.2024","s":{"patternType":"none"}},"B16":{"t":"n","v":22325.5556,"z":"???0.00???","s":{"patternType":"none"}},"C16":{"t":"n","v":27350.5931,"z":"???0.00???","s":{"patternType":"none"}},"D16":{"t":"n","v":20837.5997,"z":"???0.00???","s":{"patternType":"none"}},"E16":{"t":"n","v":995,"z":"???0.00???","w":" 995.00   ","s":{"patternType":"none"}},"F16":{"t":"n","v":9466,"z":"???0.00???","w":"9466.00   ","s":{"patternType":"none"}},"G16":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A17":{"t":"s","v":"15.09.2024","r":"<t>15.09.2024</t>","h":"15.09.2024","z":"General","w":"15.09.2024","s":{"patternType":"none"}},"B17":{"t":"n","v":31019.5878,"z":"???0.00???","s":{"patternType":"none"}},"C17":{"t":"n","v":31262.5195,"z":"???0.00???","s":{"patternType":"none"}},"D17":{"t":"n","v":27725.7421,"z":"???0.00???","s":{"patternType":"none"}},"E17":{"t":"n","v":4914,"z":"???0.00???","w":"4914.00   ","s":{"patternType":"none"}},"F17":{"t":"n","v":3924,"z":"???0.00???","w":"3924.00   ","s":{"patternType":"none"}},"G17":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A18":{"t":"s","v":"16.09.2024","r":"<t>16.09.2024</t>","h":"16.09.2024","z":"General","w":"16.09.2024","s":{"patternType":"none"}},"B18":{"t":"n","v":9420.4597,"z":"???0.00???","s":{"patternType":"none"}},"C18":{"t":"n","v":25669.7242,"z":"???0.00???","s":{"patternType":"none"}},"D18":{"t":"n","v":11606.7781,"z":"???0.00???","s":{"patternType":"none"}},"E18":{"t":"n","v":159,"z":"???0.00???","w":" 159.00   ","s":{"patternType":"none"}},"F18":{"t":"n","v":14448,"z":"???0.00???","w":"14448.00   ","s":{"patternType":"none"}},"G18":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A19":{"t":"s","v":"17.09.2024","r":"<t>17.09.2024</t>","h":"17.09.2024","z":"General","w":"17.09.2024","s":{"patternType":"none"}},"B19":{"t":"n","v":5878.0339,"z":"???0.00???","s":{"patternType":"none"}},"C19":{"t":"n","v":27906.0828,"z":"???0.00???","s":{"patternType":"none"}},"D19":{"t":"n","v":8428.9483,"z":"???0.00???","s":{"patternType":"none"}},"E19":{"t":"n","v":91,"z":"???0.00???","w":"  91.00   ","s":{"patternType":"none"}},"F19":{"t":"n","v":22430,"z":"???0.00???","w":"22430.00   ","s":{"patternType":"none"}},"G19":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A20":{"t":"s","v":"18.09.2024","r":"<t>18.09.2024</t>","h":"18.09.2024","z":"General","w":"18.09.2024","s":{"patternType":"none"}},"B20":{"t":"n","v":19851.3014,"z":"???0.00???","s":{"patternType":"none"}},"C20":{"t":"n","v":28432.69,"z":"???0.00???","s":{"patternType":"none"}},"D20":{"t":"n","v":20921.9277,"z":"???0.00???","s":{"patternType":"none"}},"E20":{"t":"n","v":1208,"z":"???0.00???","w":"1208.00   ","s":{"patternType":"none"}},"F20":{"t":"n","v":10463,"z":"???0.00???","w":"10463.00   ","s":{"patternType":"none"}},"G20":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A21":{"t":"s","v":"19.09.2024","r":"<t>19.09.2024</t>","h":"19.09.2024","z":"General","w":"19.09.2024","s":{"patternType":"none"}},"B21":{"t":"n","v":29678.5317,"z":"???0.00???","s":{"patternType":"none"}},"C21":{"t":"n","v":25336.7334,"z":"???0.00???","s":{"patternType":"none"}},"D21":{"t":"n","v":19967.4171,"z":"???0.00???","s":{"patternType":"none"}},"E21":{"t":"n","v":10343,"z":"???0.00???","w":"10343.00   ","s":{"patternType":"none"}},"F21":{"t":"n","v":8322,"z":"???0.00???","w":"8322.00   ","s":{"patternType":"none"}},"G21":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A22":{"t":"s","v":"20.09.2024","r":"<t>20.09.2024</t>","h":"20.09.2024","z":"General","w":"20.09.2024","s":{"patternType":"none"}},"B22":{"t":"n","v":27507.4243,"z":"???0.00???","s":{"patternType":"none"}},"C22":{"t":"n","v":30436.1625,"z":"???0.00???","s":{"patternType":"none"}},"D22":{"t":"n","v":25504.3776,"z":"???0.00???","s":{"patternType":"none"}},"E22":{"t":"n","v":3583,"z":"???0.00???","w":"3583.00   ","s":{"patternType":"none"}},"F22":{"t":"n","v":5327,"z":"???0.00???","w":"5327.00   ","s":{"patternType":"none"}},"G22":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A23":{"t":"s","v":"21.09.2024","r":"<t>21.09.2024</t>","h":"21.09.2024","z":"General","w":"21.09.2024","s":{"patternType":"none"}},"B23":{"t":"n","v":29905.9525,"z":"???0.00???","s":{"patternType":"none"}},"C23":{"t":"n","v":26973.5467,"z":"???0.00???","s":{"patternType":"none"}},"D23":{"t":"n","v":21542.9031,"z":"???0.00???","s":{"patternType":"none"}},"E23":{"t":"n","v":5969,"z":"???0.00???","w":"5969.00   ","s":{"patternType":"none"}},"F23":{"t":"n","v":5816,"z":"???0.00???","w":"5816.00   ","s":{"patternType":"none"}},"G23":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A24":{"t":"s","v":"22.09.2024","r":"<t>22.09.2024</t>","h":"22.09.2024","z":"General","w":"22.09.2024","s":{"patternType":"none"}},"B24":{"t":"n","v":20960.3568,"z":"???0.00???","s":{"patternType":"none"}},"C24":{"t":"n","v":28033.8058,"z":"???0.00???","s":{"patternType":"none"}},"D24":{"t":"n","v":24826.708,"z":"???0.00???","s":{"patternType":"none"}},"E24":{"t":"n","v":1325,"z":"???0.00???","w":"1325.00   ","s":{"patternType":"none"}},"F24":{"t":"n","v":6205,"z":"???0.00???","w":"6205.00   ","s":{"patternType":"none"}},"G24":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A25":{"t":"s","v":"23.09.2024","r":"<t>23.09.2024</t>","h":"23.09.2024","z":"General","w":"23.09.2024","s":{"patternType":"none"}},"B25":{"t":"n","v":6953.8936,"z":"???0.00???","s":{"patternType":"none"}},"C25":{"t":"n","v":25143.1419,"z":"???0.00???","s":{"patternType":"none"}},"D25":{"t":"n","v":14272.0997,"z":"???0.00???","s":{"patternType":"none"}},"E25":{"t":"n","v":307,"z":"???0.00???","w":" 307.00   ","s":{"patternType":"none"}},"F25":{"t":"n","v":18203,"z":"???0.00???","w":"18203.00   ","s":{"patternType":"none"}},"G25":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A26":{"t":"s","v":"24.09.2024","r":"<t>24.09.2024</t>","h":"24.09.2024","z":"General","w":"24.09.2024","s":{"patternType":"none"}},"B26":{"t":"n","v":14293.862,"z":"???0.00???","s":{"patternType":"none"}},"C26":{"t":"n","v":28264.5348,"z":"???0.00???","s":{"patternType":"none"}},"D26":{"t":"n","v":19409.0328,"z":"???0.00???","s":{"patternType":"none"}},"E26":{"t":"n","v":358,"z":"???0.00???","w":" 358.00   ","s":{"patternType":"none"}},"F26":{"t":"n","v":16191,"z":"???0.00???","w":"16191.00   ","s":{"patternType":"none"}},"G26":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A27":{"t":"s","v":"25.09.2024","r":"<t>25.09.2024</t>","h":"25.09.2024","z":"General","w":"25.09.2024","s":{"patternType":"none"}},"B27":{"t":"n","v":14175.7111,"z":"???0.00???","s":{"patternType":"none"}},"C27":{"t":"n","v":24358.3213,"z":"???0.00???","s":{"patternType":"none"}},"D27":{"t":"n","v":20017.1935,"z":"???0.00???","s":{"patternType":"none"}},"E27":{"t":"n","v":1752,"z":"???0.00???","w":"1752.00   ","s":{"patternType":"none"}},"F27":{"t":"n","v":11676,"z":"???0.00???","w":"11676.00   ","s":{"patternType":"none"}},"G27":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A28":{"t":"s","v":"26.09.2024","r":"<t>26.09.2024</t>","h":"26.09.2024","z":"General","w":"26.09.2024","s":{"patternType":"none"}},"B28":{"t":"n","v":4839.2931,"z":"???0.00???","s":{"patternType":"none"}},"C28":{"t":"n","v":22554.5964,"z":"???0.00???","s":{"patternType":"none"}},"D28":{"t":"n","v":12568.2784,"z":"???0.00???","s":{"patternType":"none"}},"E28":{"t":"n","v":114,"z":"???0.00???","w":" 114.00   ","s":{"patternType":"none"}},"F28":{"t":"n","v":17320,"z":"???0.00???","w":"17320.00   ","s":{"patternType":"none"}},"G28":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A29":{"t":"s","v":"27.09.2024","r":"<t>27.09.2024</t>","h":"27.09.2024","z":"General","w":"27.09.2024","s":{"patternType":"none"}},"B29":{"t":"n","v":18551.8733,"z":"???0.00???","s":{"patternType":"none"}},"C29":{"t":"n","v":34490.9525,"z":"???0.00???","s":{"patternType":"none"}},"D29":{"t":"n","v":24210.8099,"z":"???0.00???","s":{"patternType":"none"}},"E29":{"t":"n","v":1192,"z":"???0.00???","w":"1192.00   ","s":{"patternType":"none"}},"F29":{"t":"n","v":17615,"z":"???0.00???","w":"17615.00   ","s":{"patternType":"none"}},"G29":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A30":{"t":"s","v":"28.09.2024","r":"<t>28.09.2024</t>","h":"28.09.2024","z":"General","w":"28.09.2024","s":{"patternType":"none"}},"B30":{"t":"n","v":17854.4364,"z":"???0.00???","s":{"patternType":"none"}},"C30":{"t":"n","v":29570.7413,"z":"???0.00???","s":{"patternType":"none"}},"D30":{"t":"n","v":23838.2422,"z":"???0.00???","s":{"patternType":"none"}},"E30":{"t":"n","v":827,"z":"???0.00???","w":" 827.00   ","s":{"patternType":"none"}},"F30":{"t":"n","v":13067,"z":"???0.00???","w":"13067.00   ","s":{"patternType":"none"}},"G30":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A31":{"t":"s","v":"29.09.2024","r":"<t>29.09.2024</t>","h":"29.09.2024","z":"General","w":"29.09.2024","s":{"patternType":"none"}},"B31":{"t":"n","v":21404.9697,"z":"???0.00???","s":{"patternType":"none"}},"C31":{"t":"n","v":37603.6606,"z":"???0.00???","s":{"patternType":"none"}},"D31":{"t":"n","v":27046.1627,"z":"???0.00???","s":{"patternType":"none"}},"E31":{"t":"n","v":1377,"z":"???0.00???","w":"1377.00   ","s":{"patternType":"none"}},"F31":{"t":"n","v":17891,"z":"???0.00???","w":"17891.00   ","s":{"patternType":"none"}},"G31":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A32":{"t":"s","v":"30.09.2024","r":"<t>30.09.2024</t>","h":"30.09.2024","z":"General","w":"30.09.2024","s":{"patternType":"none"}},"B32":{"t":"n","v":10481.461,"z":"???0.00???","s":{"patternType":"none"}},"C32":{"t":"n","v":33582.3744,"z":"???0.00???","s":{"patternType":"none"}},"D32":{"t":"n","v":17196.0096,"z":"???0.00???","s":{"patternType":"none"}},"E32":{"t":"n","v":258,"z":"???0.00???","w":" 258.00   ","s":{"patternType":"none"}},"F32":{"t":"n","v":23721,"z":"???0.00???","w":"23721.00   ","s":{"patternType":"none"}},"G32":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"A33":{"t":"z","z":"General","s":{"patternType":"none"}},"B33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"C33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"D33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"E33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"F33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"G33":{"t":"z","z":"???0.00???","s":{"patternType":"none"}},"!cols":[{"width":13.524704933166504,"customwidth":"1","wpx":176,"wch":13.15,"MDW":13},{"width":17.21987533569336,"customwidth":"1","wpx":224,"wch":16.85,"MDW":13},{"width":21.423458099365234,"customwidth":"1","wpx":279,"wch":21.08,"MDW":13},{"width":20.53892707824707,"customwidth":"1","wpx":267,"wch":20.15,"MDW":13},{"width":29.89479637145996,"customwidth":"1","wpx":389,"wch":29.54,"MDW":13},{"width":27.994218826293945,"customwidth":"1","wpx":364,"wch":27.62,"MDW":13},{"width":9.140625,"customwidth":"1","wpx":119,"wch":8.77,"MDW":13}]}
12

Did you use the sheet to json node ?
if yes, try setting the header to:

image
image1006×172 39.5 KB

now you get an array of objects with the column names as keys, you can run that through a split node.

13

Thanks Bakman2,
The Sheet to Json node require a fix range but in my case it depends on the number of lines which can change from one excel to another, I need a way to loop according to the numbers of line.

14

It is optional.

selectRange string
If the Range property is not configured in the node, this optional property sets an A-1 based range.

15

I had the whole night on it and is now running perfectly, thanks for your help guys :slight_smile: