# Formatting data to excel

**URL:** <https://discourse.nodered.org/t/formatting-data-to-excel/47533>\
**Category:** General\
**Created:** [23 June 2021 14:05 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533 "2021-06-23T14:05:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Duncan](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@Duncan](https://discourse.nodered.org/u/Duncan)\
**Post date:** [23 June 2021 14:05 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/1 "2021-06-23T14:05:18Z")

</div>

![Knipsel](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/1/21cde458fa896fbd17ba8370b781709dd5b6f88b.jpeg)  
good afternoon all, I need a little help with formatting the data in the attached pic to a readble EXCEL format. thanks

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [23 June 2021 14:25 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/2 "2021-06-23T14:25:59Z")

</div>

You should be able to pass it through a csv node to convert to a comma seperated string, if you save that as a .csv file excell should be able to open it.

---

<div class="post-metadata">

**Author:** ![Duncan](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@Duncan](https://discourse.nodered.org/u/Duncan)\
**Post date:** [28 June 2021 11:34 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/3 "2021-06-28T11:34:14Z")

</div>

Hi E1cid,  
I took your advice and went down the CSV path and I still can't seem to get the data formated how I would like it.

 ![node-red](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/1/5153bc82a9ee2a4a67faa90f5e07edbde9c09f75.jpeg)

This is what goes into the File node:

28-6-2021 13:24:06[node: e0b5b982.8cd6c8](http://10.31.30.73:1880/)msg : Object

_object_

\_msgid: "29c89576.097ada"

year: 2021

query: "SELECT last("value")-first("value") FROM "Gas\_Total" WHERE time \>= '2021-03-01 00:00:00' and time \< '2021-03-30 00:00:00'"

gas: _array[12]_

payload: _array[12]_

[0 … 9]

0: _object_

april: 137435

1: _object_

feb: 144229

2: _object_

may: 107494

3: _object_

june: 104681

4: _object_

mart: -94272

5: _object_

feb: 144229

6: _object_

june: 104686

7: _object_

may: 107494

8: _object_

april: 137435

9: _object_

[10 … 11]

10: _object_

may: 107494

11: _object_

mart: -94272

and this is what gets generated in the CSV file:  
I want the data something like this:  
june : 104686  
april : 137435

[{"june":104686},{"april":137435},{"feb":144229},{"mart":-94272},{"june":104690},{"may":107494},{"april":137435},{"mart":-94272},{"feb":144229},{"april":137435},{"may":107494},{"june":104690}]

any advice? Thanks.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 June 2021 11:56 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/4 "2021-06-28T11:56:56Z")

</div>

Now you have supplied some data, it looks like you need to transform the data first so the months are values not properties.  
here is an example

```auto
[{"id":"75ab1d00.e97ed4","type":"inject","z":"a80913f6.edcdb8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"june\":104686},{\"april\":137435},{\"feb\":144229},{\"mart\":-94272},{\"june\":104690},{\"may\":107494},{\"april\":137435},{\"mart\":-94272},{\"feb\":144229},{\"april\":137435},{\"may\":107494},{\"june\":104690}]","payloadType":"json","x":210,"y":380,"wires":[["77239356.ece26c"]]},{"id":"77239356.ece26c","type":"change","z":"a80913f6.edcdb8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.{\"col1\":$keys($),\"col2\":$.*}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":360,"wires":[["2aa3d05.7a8a63"]]},{"id":"2aa3d05.7a8a63","type":"csv","z":"a80913f6.edcdb8","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":570,"y":360,"wires":[["734d800c.adeb58"]]},{"id":"734d800c.adeb58","type":"debug","z":"a80913f6.edcdb8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":420,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![Duncan](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@Duncan](https://discourse.nodered.org/u/Duncan)\
**Post date:** [28 June 2021 12:11 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/5 "2021-06-28T12:11:01Z")

</div>

Thanks!! that worked, great stuff.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [27 August 2021 12:11 UTC](https://discourse.nodered.org/t/formatting-data-to-excel/47533/6 "2021-08-27T12:11:12Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.