CODEWITHSUNDEEP
vbams-accesscsv
Master Yi
Master Yi

Reputation: 31

How to export a table in access to CSV with dot in field names through VBA?

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.

Upvotes: 0

Views: 1537

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

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."

Upvotes: 1

Related Questions