使用 DoCmd.TransferText 命令将表导出到 .csv 时，如何保留必要的小数位? [英] How do I keep the necessary decimal places when using the DoCmd.TransferText command to export a table to a .csv?

问题描述

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

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?

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

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.

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

Here is the line I am currently using to export the file to .csv:

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

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

推荐答案

我建议你使用取自 擦除:

以下是您使用/调用它的方式:

Here's how you use/call it:

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 

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

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屋！