How to rearrange CSV / JSON keys columns? (Javascript)

Question

I am converting a JSON object array to CSV using Papa Parse JavaScript Library. Is there a way to have the CSV columns arranged in a certain way.

For e.g; I get the column as:

OrderStatus, canOp, OpDesc, ID, OrderNumber, FinishTime, UOM, StartTime

but would like to be arranged as:

ID, OrderNumber, OrderStatus, StartTime, FinishTime, canOp, OpDesc, UOM

The reason why I get the CSV as unarranged is because the JSON data looks like this:

json = [
{
    OrderStatus: "Good",
    canOp:"True",
    OpDesc:"Good to go",
    ID:"100",
    OrderNumber:"1000101",
    FinishTime:"20:50",
    UOM:"K",
    StartTime:"18:10"
},
...
]

Thanks

Deserialize the JSON into an array of objects, then loop through the objects, pulling the values out in the order you desire. — Tim, Jun 23 '15 at 03:07
do what tim as suggested above. it should work in the way you want it to be. =) — Mox, Jun 23 '15 at 03:12
Yeah well, thought there maybe a library that was doing this already in a much more efficient way then spitting out my own master piece. — Shahzad Ali, Jun 23 '15 at 03:27

score 7 · Accepted Answer · edited Jan 29 '19 at 23:47

Papa Parse allows to specify order of fields in the unparse() function:

var csv = Papa.unparse({
  fields: ["ID", "OrderNumber", "OrderStatus", "StartTime", "FinishTime", "canOp", "OpDesc", "UOM"],
  data: [{
      OrderStatus: "Good",
      canOp: "True",
      OpDesc: "Good to go",
      ID: "100",
      OrderNumber: "1000101",
      FinishTime: "20:50",
      UOM: "K",
      StartTime: "18:10"
    },
    // ...
  ]
});

console.log(csv);

<script src="https://unpkg.com/papaparse@4.6.3/papaparse.min.js"></script>
<h3>See your dev console for the results</h3>

Christian Landgren · Answer 2 · 2020-12-19T20:34:43.257

0

You don't need any framework to convert from json to csv.

// fields in the order you want them
const fields = ['ID', 'OrderNumber', 'OrderStatus', 'StartTime', 'FinishTime', 'canOp', 'OpDesc', 'UOM'];
const mapper = (key, value) => value === null ? '' : value // how to handle null values
const csv = [
  fields.join(','), // header row
  ...json.map((row) => fields.map((fieldName) => JSON.stringify(row[fieldName], mapper))).join(',')
]
console.log(csv.join('\r\n'))

Outputs:

ID,OrderNumber,OrderStatus,StartTime,FinishTime,canOp,OpDesc,UOM
"100","1000101","Good","18:10","20:50","True","Good to go","K"
"100","1000101","Good","18:10","20:50","True","Good to go","K"

edited Dec 19 '20 at 20:34

answered Jul 21 '15 at 10:18

Christian Landgren

13,127
6
35
31

1

You are not handling a lot of edge cases, that CSV should support, like different line endings, null/undefined values for fields, handling quotes and whitespaces, handling float values with different locale for decimal separator and much more. – PatrykMilewski Nov 09 '20 at 10:54
Thanks. Easiest way of handling most cases you mention is to encode the output with the JSON.stringify and also use a replacer for handling null values: return JSON.stringify(row[fieldName], (val) => val === null ? 'NULL' : val). I'm updating the example. – Christian Landgren Dec 19 '20 at 20:20

How to rearrange CSV / JSON keys columns? (Javascript)

2 Answers2