TheInformationLab
Excel Error when NULL value in numeric column
When exporting a table that has a column with currency, Excel gives the following error message:
After clicking yes, it displays the column, but only as decimal and not currency.
Upon further testing, it appears that the issue is when there is a NULL value in the data rather than 0. So, if there is any numeric field and there is a NULL value in the data, then that error message will pop up.
the "NULL" string is displayed as soon as there is a null value in a column, whatever its original format (text, date, numeric, boolean). One could use as a workaround, the zn function,or something with the iif (isnull([myfield]):'':[myfield]) but what to say for a boolean ?
experiencing similar issues; I've got 10 views configured in ExportAll. All have NULLs in numeric data. 3 views export fine, and show the NULLs properly. 7 of the views do not export; I get the above 2 messages already reported, and then 'Repair Result to ExportAll-30.xml' files are opened (see below). So far, I'm stumped trying to find the cause or pattern. Any clues? (have tested in Firefox and Chrome, on Mac. Same behaviour on Windows.
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to ExportAll-20.xml</logFileName>
Have just found this extension and it's perfect for what I'm looking for. A lot of my users need to download the data to incorporate it into other reports they are preparing. The use of crosstab / data downloads is fiddly for end users (who in my case can be using the dashboards twice a year). But with Export All I can set up an Export sheet with the basic info in it, and then 'hide it' (put it at -10, -10, size 1 x 1). The Export button then neatly downloads a simple to use data file. Finding this extension has saved me a huge amount of work!
But - this bug with number data with missing (ie NULL) values is a problem as users I have tested with this all expressed the concern, having seen the error message, that there could be missing data (which there isn't).
Do you have an idea of when the fix for this bug might be available? Ideally it would just strip out the NULLs leaving the 'Null' cells in Excel empty.
I believe one could use the 'alias' option to replace null by ' ' under Desktop as a workaround, on each measure ?
On Wed, Apr 8, 2020 at 4:54 PM paulanson [email protected] wrote:
Have just found this extension and it's perfect for what I'm looking for. A lot of my users need to download the data to incorporate it into other reports they are preparing. The use of crosstab / data downloads is fiddly for end users (who in my case can be using the dashboards twice a year). But with Export All I can set up an Export sheet with the basic info in it, and then 'hide it' (put it at -10, -10, size 1 x 1). The Export button then neatly downloads a simple to use data file. Finding this extension has saved me a huge amount of work!
But - this bug with number data with missing (ie NULL) values is a problem as users I have tested with this all expressed the concern, having seen the error message, that there could be missing data (which there isn't).
Do you have an idea of when the fix for this bug might be available? Ideally it would just strip out the NULLs leaving the 'Null' cells in Excel empty.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/TheInformationLab/Tableau-Extension-ExportAll/issues/7#issuecomment-611006110, or unsubscribe https://github.com/notifications/unsubscribe-auth/AH7OP5ALSCZ4AQOQL2VHFCLRLSFY5ANCNFSM4IE4BJJA .
Using the alias option does allow you to replace null by a space (' '). It looks fine in Tableau, but the issue is that a space is still a string so you get a column of mixed numbers and strings, and when you open the exported Excel file you get the same XML error message. The issue is caused by a mixed column of numbers and strings. It's just that the sting 'Null' is the default that Tableau puts out for a null value. Swapping Null for '' is very useful though for getting rid of Null as an entry in text columns.