CODEWITHSUNDEEP
javascripttypescriptcsv
EJZ
EJZ

Reputation: 1256

Create a line break between rows without blank cell when exporting a CSV file in JavaScript

The following is my script for exporting data to a CSV file in JS. All of the data is pulled in as a single array called arrayPulledFromServer. With that, I am trying to separate each users data inside of it into rows in a CSV. There are only three values per user, so it gets cut off when it hits the 3 limit. From there I push the new users information unto another array called row.

  var masterCSV: any[] = [];
  var row: any[] = [];

    arrayPulledFromServer?.map((record) => {
      masterCSV.push(
        [record.name],
        [record.age],
        [record.city]        
      );
      row.push(masterCSV);

      if (masterCSV.length == 3) {
        row.push('\n');
        masterCSV = [];
      }
    });

    const rows = [
      [
        "Full Name",
        "Age",
        "City"
      ],
      [row],
    ];

    let csvContent =
      "data:text/csv;charset=utf-8," + rows.map((e) => e.join(",")).join("\n");
    var encodedUri = encodeURI(csvContent);
    var link = document.createElement("a");
    link.setAttribute("href", encodedUri);
    link.setAttribute("download", "records.csv");
    document.body.appendChild(link);
    link.click();
  };

This code works fine, but when I export it as is, the second row is indented one space inward: CSV example There are also various blank cells toward the end.

I have tried using using \u2028 instead of \n but didn't see any results. When I print the entire array it doesn't show any blank spaces, only the '\n'. I would greatly appreciate any advice you could provide on cleaning up this export.

Upvotes: 0

Views: 860

Answers (2)

John Afolayan
John Afolayan

Reputation: 36

I ran your solution and noticed that every row, except the headers, was prefixed with a , before the actual row data. That translates to an empty cell on every row.

The problem was the way the arrays were manipulated. I helped fix it.

// rows is an array of arrays with the structure
// [
//    [name1, age1, city1],
//    [name2, age2, city2]
// ]
var rows: any[] = [];

// some dummy data
const arrayPulledFromServer = [
    { name: "John", age: 45, city: "City 1" },
    { name: "Klar", age: 23, city: "City 2" },
    { name: "Poll", age: 31, city: "City 3" },
];

arrayPulledFromServer?.map((record) => {
    // push a new row
    rows.push([
        record.name,
        record.age,
        record.city,
    ]);
});

// prepend the headers
rows.unshift([
    "Full Name",
    "Age",
    "City"
]);

let csvContent =
    "data:text/csv;charset=utf-8," + rows.map((e) => e.join(",")).join("\n");
var encodedUri = encodeURI(csvContent);
console.log(csvContent);

This should work for you.

Upvotes: 2

Mr. Polywhirl
Mr. Polywhirl

Reputation: 48693

Break the problem into pieces.

  1. Know your data
  2. Know your columns
  3. Build a matrix from your columns and data
  4. Convert the matrix into a line-separated list of comma-delimited values
  5. Set the href to the appropriate string

const data = [
  { name: 'John' , age: 18 , city: 'Chicago'  },
  { name: 'Ben'  , age: 19 , city: 'New York' }
];

const cols = [
  { field: 'name' , text: 'Full Name' },
  { field: 'age'  , text: 'Age'       },
  { field: 'city' , text: 'City'      }
];

const toMatrix = (data, columns) => [
  columns.map(col => col.text),
  ...data.map(record => columns.map(col => record[col.field]))
];

const toCsv = (data, columns, delim = ',') =>
  toMatrix(data, cols).map(row => row.join(delim)).join('\n');

const download = (data, columns) => {
  const csvContent = `data:text/csv;charset=utf-8,${toCsv(data, cols)}`;
  const encodedUri = encodeURI(csvContent);
  const link = document.createElement('a');
  link.setAttribute('href', encodedUri);
  link.setAttribute('download', 'records.csv');
  document.body.appendChild(link);
  link.click();
}

download(data, cols);

Upvotes: 0

Related Questions