Preserve leading zero in csv file using javascript

Question

I am downloading a csv using the following code.

<a download="somedata.csv"  id='x'>Download CSV</a>
<script>
       var csv = '01'; //prints 1 in the cell, truncating leading zero
       var csv = "'"+01+"'"; //prints '01' in the cell, with single quote
       var csv = '"\""01"\""'; //prints "01" in the cell, with double quotes
       var a = document.getElementById('x');
       a.href='data:text/csv;base64,' + btoa(csv);
</script>

I need to print the numbers with leading zeroes in the csv file. The solution I have found so far is to make the number a text by wrapping it either with single or double quotes. But this does not solve the problem as it prints the quotes also in the cell.

Isn't there a way to preserve the leading zeroes and print the numbers without quotes in the cell? Any suggestions?

EDIT: I forgot to mention in the original post that I MUST need to open the csv in Excel.

var csv = "'01";   //prints '01 in the cell
var csv = '="01"'; //prints  01 in the cell, but the value is ="01"

Isn't there a way to get the cell value and display both as 01 (instead of different display and value)?

Please indicate the manner in which you have determined that the leading zero is stripped. This crucial factor is, if I'm right, the cause of the problem, yet you did not include it in your problem description! — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Dec 11, 2012 at 9:26

marc_s · Accepted Answer · 2019-04-06 08:00:40Z

4

I know it is an old question, but I recently came across this issue and fixed it by adding Zero-Width-Space character at the end like this :

var csv = '01' + String.fromCharCode(8203);

I hope it will help others who come across this issue.

edited Apr 6, 2019 at 8:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 4, 2019 at 13:05

Zardaloop

1,6625 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kristianne Nerona Over a year ago

Unfortunately this code adds this character when displayed in Excel 365: â€‹

Lightness Races in Orbit · Accepted Answer · 2012-12-11 09:24:01Z

2

<a download="somedata.csv"  id='x'>Download CSV</a>
<script>
       var csv = '01';
       var a = document.getElementById('x');
       a.href='data:text/csv;base64,' + btoa(csv);
</script>

This works just fine as it is.

I imagine that you're viewing the CSV in Excel, which re-formats the number according to its whims and to cell defaults. But if you look at the CSV in Notepad, you'll see that the leading 0 is still there in the actual file.

answered Dec 11, 2012 at 9:24

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

Comments

Fenton · Accepted Answer · 2012-12-11 09:33:29Z

2

Your code actually works:

<a download="somedata.csv"  id='x'>Download CSV</a>
<script>
       var csv = '01';
       var a = document.getElementById('x');
       a.href='data:text/csv;base64,' + btoa(csv);
</script>

If you open this file in a text-editor the leading zero is present.

If you are specifically targeting Microsoft Excel, you can add a leading single-quote to preserve the leading zero - although you'll still need to enter each cell and hit enter to make it take effect!:

<a download="somedata.csv"  id='x'>Download CSV</a>
<script>
       var csv = "'01";
       var a = document.getElementById('x');
       a.href='data:text/csv;base64,' + btoa(csv);
</script>

More convoluted - but works (again only for Excel)

<a download="somedata.csv"  id='x'>Download CSV</a>
<script>
       var csv = '="01"';
       var a = document.getElementById('x');
       a.href='data:text/csv;base64,' + btoa(csv);
</script>

edited Dec 11, 2012 at 9:33

answered Dec 11, 2012 at 9:27

Fenton

253k74 gold badges404 silver badges414 bronze badges

5 Comments

Lightness Races in Orbit Over a year ago

To be specific, this forces Excel to see the 'cell' contents as text rather than formatting it as a number. Though as you say this makes the file non-portable.

Fenton Over a year ago

Yes - the only fix I can think of that would work in Excel and everywhere else would be to have a line of headings, then a line of dummy data that were all strings, then the real data from line 3. Excel looks ahead a few lines when determining the data-type, so data with a string would force the formatter not to choose a numeric type.

Lightness Races in Orbit Over a year ago

I don't see a need for any "fix" so far. The value Excel shows is correct. The leading zero has no semantic significance. Unfortunately the OP hasn't explained why he or she cares.

Fenton Over a year ago

I worked at a pension company who had accounts migrated from companies it acquired. In that case, the difference between 0094569 and 094569 was dead important.

Lightness Races in Orbit Over a year ago

Data that must be interpreted should be quoted in CSV files; if Excel doesn't properly parse that than it's silly.

Hafizur Rahman · Accepted Answer · 2023-08-24 10:39:49Z

1

I had similar issue with CSV file, what I have found that adding a leading TAB character with number makes it work.

let csv = '\t'+'01';

This will preserve this leading zero and TAB is ignored by Excel

answered Aug 24, 2023 at 10:39

Hafizur Rahman

516 bronze badges

Collectives™ on Stack Overflow

Preserve leading zero in csv file using javascript

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related