ODBC到.xlsb文件正在将数字数据转换为字符串

Question

我有一个.xlsb文件，我通过ODBC连接到该文件，这些信息直接存储在一个数据透视缓存中，然后我可以使用它进行游戏。由于我的至少四列一直以文本形式存储(通过双击数据透视表的一部分并看到绿色标签告诉我它存储为文本来确认)，这使得不可能做总结为平均值等事情。我需要找到一种方法，让它们返回到数值。

我在网上发现了一些其他信息，这些信息似乎认为Excel是根据单元格格式选择数据类型的，所以我更新了代码，使所有所需列都格式化为数字("0.00")，但遗憾的是，这并没有帮助。

该代码首先从一个充满.csv文件的目录中获取信息(强制设置数字格式的代码也在这里)：

For Each objFile In objFolder.Files
Set tgtSheet = ThisWorkbook.Sheets.Add
tgtSheet.Name = objFile.Name
On Error Resume Next
With tgtSheet.QueryTables.Add(Connection:="TEXT;" & objFile, Destination:=tgtSheet.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
On Error GoTo 0
' ------ Force data formatting onto columns
lastRow = FindLastCell(tgtSheet).Row
For i = 1 To UBound(titleArray)
currCol = ColumnByTitle(tgtSheet, titleArray(i))
With tgtSheet
Set columnRange = .Range(.Cells(1, currCol), Cells(lastRow,     currCol)).EntireColumn
columnRange.NumberFormat = formatArray(i)
End With
Next i
Next objFile

其中columnArray和titleArray是长度为4的字符串数组，分别包含适当的列标题和格式。然后输出到.xlsb:

With ThisWorkbook
strPath = .Path
strFile = .FullName
strFileTemp = strPath & "DBtemp" & ".xlsb"
.Worksheets(arrSheets).Copy
End With

最后，ODB连接是通过在当前工作簿中仍然打开的工作表中构建SQL命令来设置的：

For i = LBound(arrSheets) To UBound(arrSheets)
If arrSheets(i) <> ActiveSheet.Name Then
If strSQL = "" Then
strSQL = "SELECT * FROM [" & arrSheets(i) & "$]"
Else
strSQL = strSQL & " UNION ALL SELECT * FROM [" & arrSheets(i) & "$]"
End If
End If
Next i
' ------ set up connection string
strCon = _
"ODBC;" & _
"DSN=Excel Files;" & _
"DBQ=" & strFileTemp & ";" & _
"DefaultDir=" & strPath & ";" & _
"DriverId=790;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"
Set pc = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
' ------ test pivot table to play around with
With pc
.Connection = strCon
.CommandType = xlCmdSql
.CommandText = strSQL
Set pt = .CreatePivotTable(TableDestination:=ActiveSheet.Range("A1"))
pt.Name = "TestPivot"
End With

为了清晰起见，我遗漏了一些内容，但如果上下文需要，请告诉我！

此外，我之所以这样做(将csv导入工作表，将其重新输出到.xlsb，并连接到该工作表)，是因为当我尝试通过ADODB记录集导入时，我根本无法使用切片器，这在这种情况下是必要的。

我也知道像powerpivot这样的工具可以很容易地完成这项工作，但遗憾的是，我不能使用它们——我的计算机上没有管理员访问权限，安装程序太耗时/不可能。

如果能为我提供任何重大帮助，或者代表我指出任何明显的疏忽，我们将不胜感激，我花了一整天的时间试图解决这个问题！

编辑1:将文件类型从xlsb更改为xlsx不会有任何作用。此外，在任何一种情况下，列的格式仍然正确设置为数字("0.00")。我认为问题可能出在ODBC部分？似乎没有太多有用的文档。

第2版：Excel 2010。

第3版：.csv文件的示例输入：

Image date, Image time, Anatomy, View, kVp, mAs, EI, DAP, Reject reason, Status
42005, 3.33E-02, Chest, P.A., 124, 1.7, 135, 9.83,, confirmed       
42005, 3.40E-02, Chest, Lat., 124, 3.9, 137, 23.84,, confirmed      
42005, 3.82E-02, Chest, Lat., 124, 1.6, 95, 9.09,, confirmed        

Answer 1

好的，所以我终于解决了这个问题。我完全取消了导入csv文件并将其导出为.xlsb的步骤，因为它们丝毫没有修复格式错误。我重新访问了另一个工作簿中的代码，该工作簿实际上创建了.csv文件，并在那里强制设置了我想要的格式：

' ------------------------------------------------------------------------------------
' Create string arrays to handle looking for correct columns and setting the proper
' formatting in them so it doesn't convert to varchar when imported again later
' ------------------------------------------------------------------------------------
ReDim titleArray(1 To 4)
titleArray(1) = "kVp"
titleArray(2) = "mAs"
titleArray(3) = "EI"
titleArray(4) = "DAP"
ReDim formatArray(1 To 4)
formatArray(1) = "0.00"
formatArray(2) = "0.00"
formatArray(3) = "0.00"
formatArray(4) = "0.00"
' ------ Force data formatting onto columns
lastRow = FindLastCell(tgtCSV.Sheets(1)).row
For i = 1 To UBound(titleArray)
currCol = ColumnByTitle(tgtCSV.Sheets(1), titleArray(i))
With tgtCSV.Sheets(1)
Set lastCell = .Range(.Cells(1, currCol), Cells(lastRow, currCol)).EntireColumn
lastCell.NumberFormat = formatArray(i)
End With
Next i

然后，在我的分析工作簿中，我简化了导入过程，只使用microsoft文本驱动程序，并明确调用了一个新创建的透视表版本(与我所做的其他ODBC内容一样，它默认为禁止使用切片器的早期版本)

' ------------------------------------------------------------------------------------
' Build the connection string and add the connection
' ------------------------------------------------------------------------------------
Workbooks("EXRAnalysis.xlsm").Connections.Add "Test", "", Array( _
Array("ODBC;DBQ=" & objFolder & ";" & _
"DefaultDir=" & objFolder & ";"), _
Array("Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DriverId=27;" & _
"HDR=Yes;" & _
"MaxBufferSize=2048"), _
Array(";MaxScanRows=0;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")), _
strSQL, 2
' ------------------------------------------------------------------------------------
' Create ODBC connection
' TODOTODOTODO: fix so it wipes out old connection or properly overwrites it. Use code
' to update pivot cache pointers to new connection.
' ------------------------------------------------------------------------------------
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Test"), Version:=xlPivotTableVersion14 _
).CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14

代码有点乱，因为它使用了一个记录的宏(当然)，但它是可用的(而且不是很慢，是的)。