All values in the Excel sheet are Strings

[marco79cgn]

Hi,
when exporting anything from Tableau (Server) every single column inside the Excel file is a String. This leads to the problem that you can't calculate with numbers, build sums etc.

Is there any way to avoid this behaviour? The underlying SheetJS should have no problems with real numbers but it seems that Tableau somehow masks every attribute as String. The same thing happens when using the internal csv export option of Tableau by the way.

[craigbloodworth]

I did raise this issue with Tableau tableau/extensions-api#121 and while the issue may have been fixed on Tableau's side (although the issue hasn't been closed) I haven't had chance to dive in and check. I'll update when I know.

[marco79cgn]

Tableau says this issue has been fixed since version 2018.2 (which we are using).

I had a look at your source code as well as the underlying SheetJS library. I can understand why everything is a string in the resulting Excel file.

The call sheet.getSummaryDataAsync({ignoreSelection: true}).then((data) returns the data from Tableau:

• data.columns contains the column names including their corrrect data types (int, date, String etc.)
  [ { "_fieldName":"Date", "_dataType":"date", "_isReferenced":true, "_index":0 }, { "_fieldName":"id_tag_container", "_dataType":"int", "_isReferenced":true, "_index":1 }, { "_fieldName":"Measure Names", "_dataType":"string", "_isReferenced":true, "_index":2 }, { "_fieldName":"Measure Values", "_dataType":"float", "_isReferenced":true, "_index":8 } // etc. ]
• data.data which you decode to a rows json contains all the data but every single value is masked as string
  rows[0] { "Date":"8/1/2018", "id_tag_container":"47110815", "Measure Names":"RPM Ad Gross", "Partner":"FunnyCompany App Android", "Partner Account ID":"001xxxxxxxxxs7AAC", "Promoter":"APromoter", "Promoter Account ID":"001xxxxxxxxxxtAAB", "Tag Container":"Imaginary-Tag-Container-Name", "Measure Values":"0.11" }

The rows json is then forwarded to the SheetJS library with the call XLSX.utils.json_to_sheet(rows, {header:headers}); and what this function does is that it is guessing the data type from the json attribute! Of course everything is guessed to be a string because it was one beforehand (data.data > rows)!

Unfortunately it's not possible to forward the data types of data.columns to the json_to_sheet call in order to tell it which attribute should be which data type. So the only solution is to set the correct data types in the json beforehand (in your decodeRows function). For example:
now/wrong:

rows[0]
{  
    "id_tag_container":"47110815",`
   //...
}

then/correct:

{  
    "id_tag_container": 47110815,`
   //...
}

This needs to be done with all possible data types. If this fixed json is then forwarded to SheetJS, the result is what you would expect!

//UPDATE:
It can be easily fixed in your decodeRows function. This one already corrects date, float and int:

function decodeRows(columns, headers, dataset, callback, ret) {
  if (!ret || ret == null) {
    var retArr = [];
  } else {
    var retArr = ret;
  }
  var thisRow = dataset[0];
  var meta = {};
  for (var j = 0; j < columns.length; j++) {
    if (headers.indexOf(columns[j].fieldName) > -1) {
      if(columns[j].dataType == 'int') {
        meta[columns[j].fieldName] = parseInt(thisRow[j].value);  
      } else if (columns[j].dataType == 'float'){
        meta[columns[j].fieldName] = parseFloat(thisRow[j].value);
      } else if (columns[j].dataType == 'date'){
        meta[columns[j].fieldName] = new Date(thisRow[j].value);
      } else {
        meta[columns[j].fieldName] = thisRow[j].formattedValue;
      }
    }
  }
  retArr.push(meta);
  dataset.splice(0,1);
  if (dataset.length == 0) {
    callback(retArr);
  } else {
    decodeRows(columns, headers, dataset, callback, retArr);
  }
}

[shimrot]

Hello. I needed a version to run on a local server so no external server would be used. I didn't see this issue earlier, which would have helped my debug. However, I've created a version where I've :

-- updated to the latest version of the extension API, and latest for community xslx.js (not sure if last needed)
-- added types to the cells (slightly diff approach as above, but same effect)
-- adds "ignoreEC:false" to the XLXS write options, to prevent Excel from crashing when attempting "text to column" operations (used to convert text to numbers if desired).
-- I've refactored "buildExcelBlob" to make iterative rather than recursive (little faster and prevents potential issues with large data sets).
-- Some refactoring to allow bulidExcelBlob to return the data without actually doing the write (to share with another change I've been trying - I'll create a diff issue for that).

I pushed a "Col_Types" branch to my forked repository (https://github.com/shimrot/Tableau-Extension-ExportData/tree/Col_Types). It may be adequate but take a look. I have no tableau server instance with which I'm able to test. However, I was able to check results with my "indirect_server_save" branch - which requires a different discussion.

[LyleEllerbach]

Any word on when this will be available?

[craigbloodworth]

Thanks for your branch @shimrot I've now added your suggestion to the v2 release with one or two tweaks. The meta from Tableau's summary data is now passed into the Excel file.