Exporting to Excel format removes zeros in starting of some strings that seems to be a number

djhmo

How to solve or avoid zero-deleting starting of strings that seems to be a number?

Example: My table contains two identifiers of objects: 888-332-12A and 000999988. Why the tool assumes that the second is a number and remove the zeros of the begining?

Thanks a lot in advance

marcw

I think you'll find that this is a problem with Excel rather than with anything providing data to Excel.
Excel always thinks it knows best what you want to do with your data. In the absence of any additional information, it will assume that anything that can be parsed as a simple number *is* a simple number.

Your best bet is to add a style to the column.

quasarblue

Excel will import numbers as string
adding prefix =" and suffix "

="00123456"

So How to do this transformation without affecting table data?
Is it this call being the one just before the export?
fnGetTableData(oConfig)

soulice

I have the same issue. We use 7 digit values with leading 0's to fill in space when less than 7 chars. The databale show 0000014 just fin, the csv shows 14. Anyone have an example style for fixing this?
spaning a tic ( ' ) mark with color of background (so it is not ugly on screen) kep the zeros in the csv...but that is ugly too.

GavinBurge

I have got around it by using the following code, it seems just putting, a number with a leading zero in quotes ("), wasnt enough, so i also added /t in the end quote, this seems to work with excel, csv and copying.

I changed some code in the fnGetDataTablesData function:

[code]
/* Rows */
for (j = 0, jLen = _DTSettings.aiDisplay.length ; j < jLen ; j++) {
/* Columns */
for (i = 0, iLen = _DTSettings.aoColumns.length ; i < iLen ; i++) {
if (_DTSettings.aoColumns[i].bVisible) {
var itemData = _DTSettings.aoData[_DTSettings.aiDisplay[j]]._aData[i].replace(/\n/g, " ").replace(/<.*?>/g, "");
if (itemData.startsWith("0")) {
itemData = '"' + itemData + '\t"';
}
sData += itemData + sSeperator;
}
}
sData = sData.slice(0, sSeperator.length * -1);
sData += sNewline;
}
[/code]

this is the startswith function:

[code]
if (typeof String.prototype.startsWith != 'function') {
String.prototype.startsWith = function (str){
return this.slice(0, str.length) == str;
};
}
[/code]