如何通过VBA在字段名称中导出带有点的CSV格式的表？ [英] How to export a table in access to CSV with dot in field names through VBA?

问题描述

我需要在导出CSV的字段名称中包含点。我不能在访问表字段中包括点，我找不到一种方法来更新VBA的CSV字段名称。任何建议？谢谢。

解决方案

你所描述的是很简单的。对于[Table1]中的示例数据

  ID text column int column datetime columnothercolumn 
  -  ----------- ---------- ------------------- ----- ------------- 
 1 foo 3 1991-11-21 01:23:45这是一个测试。 
 2 bar 9 2013-12-31 23:59:59 ...所以就是这样。 
  

以下VBA代码

选项比较数据库
选项比较数据库
 
 public Sub dotCsvExport（TableName As String，FileSpec As String）
 Dim cdb As DAO.Database，tbd As DAO.TableDef，fld As DAO.Field 
 Dim s As String，line As String，tempFileSpec As String 
 Dim fso As Object'FileSystemObject 
 Dim fOut As Object' TextStream 
 Dim fTemp As Object'TextStream 
 Const TemporaryFolder = 2 
 Const ForReading = 1 
 Const ForWriting = 2 
 
设置cdb = CurrentDb 
设置fso = CreateObject（Scripting.FileSystemObject）'新文件系统对象
 tempFileSpec = fso.GetSpecialFolder（TemporaryFolder）& fso.GetTempName 
 
'只将数据导出到临时文件
 DoCmd.TransferText _ 
 TransferType：= acExportDelim，_ 
 TableName：= TableName，_ 
 FileName：= tempFileSpec，_ 
 HasFieldNames：= False 
 
设置fTemp = fso.OpenTextFile（tempFileSpec，ForReading，False）
设置fOut = fso.OpenTextFile（FileSpec， ForWriting，True）
 
'构建CSV标题行，将替换为。在字段名称
设置tbd = cdb.TableDefs（TableName）
 line =
对于每个fld在tbd.Fields 
s = fld.Name 
s = Replace s，，。，1，-1，vbBinaryCompare）
s = Replace（s，，，1，-1，vbBinaryCompare）
如果Len （线）> 0 Then 
 line = line& ，
 End If 
 line = line& & s& 
 Next 
设置fld =无
设置tbd =无
 
将CSV标题行写入输出文件
 fOut.WriteLine行
 
'从临时文件追加实际数据
 fOut.Write fTemp.ReadAll 
 
 fOut.Close 
设置fOut = Nothing 
 fTemp.Close 
设置fTemp =无
 Kill tempFileSpec 
设置fso =无
设置cdb =无
结束子
  
 
 
 
产生此CSV档案
 ID，text.column，int.column，datetime.column，othercolumn
 1，foo，3,1991-11 -21 01:23:45，这是一个测试。 
 2，bar，9,2013-12-31 23:59:59，...就是这样。 
  
 
I need to have dot in the field names of the export CSV. I cannot include dot in the access table field, and I cannot find a way to update the CSV field name by VBA. Any suggestion? Thanks.
解决方案
What you describe is quite straightforward. For sample data in [Table1]
ID  text column  int column  datetime column      "other" column    
--  -----------  ----------  -------------------  ------------------
 1  foo                   3  1991-11-21 01:23:45  This is a test.   
 2  bar                   9  2013-12-31 23:59:59  ...and so is this.
the following VBA code
Option Compare Database
Option Explicit

Public Sub dotCsvExport(TableName As String, FileSpec As String)
    Dim cdb As DAO.Database, tbd As DAO.TableDef, fld As DAO.Field
    Dim s As String, line As String, tempFileSpec As String
    Dim fso As Object  ' FileSystemObject
    Dim fOut As Object  ' TextStream
    Dim fTemp As Object  ' TextStream
    Const TemporaryFolder = 2
    Const ForReading = 1
    Const ForWriting = 2

    Set cdb = CurrentDb
    Set fso = CreateObject("Scripting.FileSystemObject")  ' New FileSystemObject
    tempFileSpec = fso.GetSpecialFolder(TemporaryFolder) & fso.GetTempName

    ' export just the data to a temporary file
    DoCmd.TransferText _
            TransferType:=acExportDelim, _
            TableName:=TableName, _
            FileName:=tempFileSpec, _
            HasFieldNames:=False

    Set fTemp = fso.OpenTextFile(tempFileSpec, ForReading, False)
    Set fOut = fso.OpenTextFile(FileSpec, ForWriting, True)

    ' build the CSV header line, replacing " " with "." in field names
    Set tbd = cdb.TableDefs(TableName)
    line = ""
    For Each fld In tbd.Fields
        s = fld.Name
        s = Replace(s, " ", ".", 1, -1, vbBinaryCompare)
        s = Replace(s, """", """""", 1, -1, vbBinaryCompare)
        If Len(line) > 0 Then
            line = line & ","
        End If
        line = line & """" & s & """"
    Next
    Set fld = Nothing
    Set tbd = Nothing

    ' write the CSV header line to the output file
    fOut.WriteLine line

    ' append the actual data from the temporary file
    fOut.Write fTemp.ReadAll

    fOut.Close
    Set fOut = Nothing
    fTemp.Close
    Set fTemp = Nothing
    Kill tempFileSpec
    Set fso = Nothing
    Set cdb = Nothing
End Sub
produces this CSV file
"ID","text.column","int.column","datetime.column","""other"".column"
1,"foo",3,1991-11-21 01:23:45,"This is a test."
2,"bar",9,2013-12-31 23:59:59,"...and so is this."


                        
这篇关于如何通过VBA在字段名称中导出带有点的CSV格式的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！