0

I have a CSV file like this one below, I must take some different values from it, but I'm struggling in transform this CSV into an array of Objects

"+++++++++ 1.2 LifeTime Cost and Emissions +++++++++","<TABLE>"
" ","year1","year2","year3","year4","year5","year6","year7","year8","year9","year10","year11","year12","year13","year14","year15","year16","year17","year18","year19","year20","year21","year22","year23","year24","year25","<HEADER>"
"Total Annual Energy Costs (incl. annualized capital costs and electricity sales) ($)",-560.9845,353.4204,451.6855,514.2567,523.2091,572.8177,622.6726,632.3996,642.4129,652.7211,663.3330,674.2575,1458.1040,617.1780,661.0587,692.5061,705.1385,732.5260,760.2972,774.0806,788.2706,802.8795,817.9194,833.4033,849.3444
"Total Annual CO2 emissions (kg)",387734.0330,387734.0330,387736.8925,387736.8925,387736.8925,387738.4191,387738.4191,387738.4191,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886
"Levelized Cost Of Energy ($/kWh)",-0.1738,0.1095,0.1404,0.1598,0.1626,0.1786,0.1942,0.1972,0.2003,0.2035,0.2069,0.2103,0.4547,0.1925,0.2061,0.2159,0.2199,0.2284,0.2371,0.2414,0.2458,0.2504,0.2551,0.2599,0.2649

I've tryed this:

const csvFilePath = 'Result_Lifetime002.csv';
const json = await csvToJson().fromFile(csvFilePath);
const jsonString = JSON.stringify(json, null, 2);

But it returns a big string with an array in it.

Anyway, the expected result should be something like this (just taking the item 1.2 as an example):

const result = [
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Total Annual Energy Costs (incl. annualized capital costs and electricity sales) ($)",
        "year1": -560.9845,
        "year2": 353.4204,
        "year3": 451.6855,
        "year4": 514.2567,
        "year5": 523.2091,
        "year6": 572.8177,
        "year7": 622.6726,
        "year8": 632.3996,
        "year9": 642.4129,
        "year10": 652.7211,
        "year11": 663.3330,
        "year12": 674.2575,
        "year13": 1458.1040,
        "year14": 617.1780,
        "year15": 661.0587,
        "year16": 692.5061,
        "year17": 705.1385,
        "year18": 732.5260,
        "year19": 760.2972,
        "year20": 774.0806,
        "year21": 788.2706,
        "year22": 802.8795,
        "year23": 817.9194,
        "year24": 833.4033,
        "year25": 849.3444
    },
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Total Annual CO2 emissions (kg)",
        "year1": 387734.0330,
        "year2": 387734.0330,
        "year3": 387736.8925,
        "year4": 387736.8925,
        "year5": 387736.8925,
        "year6": 387738.4191,
        "year7": 387738.4191,
        "year8": 387738.4191,
        "year9": 387738.8886,
        "year10": 387738.8886,
        "year11": 387738.8886,
        "year12": 387738.8886,
        "year13": 387738.8886,
        "year14": 387738.8886,
        "year15": 387738.8886,
        "year16": 387738.8886,
        "year17": 387738.8886,
        "year18": 387738.8886,
        "year19": 387738.8886,
        "year20": 387738.8886,
        "year21": 387738.8886,
        "year22": 387738.8886,
        "year23": 387738.8886,
        "year24": 387738.8886,
        "year25": 387738.8886,
    },
    {
        "+++++++++ 1.2 LifeTime Cost and Emissions +++++++++":"Levelized Cost Of Energy ($/kWh)",
        "year1": -0.1738,
        "year2": 0.1095,
        "year3": 0.1404,
        "year4": 0.1598,
        "year5": 0.1626,
        "year6": 0.1786,
        "year7": 0.1942,
        "year8": 0.1972,
        "year9": 0.2003,
        "year10": 0.2035,
        "year11": 0.2069,
        "year12": 0.2103,
        "year13": 0.4547,
        "year14": 0.1925,
        "year15": 0.2061,
        "year16": 0.2159,
        "year17": 0.2199,
        "year18": 0.2284,
        "year19": 0.2371,
        "year20": 0.2414,
        "year21": 0.2458,
        "year22": 0.2504,
        "year23": 0.2551,
        "year24": 0.2599,
        "year25": 0.2649
    }
]
Fravadona
  • 13,917
  • 1
  • 23
  • 35
Diego Filastro
  • 1
  • 4

1 Answers1

1

Would something like this work for you?

For simplicity's sake, I've placed the contents of your CSV inside a variable, and skipped the steps of reading the file (I'll give this code at very end). Please note that there are most likely more optimal ways of dealing with this, but I decided on going with this solution, since I could break it down into simple steps.

var data = `
+++++++++ 1.2 LifeTime Cost and Emissions +++++++++,<TABLE>
 ,year1,year2,year3,year4,year5,year6,year7,year8,year9,year10,year11,year12,year13,year14,year15,year16,year17,year18,year19,year20,year21,year22,year23,year24,year25,<HEADER>
Total Annual Energy Costs (incl. annualized capital costs and electricity sales) ($),-560.9845,353.4204,451.6855,514.2567,523.2091,572.8177,622.6726,632.3996,642.4129,652.7211,663.3330,674.2575,1458.1040,617.1780,661.0587,692.5061,705.1385,732.5260,760.2972,774.0806,788.2706,802.8795,817.9194,833.4033,849.3444
Total Annual CO2 emissions (kg),387734.0330,387734.0330,387736.8925,387736.8925,387736.8925,387738.4191,387738.4191,387738.4191,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886,387738.8886
Levelized Cost Of Energy ($/kWh),-0.1738,0.1095,0.1404,0.1598,0.1626,0.1786,0.1942,0.1972,0.2003,0.2035,0.2069,0.2103,0.4547,0.1925,0.2061,0.2159,0.2199,0.2284,0.2371,0.2414,0.2458,0.2504,0.2551,0.2599,0.2649
`;

// Create an array by splitting the CSV by newline
data = data.trim().split(/\r?\n/);

// Extract the first index, since we want to repeat it later on as
// the first key of every JSON element of your resulting array
// This is the long string with the pluses
/* remove this multiline comment to see the contents of data[0]
console.log(data[0]);
*/
var title = data[0].split(",")[0];

// Extract the other main keys - years
var innerKeys = data[1].trim().split(",");

// Remove the the last one, since we don't need it - <HEADER>
innerKeys.pop();

// Prepare the array for our results
var results = [];

// Loop through indivdual rows, and split by comma / ,
// We'll skip the first two, since we've dealt with them
// data[0] being the row with the long string with the pluses
// and data[1] being the row containing the years

for(var i = 2; i < data.length; i++) {
    // Let's clean any trailing empty characters
    var tempRow = data[i].trim();

    // If there's anything we can work with
    if(tempRow) {
        // Create an array from the values in the current line
        tempRow = tempRow.split(",");

        // Let's get the value for our first keys
        // These are the Total Annual etc strings from your CSV
        var tempTitle = tempRow[0];

        // Let's declare and fill our temp object
        var innerJSON = {};

        // The first key is the one with the pluses
        // and its value is the Total Annual etc string
        innerJSON[title] = tempTitle;
        for(var j = 1; j < tempRow.length; j++) {
            // Let's fill the years and give them matching values
            innerJSON[innerKeys[j]] = tempRow[j];
        }

        // All done, add it to the resulting array
        results.push(innerJSON);
    }
}

console.log(results);

Now, if we were to read the contents of the data variable from you CSV, using FileReader object would do the trick. You could implement it like this.

var data = "";
var reader = new FileReader();
// I'm assuming you have an input element, whose type is file
// and that you read from it
reader = readAsText(document.getElementById("myFileInput").files[0]);

reader.addEventListener('load',function() {
    data = reader.result;
    parseData(data);
});

where parseData(data) would be a function doing all of the things shown in the first part of my answer (splitting your CSV into an array, looping, etc). Try it out below.

const file = document.getElementById("file");
const parse = document.getElementById("parse");
var data = "";

parse.addEventListener("click", readFile);

function readFile() {
  let reader = new FileReader();
  reader.readAsText(file.files[0]);
  
  reader.addEventListener('load', function(e) { 
    data = reader.result;
    
    parseData(data);
  });
}

function parseData(data) {
  // Let's remove the quotes first - you don't have to do this
  // if it's absolutely necessary to keep them
    data = data.replaceAll("\"","");
  // Create an array by splitting the CSV by newline
  data = data.trim().split(/\r?\n/);

  // Extract the first index, since we want to repeat it later on as
  // the first key of every JSON element of your resulting array
  // This is the long string with the pluses
  /* remove this multiline comment to see the contents of data[0]
  console.log(data[0]);
  */
  var title = data[0].split(",")[0];

  // Extract the other main keys - years
  var innerKeys = data[1].trim().split(",");

  // Remove the the last one, since we don't need it - <HEADER>
  innerKeys.pop();

  // Prepare the array for our results
  var results = [];

  // Loop through indivdual rows, and split by comma / ,
  // We'll skip the first two, since we've dealt with them
  // data[0] being the row with the long string with the pluses
  // and data[1] being the row containing the years

  for(var i = 2; i < data.length; i++) {
      // Let's clean any trailing empty characters
      var tempRow = data[i].trim();

      // If there's anything we can work with
      if(tempRow) {
          // Create an array from the values in the current line
          tempRow = tempRow.split(",");

          // Let's get the value for our first keys
          // These are the Total Annual etc strings from your CSV
          var tempTitle = tempRow[0];

          // Let's declare and fill our temp object
          var innerJSON = {};

          // The first key is the one with the pluses
          // and its value is the Total Annual etc string
          innerJSON[title] = tempTitle;
          for(var j = 1; j < tempRow.length; j++) {
              // Let's fill the years and give them matching values
              innerJSON[innerKeys[j]] = tempRow[j];
          }

          // All done, add it to the resulting array
          results.push(innerJSON);
      }
  }

  console.log(results);
}
<input type="file" id="file">
<button type="button" id="parse">Parse</button>
FiddlingAway
  • 1,598
  • 3
  • 14
  • 30
  • 1
    That wasn't the final answer to my problem, but certainly was a part of it, it really helped me to "stop spinning", thank you! – Diego Filastro Jan 30 '23 at 13:50