将Tableau数据下载到Excel中(科学符号列) [英] Downloading Tableau data into Excel (scientific notation column)
问题描述
在我的Tableau报告之一中，我有一个获取方参考号"字段，该字段长23位.当数据下载到Excel中时，Excel将截断该列并将其显示为科学计数法(如下所示)，而不是完整的23位数字.我知道这不是Tableau的问题，但我想知道是否有人遇到过此问题以及解决方法是什么.
下载数据后，我尝试重新格式化Excel中的此列，但是它将最后几位转换为0.
我希望显示完整的获取方参考号:24692168345100558990040，而不是这样显示:2.46921690161007E + 22.
由于在Excel中幕后使用了浮点编号系统，因此似乎有一个已知的15位数字限制:
编辑以考虑Tableau Server:
Tableau Server似乎会为数据"和交叉表"下载选项生成.csv文件.Tableau Desktop为"Crosstab"选项生成一个.xlsx文件，这与之形成对比.这可能是Tableau Server创建.xlsx文件的错误或已知限制.
作为一种解决方法，由于获取方参考号"最有可能是一个维度(即:未对其执行数学运算)，因此可以创建这样的计算字段并将结果字符串解释给用户(或由用户固有地理解):
"AR:" + STR([获取方参考编号]) 将 24692168345100558990040 转换为 AR:24692168345100558990040
我已验证从Tableau Server导出到Excel时输出看起来还可以.前导字母使Excel将字段识别为文本而不是数字.
Within one of my Tableau reports, I have an Acquirer Reference Number field which is 23 digits long. When the data downloads into Excel, Excel truncates the column and shows it as scientific notation (shown below) instead of the full 23 digit number. I know this isn't a Tableau issue, but I was wondering if anybody has ran into this issue and what the workaround is.
I have tried to reformat this column in Excel after the data is downloaded, but it converts the last several digits to 0s.
I want the full Acquirer Reference Number to be displayed like this: 24692168345100558990040 instead of like this: 2.46921690161007E+22.
It looks like there is a known 15 digit limitation due to the floating point numbering system used behind the scenes in Excel:
https://superuser.com/questions/373997/adding-more-than-15-digits-in-excel
(Take note that it appears that Tableau has the same 15 digit floating point limitation when it comes to measures and mathematical operations.)
I have reproduced your situation, and even when the Acquirer Reference Number is within Tableau as a Dimension and even when it is also converted to a 'String' type - it will still export to Excel in scientific notation.
The only workaround I've been able to find is to instead of "Exporting Data" to instead "Export Crosstab to Excel" as seen below in the "Worksheet" Menu. The result is similar to that of putting an apostrophe in front of every cell in the Excel workbook. It doesn't look as pretty, it has limitations, but it might accomplish what you are trying to do.
Edit for Tableau Server consideration:
Tableau Server seems to generate .csv files for both "Data" and "Crosstab" download options. This is contrasted by Tableau Desktop generating a .xlsx file for the "Crosstab" option. This could be a bug or a known limitation of Tableau Server creating .xlsx files.
As a workaround, since the Acquirer Reference Number is a most likely a dimension (ie: not having math performed on it,) a calculated field like this could be created and resulting string explained to users (or inherently understood by them):
"AR:" + STR([Acquirer Reference Number])
Turns 24692168345100558990040 into AR:24692168345100558990040
I have verified that the output looks okay in exporting from Tableau Server to Excel. The leading letter(s) cause Excel to recognize the field as text instead of a number.
这篇关于将Tableau数据下载到Excel中(科学符号列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
