How to remove space and comma in csv download files

[former_member227082]

Hi All,

     I am new to sapui5. I have downloading ui5 tables as CSV format by refereed below link.

Those tables data i am getting from XSODATA Service. service returning result as Json Format.

Now the problem is my CSV file having %20 and %2 instead of space and comma values(if I remove enCodeURIComponent, values are misplacing in excel cells which is values having comma and space). I have added my code snippets below.

View.js

tableObj = sap.ui.getCore().byId("myTableId");
var oModel = new sap.ui.model.json.JSONModel();
jQuery.ajax({
                   url: url,  
                 dataType: "json",
               beforeSend: function(){
                tableObj.setBusy(true);
           },
            success: function(data, textStatus, jqXHR) { 
                totalResultsCount = data.d.results.length;
               var b64text = generateTableCSV(tableObj,data.d.results);
                    sap.ui.getCore().byId("id_exportcsvbutton").setHref('data:application/csv;charset=utf-8;base64,' + (Base64.encode(b64text)));
                    setTimeout(function (){
                        $( "#Myprgs_exportcsv" ).attr('download', 'MyProgress.csv');
                    },1000); 
          error: function(jqXHR, textStatus, errorThrown) {

            },
            complete: function(){
         tableObj.setBusy(false);
           }
        });

downloadExcel.js

function generateTableCSV(table, jsonData) {
    for (var i = 0; i < table.getColumns().length; i++) {        
    info += encodeURIComponent(table.getColumns()[i].getLabel().getText())
            + ',';                
        }            
        }
    info += '\r\n';

    if (jsonData.length != undefined) {
        for (var j = 0; j < jsonData.length; j++) {            
            for (var i = 0; i < table.getColumns().length; i++) {                
                var dk=  table.getColumns()[i].getTemplate().getBinding(
                'value').sPath;
                if (table.getColumns()[i].getTemplate() != undefined 
                && table.getColumns()[i].getTemplate().getBinding(
                                'value') != undefined) {
                    var valor = eval('jsonData[j].'+ dk);    
                    info += encodeURIComponent(valor) + ',';    
                    } 
            }
            info += '\r\n';
            }
    } else {
        $.each(jsonData, function(key, value) {
            for (var i = 0; i < table.getColumns().length; i++) {                
                var dk=  table.getColumns()[i].getTemplate().getBinding('value').sPath;
                if (table.getColumns()[i].getTemplate() != undefined && table.getColumns()[i].getTemplate().getBinding('text') != undefined) {
                    var valor = eval('jsonData[j].'+ dk);
                    info += encodeURIComponent(valor) + ',';
                }                 
            }
            info += '\r\n';
        });
    }
    return info;
}

Base 64 Util.js

I have using same code which is in this link 
Base 64 Util.

also I have attached my CSV output as screenshot. Can some one help me to resolve this?? Any help would be greatly appreciated.

Thanks,

Jayan.M

[former_member190010 · Accepted solution]

Hi Jayan,

The problem is your file cells contains "," which at the same time is your csv separator.

  info += encodeURIComponent(valor) + ',';

You should change your csv separator from"," to ";" or another one.

This way you won't have this issues anymore.

Once you change your separator, the previous sentence would look like this:

info += valor + ';';

In thenext post you can find the explanation on how to change your csv file separator.

Import or export text (.txt or .csv) files - Excel

Regards,

Emanel

[former_member227082 · Accepted solution]

Hi Emanel,

    Thanks for your reply. I have tried your approach. it's adding Semicolon instead of comma but the problem is  my xsodata service data also having some comma values so it's placing comma values to next cell in csv file. Is there any way to remove comma values while getting json response?

Thanks,

Jayan

[former_member190010 · Accepted solution]

You can replace the comma values using the javascript function replace.

You can find more detail at the following place:

JavaScript String replace() Method

Regards,

Emanuel

[former_member227082 · Accepted solution]

Hi Emanuel,

    Thanks for reply. I have did replace function like this

var valor = (dk == "MATERIAL" || dk == "Material") ? (eval('jsonData[j].'+ dk)).replace(/,/g , " ") : eval('jsonData[j].'+ dk) ;

It's working fine...   Thanks...

[former_member190010 · Accepted solution]

hapy to hear that Jaya.

Regards,

Emanuel