如何保持必要的小数位使用DoCmd.TransferText命令表导出到.csv什么时候? [英] How do I keep the necessary decimal places when using the DoCmd.TransferText command to export a table to a .csv?
问题描述
我使用的 DoCmd.TransferText 的在MS-ACCESS-2010 VBA来表导出到.csv文件。然而,当我这样做所产生的.csv文件截断表中的信息。例如经度-85.350223变为-85.35。我如何让它在那里得到的.csv文件仍然是逗号分隔,并保持从表中的全部信息?
如果我需要创建一个导入/导出规范,并使用引用它在命令行中的 SpecificationName 的DoCmd.TransferText的功能(假设我已经正确地跨preTED此功能作为一个格式化工具),请解释如何做到这一点。
下面是我目前使用的导出文件为.csv行:
DoCmd.TransferText acExportDelim, AllMetersAvgRSSI CurrentProject.Path和放大器; \ AllMetersAvgRSSI.csv
我建议你使用此功能从的 eraserve :
下面是你如何使用/调用它:
呼叫ExportToCSV(AllMetersAvgRSSI,_
CurrentProject.Path和放大器; \ AllMetersAvgRssi.csv)
和这里的功能:
公共功能ExportToCSV(表名作为字符串_
strFile作为字符串_
可选tfQualifier为布尔,_
可选strDelimiter的String =,_
可选的字段名作为布尔)为字节
参考文献:的Microsoft Access 11.0对象库,Microsoft DAO 3.6对象库
'设置在code查看窗口中点击工具引用,然后参考
导出表到一个文本文件中。
接受
表名:目标表的名称
strFile:路径和文件名的表导出到
tfQualifier:真或假
strDelimiter:字符串值默认为逗号:,
字段名:真或假
用法:ExportToCSV表名,strFile,真正的,真
对错误转到errhandler
昏暗intOpenFile作为整数,X为整数
昏暗STRSQL作为字符串,strCSV作为字符串,strPrint作为字符串,strQualifier作为字符串
关闭所有打开的文件,不是我们期望任何
重置
抢下一页免费文件编号
intOpenFile = FreeFile
打开我们的文件工作
打开strFile输出访问写为#intOpenFile
'编写的表的内容将文件
打开源
STRSQL =SELECT * FROM&放大器;表名和放大器; 作为与&表名
'设置限定符
strQualifier = CHR(34)
随着CurrentDb.OpenRecordset(STRSQL,dbOpenSnapshot)
检查,如果我们需要的字段名称
如果字段名= TRUE,则
对于x = 0到.Fields.Count - 1
如果tfQualifier = TRUE,则
根据需要写的字段名称
预选赛是strQualifier或引用
strCSV = strCSV和放大器; strQualifier和放大器; strDelimiter和放大器; strQualifier和放大器; _
点域(X).Name点
添加最后strQualifier
如果x = .Fields.Count - 1。然后
strCSV = strCSV和放大器; strQualifier
结束如果
其他
根据需要写的字段名称
没有资格赛
strCSV = strCSV和放大器; strDelimiter和放大器;点域(X).Name点
结束如果
下一个x
'写入文件
strPrint = MID(strCSV,莱恩(strDelimiter)+ 2)
打印#intOpenFile,strPrint
结束如果
写的CSV
做直到.EOF
strCSV =
对于x = 0到.Fields.Count - 1
检查资格赛
如果tfQualifier = TRUE,则
预选赛是strQualifier或引用
strCSV = strCSV和放大器; strQualifier和放大器; strDelimiter和放大器; strQualifier和放大器; _
新西兰(点域(X),vbNullString)
添加最后strQualifier
如果x = .Fields.Count - 1。然后
strCSV = strCSV和放大器; strQualifier
结束如果
其他
没有资格赛
strCSV = strCSV和放大器; strDelimiter和放大器;新西兰(点域(X),vbNullString)
结束如果
下一个x
消除背靠背strQualifiers或预选赛,如果改变
strCSV =替换(strCSV,strQualifier和放大器; strQualifier,)
strPrint = MID(strCSV,莱恩(strDelimiter)+ 2)
打印#intOpenFile,strPrint
.MoveNext
循环
结束与
ExitHere:
关闭文件
关闭#intOpenFile
退出功能
errhandler:
随着犯错
MSGBOX错误和放大器; .Number和放大器; vbCrLf&安培; 。描述, _
vbOKOnly或者vbCritical,ExportToCSV
结束与
简历ExitHere
端功能
您也可以通过改变有问题的字段文本字段,或者简单地将其复制到一些临时文本字段你做导出之前有所成就。
I am using the DoCmd.TransferText in MS-Access-2010 VBA to export a table to a .csv file. However when I do this the resulting .csv file truncates the information in the table. For example the longitude -85.350223 becomes -85.35. How do I make it where the resulting .csv file is still comma delimited and keeps the full information from the table?
If I need to create an Import/Export specification and reference it in the command line using the SpecificationName feature of DoCmd.TransferText (assuming I have correctly interpreted this feature as a formatting tool) please explain how to do that.
Here is the line I am currently using to export the file to .csv:
DoCmd.TransferText acExportDelim, , "AllMetersAvgRSSI", CurrentProject.Path & "\AllMetersAvgRSSI.csv"
I recommend you use this function taken from eraserve:
Here's how you use/call it:
Call ExportToCSV("AllMetersAvgRSSI", _
CurrentProject.Path & "\AllMetersAvgRssi.csv")
And here's the function:
Public Function ExportToCSV(TableName As String , _
strFile As String , _
Optional tfQualifier As Boolean , _
Optional strDelimiter As String = "," , _
Optional FieldNames As Boolean ) As Byte
'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
'Set references by Clicking Tools and Then References in the Code View window
'
' Exports a table to a text file.
' Accepts
' Tablename: Name of the Target Table
' strFile: Path and Filename to Export the table to
' tfQualifier: True or False
'strDelimiter: String Value defaults to comma: ,
' FieldNames: True or False
'
'USAGE: ExportToCSV TableName, strFile, True, ",", True
On Error GoTo errhandler
Dim intOpenFile As Integer , x As Integer
Dim strSQL As String , strCSV As String , strPrint As String , strQualifier As String
'Close any open files, not that we expect any
Reset
'Grab Next Free File Number
intOpenFile = FreeFile
'OPen our file for work
Open strFile For Output Access Write As # intOpenFile
'Write the contents of the table to the file
'Open the source
strSQL = "SELECT * FROM " & TableName & " As " & TableName
'set the qualifer
strQualifier = Chr( 34 )
With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'Check if we need Field Names
If FieldNames = True Then
For x = 0 To .Fields.Count - 1
If tfQualifier = True Then
'Write the Field Names as needed
'The Qualifier is strQualifier or Quote
strCSV = strCSV & strQualifier & strDelimiter & strQualifier & _
.Fields(x).Name
'Add last strQualifier
If x = .Fields.Count - 1 Then
strCSV = strCSV & strQualifier
End If
Else
'Write the Field Names as needed
'No Qualifier
strCSV = strCSV & strDelimiter & .Fields(x).Name
End If
Next x
'Write to File
strPrint = Mid(strCSV, Len(strDelimiter) + 2 )
Print # intOpenFile, strPrint
End If
'Write the CSV
Do Until .EOF
strCSV = ""
For x = 0 To .Fields.Count - 1
'Check for Qualifier
If tfQualifier = True Then
'The Qualifier is strQualifier or Quote
strCSV = strCSV & strQualifier & strDelimiter & strQualifier & _
Nz(.Fields(x), vbNullString)
'Add last strQualifier
If x = .Fields.Count - 1 Then
strCSV = strCSV & strQualifier
End If
Else
'No Qualifier
strCSV = strCSV & strDelimiter & Nz(.Fields(x), vbNullString)
End If
Next x
'Eliminate Back to back strQualifiers or Qualifiers if changed
strCSV = Replace(strCSV, strQualifier & strQualifier, "" )
strPrint = Mid(strCSV, Len(strDelimiter) + 2 )
Print # intOpenFile, strPrint
.MoveNext
Loop
End With
ExitHere:
'Close the file
Close # intOpenFile
Exit Function
errhandler:
With Err
MsgBox "Error " & .Number & vbCrLf & .Description, _
vbOKOnly Or vbCritical, "ExportToCSV"
End With
Resume ExitHere
End Function
You may also have success by changing the offending fields to text fields, or simply copying them into some temporary text fields before you do the export.
这篇关于如何保持必要的小数位使用DoCmd.TransferText命令表导出到.csv什么时候?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!