Access VBA DoCmd.TransferText Method

This Access VBA tutorial explains how to import and export Access Objects to text using DoCmd.TransferText Method.

You may also want to read

Access VBA Export all Queries to Excel

Access VBA import txt using DoCmd.TransferText Method

Syntax of  DoCmd.TransferText Method

DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
Name Required/Optional Data Type Description
TransferType Optional AcTextTransferType The type of transfer you want to make. You can import data from, export data to, or link to data in delimited or fixed-width text files or HTML files. The default value is acImportDelim. Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).

Name Value Description
acExportDelim 2 Export Delimited
acExportFixed 3 Export Fixed Width
acExportHTML 8 Export HTML
acExportMerge 4 Export Microsot Word Merge
acImportDelim 0 Import Delimited
acImportFixed 1 Import Fixed Width
acImportHTML 7 Import HTML
acLinkDelim 5 Link Delimited
acLinkFixed 6 Link Fixed Width
acLinkHTML 9 Link HTML
SpecificationName Optional Variant A string expression that’s the name of an import or export specification you’ve created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
TableName Optional Variant A string expression that’s the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.
FileName Optional Variant A string expression that’s the full name, including the path, of the text file you want to import from, export to, or link to.
HasFieldNames Optional Variant Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.
HTMLTableName Optional Variant A string expression that’s the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the TransferType argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there’s a <CAPTION> tag. If there’s no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.
CodePage Optional Variant A Long value indicating the character set of the code page.

Export Access Query to TXT using DoCmd.TransferText Method

The below Procedure exports Query1 to the C:\test\ folder. The file will contain field name in the first row.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Access VBA Export all Queries to Text File 01

By default, text files exported via DoCmd.TransferText Method contain double quote around the data and field name.

There is no argument in the Method that we can use to remove the double quote, fortunately there is workaround using Specification argument in the Method.

First, perform a manual export using non-VBA approach (right click on a Query > Export > Text File > OK > click on the Advanced button

Access VBA DoCmd.TransferText Method 05

 

In the Text Qualifier dropdown box, select none > click on Save As > OK

Access VBA Export all Queries to Text File 02

 

Now add one more parameter SpecificationName to the Procedure.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Double quotes are removed.

Access VBA DoCmd.TransferText Method 06

Export Access Query to CSV using DoCmd.TransferText Method

There is no argument in DoCmd.TransferText Method to define how data is separated, whether by comma, semi colon, tab, etc. Instead we should define it in the Specification first.

As explained earlier, export a Query in the non-VBA way, press Advance button to define a Specification.

Access VBA Export all Queries to Text File 03

Then we can write the below Sub Procedure using the above CSV specification, also change the file extension to .csv

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="CSV Specification", TableName:="Query1", FileName:="C:\test\Query1.csv", hasfieldnames:=True
End Sub

Export all Access Query using DoCmd.TransferText Method

For details refer to my previous post.

Public Sub export_query()
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:=qdf.Name, FileName:="C:\test\" & qdf.Name & ".txt", hasfieldnames:=True
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
End Sub

Import TXT using DoCmd.TransferText Method

Prepare a TXT file

Access VBA DoCmd.TransferText Method 06

 

You can either create a blank Table in Access that will be used to import the TXT file, or let the Macro generate a  new one for you.

Access VBA Export all Queries to Text File 04

 

Again, you have to specify whether the text file is delimited by semi colon, tab, comma etc using the previously created Specification. Run the below Procedure.

Public Sub import_query()
    DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Query1 Export Specification", TableName:="import_table", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Import all TXT files in the folder

Public Sub import()
    Dim FileName, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer

    DoCmd.SetWarnings False
    Path = "C:\test\"
    FileName = Dir(Path & "")
    
    While FileName <> "" And Right(FileName, 3) = "txt"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileName
        FileName = Dir()
    Wend
    
    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            FilePathName = Path & FileNameList(FileCount)
            DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Query1 Export Specification", TableName:="import_table", FileName:=FilePathName, hasfieldnames:=True
        Next
    End If
    DoCmd.SetWarnings True
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff835958.aspx

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

10 thoughts on “Access VBA DoCmd.TransferText Method

  1. Great post! It helped me a lot, thank you! However, how do I get rid of the 0.00 decimal places in .csv file? Is there a way to get only round numbers? Thank you!

    1. Hi Viki, I guess you mean your original value in Access is in 0.00 format but you want it to gone in CSV output? I don’t think it is possible, but you can have 2 workarounds
      1) Create an Expression in Query and use the round function before export
      2) Write an Excel Macro in Access to open the CSV and make the change

  2. Hello, i’m french so don’t look at my english ! Its’s realy well , but i need to import several txt file one by one in différents table temp, it ‘s to extract and treat each file with a query. I’ve got 50 txt file per day.
    Thank you for you help.

  3. Hello, yes, a txt file have 130 lines, i import one Field with fixed lenght 71. I have to execute a query to transform 130 lines in one (query is ok) and add it in a table. I just need a loop to create a tbl_temp to each txt file and delete it after the run query. I’am just begining with VBA. Thank a lot to get your time to my probleme !

    1. I am going to make a new post tomorrow to
      1) import multiple files in one table
      2) import multiple files in separate tables using file name
      hope that it is what you want

Leave a Reply

Your email address will not be published. Required fields are marked *