Microsoft Access Import A Tab Delimited File Into A Table
The program listed below allows tab-delimited files to be imported into tables. There are actually two modules required: The first module is attached to a form's button click event and actually performs the import by calling the second module, a class module defined outside the form.
Program Code
Option Compare Database
Option Explicit
' *******************************************************
' Note: Define the following Variable in a Regular Module
' (Not in This Module Which is Attached to a Form)
' Public strImportDirectory As String
' *******************************************************
' ********************************************************
' The Following Module Is Part Of A Form And Is Triggered
' By Clicking An "Import File" Button
' ********************************************************
Private Sub cmdImportTabDelimited_Click()
' ********************************************************
' Import A Tab-Delimited File With Known Format
' Note: This requires the Class Module CommonDialogueAPI
' ********************************************************
Dim cdlg As New CommonDialogAPI
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
Dim i As Integer
Dim strFullPathToInput As String
Dim objFSO As Object
Dim objTextStream As Object
Dim strTextLine As String
Dim strInputFileName As String
On Error GoTo Handle_Error
lngFormHwnd = Me.Hwnd
lngAppInstance = Application.hWndAccessApp
' ********************************************************
' When The User Navigates To The File to Open, Start
' At the C:\ Directory The First Time, But Remember
' The User's Directory Choice For Additional Imports
' ********************************************************
' ********************************************************
' Set Global Default Directory to C:\ If not Initialized
' ********************************************************
If strImportDirectory = "" Then
strImportDirectory = "C:\"
End If
' ********************************************************
' Set The Browse Open Directory To The Saved
' Directory Name
' ********************************************************
strInitDir = strImportDirectory
' ********************************************************
' Set The Filter For Types Of Files Allowed For Import
' ********************************************************
strFileFilter = "Text Files (*.txt)" & _
Chr(0) & "*.txt" & Chr(0)
' ********************************************************
' Present The User With An "Open File" Dialogue Box
' ********************************************************
lngResult = cdlg.OpenFileDialog(lngFormHwnd, _
lngAppInstance, strInitDir, strFileFilter)
' ********************************************************
' Clear The Destination Table For Import Of Any Previous
' Data
' ********************************************************
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteImportTableData", acViewNormal, acEdit
DoCmd.SetWarnings True
' ********************************************************
' Get The Full Path of the File Selected By The User
' cdlg.GetStatus Is True If Successful
' ********************************************************
If cdlg.GetStatus = True Then
' Remove the Hex Zeroes The strInputFileName Variable
strInputFileName = cdlg.GetName
For i = 1 To 257
If Asc(Mid(strInputFileName, i, 1)) = 0 Then
strFullPathToInput = Left(strInputFileName, i - 1)
Exit For
End If
Next i
' ********************************************************
' Save The Directory Path So That The Next Import Doesn't
' Need to Start At C:\
' ********************************************************
strImportDirectory = Left(strFullPathToInput, InStrRev(strFullPathToInput, "\"))
' ********************************************************
' Set Up File System Objects That Allow The Program
' To Read The Text File And Validate It
' ********************************************************
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextStream = objFSO.OpenTextFile(strInputFileName)
strTextLine = objTextStream.ReadLine
' ********************************************************
' Check The First Line of the Imported Text File
' To Ensure That It Is In The Expected Format
' ********************************************************
If Left(strTextLine, 7) <> "caption" Then
MsgBox ("The Tab Delimited File Selected Does Not Match The Expected Format")
objTextStream.Close
Set objFSO = Nothing
Set objTextStream = Nothing
Exit Sub
Else
objTextStream.Close
Set objFSO = Nothing
Set objTextStream = Nothing
End If
End If
' ********************************************************
' If The File Selection Is Valid Then Import The Data
' Into A Table. You Must Have Previously Set Up The
' Import Specification And Defined The Import Table
' ********************************************************
' Note that the "AIMSImportSpecification" must be created
' by using the File, Get External Data tool and saving
' the import specification with that name. Creation
' of the import specification must be completed before
' attempting to do the DoCmd.TransferText procedure.
' **********************************************************
If cdlg.GetStatus = True Then
DoCmd.TransferText acImportDelim, "AIMSImportSpecification", "tblAIMSInventoryImport", _
cdlg.GetName, True
Else
MsgBox "No file selected."
Exit Sub
End If
Exit Sub
Handle_Error:
MsgBox ("Error in Tab Delimited Import - No Records Imported")
MsgBox ("Error " & Err & ": " & Error(Err))
Application.Quit
End Sub
' **********************************************************************
' The Following Module Is Required To Be Compiled As A Class Module
' It allows files to be opened - It Is The CommonDialogAPI
' **********************************************************************
Option Compare Database
Option Explicit
' **********************************************************************
' CommonDialogAPI
' **********************************************************************
Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private mstrFileName As String
Private mblnStatus As Boolean
Public Property Let GetName(strName As String)
mstrFileName = strName
End Property
Public Property Get GetName() As String
GetName = mstrFileName
End Property
Public Property Let GetStatus(blnStatus As Boolean)
mblnStatus = blnStatus
End Property
Public Property Get GetStatus() As Boolean
GetStatus = mblnStatus
End Property
Public Function OpenFileDialog(lngFormHwnd As Long, _
lngAppInstance As Long, strInitDir As String, strFileFilter As String) As Long
Dim OpenFile As OPENFILENAME
Dim X As Long
With OpenFile
.lStructSize = Len(OpenFile)
.hwndOwner = lngFormHwnd
.hInstance = lngAppInstance
.lpstrFilter = strFileFilter
.nFilterIndex = 1
.lpstrFile = String(257, 0)
.nMaxFile = Len(OpenFile.lpstrFile) - 1
.lpstrFileTitle = OpenFile.lpstrFile
.nMaxFileTitle = OpenFile.nMaxFile
.lpstrInitialDir = strInitDir
.lpstrTitle = "Open File"
.Flags = 0
End With
X = GetOpenFileName(OpenFile)
If X = 0 Then
mstrFileName = "none"
mblnStatus = False
Else
mstrFileName = Trim(OpenFile.lpstrFile)
mblnStatus = True
End If
End Function
Public Function SaveFileDialog(lngFormHwnd As Long, _
lngAppInstance As Long, strInitDir As String, strFileFilter As String) As Long
Dim SaveFile As OPENFILENAME
Dim X As Long
With SaveFile
.lStructSize = Len(SaveFile)
.hwndOwner = lngFormHwnd
.hInstance = lngAppInstance
.lpstrFilter = strFileFilter
.nFilterIndex = 1
.lpstrFile = String(257, 0)
.nMaxFile = Len(SaveFile.lpstrFile) - 1
.lpstrFileTitle = SaveFile.lpstrFile
.nMaxFileTitle = SaveFile.nMaxFile
.lpstrInitialDir = strInitDir
.lpstrTitle = "Save File"
.Flags = 0
End With
X = GetSaveFileName(SaveFile)
If X = 0 Then
mstrFileName = "none"
mblnStatus = False
Else
mstrFileName = Trim(SaveFile.lpstrFile)
mblnStatus = True
End If
End Function
