Scriptable how to process CSV Data?

Scriptable
1

Hi all,
I have a CSV looking like that:

0000,297480,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0
0005,297504,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0
0010,297528,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0
0015,297551,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0
0020,297573,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0
0025,297596,12729,220564,0,0,0,0,122137,22387,0,0,0,0,0,5,0,0,0,0,0,0,0,0

The number of rows is increasing during a day, but the number of columns is always the same.

Is it possible to convert this CSV in Scriptable to a data type I can do further processing with, for example to JSON?

Hope you can help me!

2

One of these JavaScript options should allow you to convert CSV to JSON.

3

This will return an array from CSV:

function parseCSV(str, len) {
  let arr = [];
  let quote = false;
  let col, c;
  for (let row = col = c = 0; c < str.length && row < len; c++) {
    let cc = str[c], nc = str[c+1];
    arr[row] = arr[row] || [];
    arr[row][col] = arr[row][col] || '';
    if (cc == '"' && quote && nc == '"') { arr[row][col] += cc; ++c; continue; }
    if (cc == '"') { quote = !quote; continue; }
    if (cc == ',' && !quote) { ++col; continue; }
    if (cc == '\r' && nc == '\n' && !quote) { ++row; col = 0; ++c; continue; }
    if (cc == '\n' && !quote) { ++row; col = 0; continue; }
    if (cc == '\r' && !quote) { ++row; col = 0; continue; }
    arr[row][col] += cc;
  }
  return arr;
}

This will return an object from that array, using the first element (line) as key names:

function arrayHash(arr) {
  let head = arr[0]
  let body = arr.slice(1)
  return body.map(row => {
    return row.reduce((acc, v, i) => {
      let key = head[i]
      acc[key] = v
      return acc
    }, {})
  })
}
1 Like
4

Thanks for your answers!
The following script worked for me (creating an object), since I don’t have the key names in the first line of the CSV I’m setting them manuelly. (All data I want to use has names, the rest is numbered)

 function csvJSON(csv){  
  let lines = csv.split("\n");
  let result = [];
  let headers = ["Zeit", "Bezug", "Einspeisung", "PV", "1", "2", "3", "4", "Laden", "Entladen", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "SoC", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32"]
  for(let i=0;i<lines.length;i++){
      let obj = {};
      let currentline=lines[i].split(",");
      for(let j=0;j<headers.length;j++){
          obj[headers[j]] = currentline[j];
      }
      result.push(obj);
  }
  return result;
}