vba - 使用 DoCmd.TransferText 命令将表导出到 .csv 时，如何保留必要的小数位？

Question

我在 MS-Access-2010 VBA 中使用 DoCmd.TransferText 将表导出到 .csv 文件。但是，当我这样做时，生成的 .csv 文件会截断表中的信息。例如，经度 -85.350223 变为 -85.35。如何在生成的 .csv 文件仍然以逗号分隔并保留表格中的完整信息的情况下制作它？

如果我需要创建导入/导出规范并使用 DoCmd.TransferText 的 SpecificationName 功能在命令行中引用它(假设我已将此功能正确解释为格式化工具)，请解释如何执行此操作。

这是我目前用于将文件导出到 .csv 的行:

DoCmd.TransferText acExportDelim, , "AllMetersAvgRSSI", CurrentProject.Path & "\AllMetersAvgRSSI.csv"

Answer 1

我建议你使用取自 eraserve 的这个函数:

这是您使用/调用它的方式:

Call ExportToCSV("AllMetersAvgRSSI", _
                  CurrentProject.Path & "\AllMetersAvgRssi.csv")  

这是功能:

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 

您也可以通过将有问题的字段更改为文本字段，或者在导出之前将它们简单地复制到一些临时文本字段中来取得成功。