Breaking apart values separated by semicolons in JavaScript to create separate rows with additional information

Question

Breaking apart values separated by semicolons in JavaScript to create separate rows with additional information

Here is the provided data:

result =

    [  
       {  
          "Id":"0012v00002InPVmAAN",
          "Test__c":"India; Africa; Mombasa",
          "Test1__c":"AFR; TFR; GFR"
       }
    ]

I want to convert the above data into a CSV file by splitting the semicolon separated values (Test__c & Test1__c) into multiple rows along with other data.

"Id,Test__c,Test1__c --> csv file column header
"0012v00002InPVmAAN","India","AFR"
"0012v00002InPVmAAN","Africa","TFR"
"0012v00002InPVmAAN","Mombasa","GFR"

In JavaScript -

this.data = result
downloadCSVFile() {
        let rowEnd = '\n';
        let csvString = '';
        // This set eliminates any duplicate keys
        let rowData = new Set();
        
        this.data.forEach(function (record) {
            Object.keys(record).forEach(function (key) {
                rowData.add(key);
            });
        });

        rowData = Array.from(rowData);

        csvString += rowData.join(',');
        csvString += rowEnd;

        for (let i = 0; i < this.data.length; i++) {
            let colValue = 0;

            for (let key in rowData) {
                if (rowData.hasOwnProperty(key)) {
                    let rowKey = rowData[key];
                    
                    if (colValue > 0) {
                        csvString += ',';
                    }

                    let value = this.data[i][rowKey] === undefined ? '' : this.data[i][rowKey];
                    csvString += '"' + value + '"';
                    colValue++;
                }
            }
            csvString += rowEnd;
        }

I attempted the code above, but it is combining (Test__c & Test1__c) values into a single row.

How can I properly create a CSV file by splitting the semicolon separated values (Test__c & Test1__c) into multiple rows along with other data?

javascript arrays csv

Answer 1

Answer №1

In this updated version, the downloadCSVFile function has been removed and replaced with result. If the field names ('Id', 'Test__c', 'Test1__c') remain constant, you can simplify the second part of your code:

const result = [
  {
    Id: "0012v00002InPVmAAN",
    Test__c: "India; Africa; Mombasa",
    Test1__c: "AFR; TFR; GFR"
  }
];

result.map(item => {});

let rowEnd = "\n";
let csvString = "";
// using a set to eliminate duplicates in keys
let rowData = new Set();
// extracting keys from data
result.forEach(function(record) {
  Object.keys(record).forEach(function(key) {
    rowData.add(key);
  });
});
// converting set to array
rowData = Array.from(rowData);

// separating values with ','
csvString += rowData.join(",");
csvString += rowEnd;

const lines = [];

lines.push(rowData.join(","));

result
  .reduce((acc, { Id, Test__c, Test1__c }) => {
    const a = Test__c.split(";");
    const b = Test1__c.split(";");
    a.forEach((item, index) => {
      acc.push({ Id, Test__c: item, Test1__c: b[index] });
    });
    return acc;
  }, [])
  .forEach(item => {
    lines.push(rowData.map(key => item[key]).join(","));
  });

csvString = lines.join(rowEnd);

console.log(csvString);

// Id,Test__c,Test1__c
// 0012v00002InPVmAAN,India,AFR
// 0012v00002InPVmAAN, Africa, TFR
// 0012v00002InPVmAAN, Mombasa, GFR

Answer 2

In this updated version, the downloadCSVFile function has been removed and replaced with result. If the field names ('Id', 'Test__c', 'Test1__c') remain constant, you can simplify the second part of your code:

const result = [
  {
    Id: "0012v00002InPVmAAN",
    Test__c: "India; Africa; Mombasa",
    Test1__c: "AFR; TFR; GFR"
  }
];

result.map(item => {});

let rowEnd = "\n";
let csvString = "";
// using a set to eliminate duplicates in keys
let rowData = new Set();
// extracting keys from data
result.forEach(function(record) {
  Object.keys(record).forEach(function(key) {
    rowData.add(key);
  });
});
// converting set to array
rowData = Array.from(rowData);

// separating values with ','
csvString += rowData.join(",");
csvString += rowEnd;

const lines = [];

lines.push(rowData.join(","));

result
  .reduce((acc, { Id, Test__c, Test1__c }) => {
    const a = Test__c.split(";");
    const b = Test1__c.split(";");
    a.forEach((item, index) => {
      acc.push({ Id, Test__c: item, Test1__c: b[index] });
    });
    return acc;
  }, [])
  .forEach(item => {
    lines.push(rowData.map(key => item[key]).join(","));
  });

csvString = lines.join(rowEnd);

console.log(csvString);

// Id,Test__c,Test1__c
// 0012v00002InPVmAAN,India,AFR
// 0012v00002InPVmAAN, Africa, TFR
// 0012v00002InPVmAAN, Mombasa, GFR

Breaking apart values separated by semicolons in JavaScript to create separate rows with additional information

Answer №1

Similar questions

What is the best way to turn off default CSS styling in KendoUI?

Picture is not showing up on my MVC software

Steps on how to set the values of a select option based on a JSON parsed array

What is the best way to execute individual API requests for various sheets within the same spreadsheet?

Why does my script seem to be missing from this GET request?

org.openqa.selenium.WebDriverException: unexpected issue: Chrome failed to initiate: crashed.(chrome inaccessible)

What is the best way to transfer a JavaScript object with string values containing quotes from node.js to the browser?

Running JavaScript in selenium and obtaining the result

React Array Not Behaving Properly When Checkbox Unchecked and Item Removed

Conflicting issues with jQuery's load() method

Formulation, on the other side of the comma

Learn to save Canvas graphics as an image file with the powerful combination of fabric.js and React

Unable to forward to another page using NodeJS (Express)

When you add a new library using npm and it has a dependency on another existing library, it could potentially cause conflicts or issues with

How can I retrieve the array data that was sent as a Promise?

How can I transfer Gmail message using express rendering parameters?

Challenges with handling callbacks in Javascript

Establishing the httppostedfilebase variable when validation is unsuccessful in an ASP.Net MVC view

Make an angularJS PUT request using $http

What is the difference between using array pointers and vectors in C++?