Using Alasql can you replace null column with empty string

Question

In my angular controller i have ExportToExcel function, which is using alasql to export data to Excel and the data is coming from angular array. The issue is the array might contains null as data, is it a way to replace null to empty string in alasql

Following is the function

 ac.ExportToExcel = function () {
        var time = new Date().toJSON().slice(0, 10);
        alasql('SELECT * INTO XLSX("ExchangeReport' + time + '.xlsx",{headers:true}) FROM ? WHERE BillingPeriod = "' + ac.ExchangeReport.BillingPeriod + '" or "' + ac.ExchangeReport.BillingPeriod + '" = "" ', [ac.ExchangeDatas]);
    }

And this is my data source

ac.ExchangeDatas = [];

from

        ac.GetAllExchangeData = function () {
        AccountRepository.GetAllExchangeData().$promise.then(
            function (data) {
                ac.ExchangeDatas = data.result;
            },
            function (err) {
                console.log("error in GetAllExchangeData : " + err);
            }
        );
    }

Data in excel:

score 3 · Answer 1 · edited Apr 11 '17 at 11:16

3

var old = JSON.stringify($scope.achData).replace(/null/g, '""'); //convert to 
JSON string and puts empty string in place of null values   
var newArray = JSON.parse(old); //convert back to array  
alasql('select * into XLSX("amar.xlsx", {headers:true}) from ?', [newArray]);

edited Apr 11 '17 at 11:16

Hakan Fıstık

16,800
14
110
131

answered Apr 11 '17 at 10:46

Amar Husain

69
5

score 1 · Answer 2 · answered Jun 01 '17 at 18:17

1

You should be able to use COALESCE, though you need to list all the columns like COALESCE(YourColumn,'') AS YourColumn instead of *. See https://github.com/agershun/alasql/wiki/Coalesce

answered Jun 01 '17 at 18:17

ckcn

179
1
9

Using Alasql can you replace null column with empty string

2 Answers2