JS built CSV breaking after # in Excel

Question

I'm building CSVs via JavaScript and some of the cell values contain hashtags and other special characters. I have noticed in particular that the hashtags act as a hard stop for the file. I have not been able to find a solution online, nor have I really seen this specific issue. I have tried the \uFEFF fix, but to no avail. Below is the function I use to build my CSVs.

export const buildCSV = (headers, data) => {
  if (!headers && !data) return;

  let csvContent = "data:text/csv;charset=UTF-8,";

  if (headers) {
    csvContent += headers.join(",") + "\r\n";
  }

  if (data) {
    data.forEach(row => {
      csvContent += row.join(",") + "\r\n";
    });
  }

  return encodeURI(csvContent);
};

For reference, currently, a value such as 150 # content will show up as 150 and the rest of the rows are cutoff when opening the CSV.

Thank you in advance to anyone that takes the time to provide an answer!

So the user can download the created file using an `` – Rory L. Sep 16 '20 at 17:07 — Rory L., Sep 16 '20 at 17:07

Rory L. · Accepted Answer · 2020-09-17T13:09:20.910

10

I managed to resolve the issue by making this switch:

  const encodedURI = encodeURI(csvContent);
  const fixedEncodedURI = encodedURI.replaceAll('#', '%23');
  return fixedEncodedURI;

Performing the switch prior to running encodeURI caused the result to be "%23" instead of correctly converting it back to a hashtag. According to MDN (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/encodeURI#Description), the encodeURI function doesn't escape hashtags (among other characters, however I haven't noticed an issue with the other characters). After manually encoding them, they are converted back to hashtags once downloaded and opened in Excel.

TKoL created a fiddle showing the use of encodeURIComponent resulting in the desired output. Some things to note, as I ran into an issue attempting to use this function, is that the following changes must be made to the initial code or it will result in a failed download:

let csvContent = "";

other code...

return "data:text/csv;charset=utf-8," + encodeURIComponent(csvContent);

Thank you to all that provided potential solutions!

edited Sep 17 '20 at 13:09

answered Sep 16 '20 at 18:28

Rory L.

308
2
13

I will accept this answer once the system allows me, which is apparently in 2 days. – Rory L. Sep 16 '20 at 20:07
What about `encodeURIComponent`, which does replace hashtags? – TKoL Sep 17 '20 at 07:57
2

I made [this jsfiddle](https://jsfiddle.net/1gxsp75o/1/) to test `encodeURIComponent` – TKoL Sep 17 '20 at 08:02
Hm, that does indeed work. It seems my issue when attempting that solution was that if `data:text/csv;charset=UTF-8,` is added to the `csvContent` variable before `encodeURIComponent` is ran, it results in a failed download. However, apparently if you add the data specification to the result of `encodeURIComponent` it functions as expected. – Rory L. Sep 17 '20 at 13:03
Have you considered making the uri out of a blob instead? https://gist.github.com/dhunmoon/d743b327c673b589e7acfcbc5633ff4b – TKoL Sep 17 '20 at 13:07

score 1 · Answer 2 · answered Sep 16 '20 at 18:22

Picked clues from an answer here JavaScript blob filename without link.

The below given minimal reproduction of your code seems to be working. It relies on download attribute in HTML5. So, does not support older browsers. But If you want that support, above link has more answers you can check.

<script>
 const buildCSV = (headers, data) => {
  if (!headers && !data) return;

  let csvContent = "";

  if (headers) {
    csvContent += headers.join(",") + "\r\n";
  }

  if (data) {
    data.forEach(row => {
      csvContent += row.join(",") + "\r\n";
    });
  }
return csvContent;
}

function downloadFile()
{
 function download(data, fileName) {
      var a = document.createElement("a");
    document.body.appendChild(a);
    a.style = "display: none";
            blob = new Blob([data], {type: "text/csv"}),
            url = window.URL.createObjectURL(blob);
        a.href = url;
        a.download = fileName;
        a.click();
        window.URL.revokeObjectURL(url);
    };
var data = buildCSV(['header1','header2','header3'],[['150#content','rowvalue1','rowvalue2']]),
    fileName = "myfile.csv";
    download(data, fileName);
}

</script>
<body>
<a href="" onclick="downloadFile()" >Download</a>
</body>

JS built CSV breaking after # in Excel

2 Answers2