ACC2000: How to Import a Text File That Has an Extension That Access Does Not Recognize (306144)

MORE INFORMATION

To import a text file that has an extension that Access does not recognize as valid, use one of the following methods.

Method 1 (Recommended): Modify the Windows Registry

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

The recommended method is to modify the Windows registry to include the extension of the text file that you want to import.

The following registry key lists the extensions of text files that can be imported into Microsoft Access: If you try to import a text file that has an extension that is not listed in the DisabledExtensions key, you receive the following error message: The following article in the Microsoft Knowledge Base has information about modifying this registry key:

Method 2: Use Code to Change the Extension

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. A second method is to write Microsoft Visual Basic for Applications code to programmatically create a copy of the file and add a valid extension. Follow these steps and the example of code:

1. Open the database in Microsoft Access.
2. On the Insert menu, click Module to start the Visual Basic Editor and create a new, blank module.
3. Add the following code to the new module:

   Sub DataImport(strPath As String, strTableName As String)
       'Variables used to create and modify the file extension
       Dim objFileSystem
       Dim objFile
       Dim strFileCopy As String
       Dim intExtPosition As Integer
   
       'Create an instance of the FileSystemObject to access
       'the local file system
       Set objFileSystem = CreateObject("Scripting.FileSystemObject")
   
       'Use the GetFile method to return a File object corresponding to the
       'file in a specified path.
        Set objFile = objFileSystem.GetFile(strPath)
        intExtPosition = InStr(objFile.Name, ".")
        If intExtPosition > 0 Then
           strFileCopy = Left(objFile.Name, intExtPosition - 1) & ".txt"
        Else
           strFileCopy = objFile.Name & ".txt"
        End If
        
       'Create a copy of the file with a .txt extension
       objFile.Copy strFileCopy, True
       DoCmd.TransferText acImportDelim, , strTableName, strFileCopy, True
   End Sub
   					

This procedure accepts two arguments, the path of the file that you want to import and the name of the table that you want to create in the Access database. For example, to import a file named C:\Invoices.dat and create a table named MyInvoices, use the following syntax to call the procedure: