Parse ASCII as plain text exporting to Excel from js

Question

Having a bit of trouble when exporting a spreadsheet. The tool is to create tracking links, everything works fine except when the link contains ascii characters. For example, someurl.com&trackingparameter%3d.

When this happens, it all works within the app, but when exported to the spreadsheet it would read as someurl.com&trackingparameter=.

Wondering how I can parse these ascii characters as plain text on the export. Thanks in advance for any assistance.

Best,

Erik

           <script>
    $(document).ready(function() {
        $("#btnExport").click(function(e) {
            e.preventDefault();

            var data_type = 'data:application/vnd.ms-excel';
            var table_div = document.getElementById('wrapper');
            var table_html = table_div.outerHTML.replace(/ /g, '%20');
           
            var a = document.createElement('a');
            a.href = data_type + ', ' + table_html;
            a.download = 'exported_URLS_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
            a.click();
        });
    });

</script>

 <script type="text/javascript">
    function createTable() {
        var deletebutton = '<button class="delete uk-button uk-button-danger" id="delete">delete</button>';
        var tbody = '';
        var descripdata = '';
        var simpleURLdata = '';
        var description = document.getElementById('description').value;
        var simpleURL = document.getElementById('simpleURL').value;
        var baseURL = document.getElementById('baseURL').value;
        var s1 = document.getElementById('s1').value;
        var s2 = document.getElementById('s2').value;
        var s3 = document.getElementById('s3').value;
        var s4 = document.getElementById('s4').value;
        var ts = document.getElementById('ts').value;
        var hdr = document.getElementById('hdr').value;
        var hdrid = document.getElementById('hdrid').value;
        var dmhdr = document.getElementById('dmhdr').value;
        var utm_source = document.getElementById('utm_source').value;
        var utm_medium = document.getElementById('utm_medium').value;
        var utm_campaign = document.getElementById('utm_campaign').value;
        var utm_category = document.getElementById('utm_category').value;

        tbody += '<td>';
        tbody += baseURL;
        if (s1 != '') {
            tbody += '&s1=';
            tbody += s1;
        }
        if (s2 != '') {
            tbody += '&s2=';
            tbody += s2;
        }
        if (s3 != '') {
            tbody += '&s3=';
            tbody += s3;
        }
        if (s4 != '') {
            tbody += '&s4=';
            tbody += s4;
        }
        if (ts != '') {
            tbody += '&ts=';
            tbody += ts;
        }
        if (hdr != '') {
            tbody += '&hdr='
            tbody += hdr;
        }
        if (dmhdr != '') {
            tbody += '&dmhdr=';
            tbody += dmhdr;
        }
        if (hdrid != '') {
            tbody += '&hdrid=';
            tbody += hdrid;
        }
        if (utm_source != '') {
            tbody += '&utm_source=';
            tbody += utm_source;
        }
        if (utm_medium != '') {
            tbody += '&utm_medium=';
            tbody += utm_medium;
        }
        if (utm_campaign != '') {
            tbody += '&utm_campaign=';
            tbody += utm_campaign;
        }
        if (utm_category != '') {
            tbody += '&utm_category=';
            tbody += utm_category;
        }
        tbody += '</td>';

        descripdata += '<td>';
        if (description != '') {
            descripdata += description;
        }
        descripdata += '</td>';

        simpleURLdata += '<td>';

        if (simpleURL != '') {
            simpleURLdata += simpleURL;
        }
        simpleURLdata += '</td>';

        var table = document.getElementById('urls');
        var row = table.insertRow(0);
        var cell1 = row.insertCell(0);
        cell1.innerHTML = tbody;

        var cell2 = row.insertCell(1);
        cell2.innerHTML = descripdata;

        var cell3 = row.insertCell(2);
        cell3.innerHTML = simpleURLdata;

        var cell4 = row.insertCell(3);
        cell4.innerHTML = deletebutton;
    }

</script>

But when it is exported to the .xls, it gives the character from the code, e.g %3d is converted to =. I want to ignore the ascii codes and leave the string as is. — edh4131, Jun 23 '17 at 19:17
They're not 'ASCII codes', they're [percent-encoded](https://en.wikipedia.org/wiki/Percent-encoding), often called 'url encoded'. Just FYI. — Karl Reid, Jun 23 '17 at 19:19
Where is the code which exports the URLs? Are you saving the URLs in a spreadsheet or are you reading them from a spreadsheet? — Code-Apprentice, Jun 23 '17 at 19:20
that is the code that creates the spreadsheet. It takes user input data that is put into a html table and exports that to the .xls. I will add code for the table. edit- added code that creates table. — edh4131, Jun 23 '17 at 19:21
@KarlReid thanks karl that's actually really helpful because i was searching the wrong thing. — edh4131, Jun 23 '17 at 19:39
No problem. Knowing what to Google for is a key tool for solving problems, I find. — Karl Reid, Jun 23 '17 at 19:42
@KarlReid Re: percent-encoded: Agreed, and they're not ASCII code units either; They are UTF-8 code units. — Tom Blodget, Jun 24 '17 at 01:24

score 1 · Accepted Answer · answered Jun 23 '17 at 19:51

1

You can use javascript native functions escape(), encodeURI() or encodeURIComponent() if you want to retain %3d as %3d.

Further help regarding these can be found at When are you supposed to use escape instead of encodeURI / encodeURIComponent?

answered Jun 23 '17 at 19:51

Rajeev Ranjan

3,588
6
28
52

Well, the problem seems to only be with the spreadsheet. It all renders properly in the applet. See http://websciencedesigns.com/tracker/. That's a dev link to it. If you exported a spreadsheet, only at that time are the urls messed up. – edh4131 Jun 23 '17 at 19:55
Yes, so in that specific case, you can escape these characters which would then be rendered properly in spreadsheet. For example - `%3d` would translate to `%253d`. This will go on to become `%3d` in your spreadsheet. – Rajeev Ranjan Jun 23 '17 at 20:02
So basically, I would need to escape all ASCII codes in case the user input one? – edh4131 Jun 23 '17 at 20:09
this was the correct answer. the following code adjustments fixed the issue: var table_html = table_div.outerHTML; var a = document.createElement('a'); a.href = data_type + ', ' + encodeURIComponent(table_html); – edh4131 Jun 23 '17 at 21:23

Parse ASCII as plain text exporting to Excel from js

1 Answers1