在excel中查询期间防止舍入 [英] Prevent rounding during query within excel

问题描述

SELECT'CDed for Pivots`.BeginTimeDate，`CDR for Pivots`.AccountID，`CDR for Pivots`.AgentID，`CDR for Pivots`.Caller，`CDR for Pivots`.Callee，`CDR for Pivots`。 AreaPrefix，`CDR for Pivots`.AreaName，`CDR for Pivots`.ChargedDuration，`CDR for Pivots`.CallCharges，`CDR for Pivots`.CallExpense，`CDR for Pivots`.Answered

FROM `CDR for Pivots.csv``CDR for Pivots`



以上是在Excel中运行的查询（数据菜单....连接属性...命令文本） 。



我在创建上面查询的表的某些查询中遇到了SQL舍入问题。由于本网站提供的答案，使用schema.ini解决了这个问题。这是有效的，正在查询的表是正确创建的，我可以通过（a）查看它来了解（b）总计并看到总数是正确的。但是，现在在Excel查询中进行舍入。它显然没有看schema.ini。特别是CallCharges和CallExpense都是问题所在。否则一切都很好。



我的尝试：



我尝试使用cast，dec（），作为float ....这些都不适合我。

SELECT `CDR for Pivots`.BeginTimeDate, `CDR for Pivots`.AccountID, `CDR for Pivots`.AgentID, `CDR for Pivots`.Caller, `CDR for Pivots`.Callee, `CDR for Pivots`.AreaPrefix, `CDR for Pivots`.AreaName, `CDR for Pivots`.ChargedDuration, `CDR for Pivots`.CallCharges, `CDR for Pivots`.CallExpense, `CDR for Pivots`.Answered
FROM `CDR for Pivots.csv` `CDR for Pivots`

Above is the query run within Excel (Data menu....Connection Properties... Command text).

I was having trouble with SQL rounding in some queries that create the table being queried above. It was solved by using schema.ini, thanks to answer provided at this site. That worked, and the table being queried is created correctly with I can know by (a) looking at it (b) totalling up and seeing that the totals are correct. However, now the rounding is occurring in the Excel query. It is apparently not looking at schema.ini. It is specifically CallCharges and CallExpense with are the problem. Otherwise everything is good.

What I have tried:

I tried cast, dec(), as float.... none of these worked for me.

推荐答案

您使用过CONCAT吗？尝试在Excel中转​​换为字符串。试试这个

Hi,

Have you used "CONCAT". trying to Convert to string in Excel. try this

SELECT `CDR for Pivots`.BeginTimeDate, `CDR for Pivots`.AccountID, `CDR for Pivots`.AgentID, `CDR for Pivots`.Caller, `CDR for Pivots`.Callee,
`CDR for Pivots`.AreaPrefix, `CDR for Pivots`.AreaName, `CDR for Pivots`.ChargedDuration, CONCAT('''',`CDR for Pivots`.CallCharges), CONCAT('''',`CDR for Pivots`.CallExpense), `CDR for Pivots`.Answered
FROM `CDR for Pivots.csv` `CDR for Pivots`

如果失败，您可以修改schema.ini文件。将文件放在同一文件夹中。

参考 Schema.ini文件（文本文件驱动程序） [ ^ ]

我相信我已经解决了它。当我创建表时，我按CallExpense DESC排序，它将十进制值放在文件的顶部而不是它们大多数的零。我把maxscanrows设置为8（默认情况下，我相信）然后SQL看到它需要有十进制值。这解决了这个问题。

I believe I have solved it. When I create the table, I sort by CallExpense DESC, that puts decimal values at the top of the file instead of zeroes which they mostly are. I put maxscanrows to 8 (default, I believe) and so then SQL sees that it needs to have decimal values. This solves the problem.

这篇关于在excel中查询期间防止舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！