Larry W Jordan Jr.
Derik Harris
Microsoft Corporation
Created: July 8, 1996
Revised: July 23, 1996
Larry W Jordan Jr. is a Senior Microsoft Access support engineer. His main focus is VBA, wizard and add-in development and Visual SourceSafe integration.
Derik Harris is a Senior Microsoft Access content engineer. His primary responsibility is writing, editing, and publishing Microsoft Access related material for the Microsoft Knowledge Base and the Microsoft Web site.
Click to open or copy the TEXTWIZ project files.
Microsoft Access provides numerous import filters to handle most of the common application files formats and the generic ASCII text file formats in the form of common delimited and fixed-width text files. However, there may be situations where the files you need to import into a database have an unsupported or proprietary format. In these situations, you need a custom import routine.
Creating your own custom text import wizards is a great way to handle these situations. This article explains the basics you need to implement your own import routines.
Important In order to use and modify the Sample Text Import Wizard, you must have the Microsoft Access Developer Toolkit for Windows 95 installed. The wizard uses OLE controls from this development environment, and you must have both the controls and the appropriate license installed.
The following file format is a sample format generated by the grocery store point of sale(POS) system. The POS file is available on a daily basis and needs to be imported into a temporary table. The data is then visually confirmed for correctness and posted to a main table using an append query.
Figure 1. A sample proprietary text file
Last Reset: Jul-7-1996 11:00:00
Current Reset: Jul-8-1996 11:12:00
Store: 114
1,"Dairy",456,908.13
2,"Bread",1326,348.17
3,"Meats",657,1234.00
4,"Vegetables",34,76.16
5,"Chocolate",1114,3456.15
The analysis of the file layout is the most important aspect of planning your import routine. You need to understand the file structure and be able to relate to the way in which the data in the fields will make its way into you database table structure.
The file in Figure 1 has the following characteristics:
The file format is fairly easy to analyze, but may not be handled by the filters for importing that ship with Microsoft Access. Therefore, you need a custom import routine.
The following import functions will import the data into a user-defined array structure and then create a temporary table to post the data into the database. This requires two routines: one to process the file, and another to create the temporary table.
Figure 2. Import functions
‘ Declarations section of the module.
Option Compare Database
Option Explicit
' ------------------------------------------------------
' Create user-defined Type array to
' hold the sales values.
' ------------------------------------------------------
Type SlsData
Id As Long
SlsName As String
Qty As Long
Amt As Currency
End Type
Dim SlsData() As SlsData
' ======================================================
' Function: ImportSalesDataFile
' Passed Parameter: The path/name of the file to import
‘ Returns: Boolean result
' ======================================================
Function ImportSalesDataFile(strFilename As String) As Boolean
On Local Error GoTo ImportSalesDataFile_Err
Dim i As Integer, intHandle As Integer
Dim varTempVariable As Variant
' ------------------------------------------------------
' DAO objects
' ------------------------------------------------------
Dim db As DATABASE
Dim rs As Recordset
' ------------------------------------------------------
' Dim storage variables.
' ------------------------------------------------------
Dim strLastResetDateTime As Date
Dim strCurrResetDateTime As Date
Dim intStoreId As Integer
Dim lngNumOfSalesEntries As Long
' ------------------------------------------------------
' Set file handle to first available
' file number from os...
' ------------------------------------------------------
intHandle = FreeFile
' ------------------------------------------------------
' Open requested sales data file.
' ------------------------------------------------------
Open strFilename For _
Input Access Read _
As intHandle
' ------------------------------------------------------
' Enumerate the number of sales entries for the posting
' array used to hold the sales data.
' ------------------------------------------------------
While Not EOF(intHandle)
Line Input #intHandle, varTempVariable
' ------------------------------------------------------
' Count the sale lines in the file.
' ------------------------------------------------------
lngNumOfSalesEntries = (lngNumOfSalesEntries + 1)
' ------------------------------------------------------
' File validation.
' ------------------------------------------------------
Select Case lngNumOfSalesEntries
Case 1
If InStr(varTempVariable, "Last Reset:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
Case 2
If InStr(varTempVariable, "Current Reset:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
Case 3
If InStr(varTempVariable, "Store:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
End Select
Wend
' ------------------------------------------------------
' Subtract the three header entries.
' ------------------------------------------------------
lngNumOfSalesEntries = (lngNumOfSalesEntries - 3)
If lngNumOfSalesEntries <= 0 Then
GoTo ImportSalesDataFile_End
End If
Close intHandle
ReDim SlsData(1 To lngNumOfSalesEntries)
' ------------------------------------------------------
' Set file handle to first available
' file number from os...
' ------------------------------------------------------
intHandle = FreeFile
' ------------------------------------------------------
' Re-open requested sales data file.
' ------------------------------------------------------
Open strFilename For _
Input Access Read _
As intHandle
' ------------------------------------------------------
' Use string manipulation to import the
' reset last reset date/time
' 13th+ characters in "Last Reset: Jul-7-1996 11:00:00"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
strLastResetDateTime = Mid$(varTempVariable, 13, Len(varTempVariable))
' ------------------------------------------------------
' Use string manipulation to import the
' reset current reset date/time
' 16th+ characters in "Current Reset: Jul-7-1996 11:00:00"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
strCurrResetDateTime = Mid$(varTempVariable, 16, Len(varTempVariable))
' ------------------------------------------------------
' Use string manipulation to import the
' store number from the file, for example, "Store: 114"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
intStoreId = CInt(Mid$(varTempVariable, 8, Len(varTempVariable)))
' ------------------------------------------------------
' Enumerate through the values and post to the custom
' sales table that is created each time...
' ------------------------------------------------------
For i = 1 To UBound(SlsData)
' ------------------------------------------------------
' Import sales id number.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Id = varTempVariable
' ------------------------------------------------------
' Import sales name.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).SlsName = varTempVariable
' ------------------------------------------------------
' Import sales quantity.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Qty = varTempVariable
' ------------------------------------------------------
' Import sales amount.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Amt = varTempVariable
Next i
' ------------------------------------------------------
' Create sales table using current reset date and
' time to uniquely name the table.
' ------------------------------------------------------
If CreateNewSlsDataTbl(CStr(strCurrResetDateTime)) Then
Set db = CurrentDb
Set rs = db.OpenRecordset(CStr(strCurrResetDateTime))
For i = 1 To UBound(SlsData)
rs.AddNew
rs("StoreId") = intStoreId
rs("CurrResetDateTime") = strCurrResetDateTime
rs("Id") = SlsData(i).Id
rs("Id") = SlsData(i).Id
rs("SlsName") = SlsData(i).SlsName
rs("Qty") = SlsData(i).Qty
rs("Amt") = SlsData(i).Amt
rs.UPDATE
Next i
End If
rs.Close
ImportSalesDataFile = True
ImportSalesDataFile_End:
Close intHandle
Exit Function
ImportSalesDataFile_Err:
MsgBox Err.Description, vbCritical
Resume ImportSalesDataFile_End
End Function
Breakdown of the specific features of the routine.
Figure 2.1. Import array structure
‘ Declarations section of the module.
Option Compare Database
Option Explicit
' ------------------------------------------------------
' Create user-defined Type array to
' hold the sales values.
' ------------------------------------------------------
Type SlsData
Id As Long
SlsName As String
Qty As Long
Amt As Currency
End Type
Dim SlsData() As SlsData
The user-defined Type array is designed to hold the data in the individual fields in the import file. By processing the data into an array structure first, you gain the benefit of data validation before posting the data to the database table. The array is dimensioned as a Dynamic array to use in the import function.
Figure 2.2. Array dimensioning code
' ------------------------------------------------------
' Set file handle to first available
' file number from os...
' ------------------------------------------------------
intHandle = FreeFile
' ------------------------------------------------------
' Open requested sales data file.
' ------------------------------------------------------
Open strFilename For _
Input Access Read _
As intHandle
' ------------------------------------------------------
' Enumerate the number of sales entries for the posting
' array used to hold the sales data.
' ------------------------------------------------------
While Not EOF(intHandle)
Line Input #intHandle, varTempVariable
' ------------------------------------------------------
' Count the sale lines in the file.
' ------------------------------------------------------
lngNumOfSalesEntries = (lngNumOfSalesEntries + 1)
' ------------------------------------------------------
' File validation.
' ------------------------------------------------------
Select Case lngNumOfSalesEntries
Case 1
If InStr(varTempVariable, "Last Reset:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
Case 2
If InStr(varTempVariable, "Current Reset:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
Case 3
If InStr(varTempVariable, "Store:") = False Then
MsgBox "The requested file is not a proper Sales Data file!", _
vbCritical, _
"Sales Data Import"
GoTo ImportSalesDataFile_End
End If
End Select
Wend
' ------------------------------------------------------
' Subtract the three header entries.
' ------------------------------------------------------
lngNumOfSalesEntries = (lngNumOfSalesEntries - 3)
If lngNumOfSalesEntries <= 0 Then
GoTo ImportSalesDataFile_End
End If
Close intHandle
ReDim SlsData(1 To lngNumOfSalesEntries)
The code in Figure 2.2 shows how to enumerate the actual lines of data in the file. The three lines at the start of the file are header information and the remaining lines that follow contain the data. By loading each line and counting the occurrences in the file, you can determine the number of data elements the array will need to hold. You could also use the ReDim Preserve method to dimension the elements as they are loaded. The more verbose version is included here for clarity of concepts.
Figure 2.3. Text import code
' ------------------------------------------------------
' Set file handle to first available
' file number from os...
' ------------------------------------------------------
intHandle = FreeFile
' ------------------------------------------------------
' Re-open requested sales data file
' ------------------------------------------------------
Open strFilename For _
Input Access Read _
As intHandle
' ------------------------------------------------------
' Use string manipulation to import the
' reset last reset date/time
' 13th+ characters in "Last Reset: Jul-7-1996 11:00:00"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
strLastResetDateTime = Mid$(varTempVariable, 13, Len(varTempVariable))
' ------------------------------------------------------
' Use string manipulation to import the
' reset current reset date/time
' 16th+ characters in "Current Reset: Jul-7-1996 11:00:00"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
strCurrResetDateTime = Mid$(varTempVariable, 16, Len(varTempVariable))
' ------------------------------------------------------
' Use string manipulation to import the
' store number from the file i.e. "Store: 114"
' ------------------------------------------------------
Line Input #intHandle, varTempVariable
intStoreId = CInt(Mid$(varTempVariable, 8, Len(varTempVariable)))
' ------------------------------------------------------
' Enumerate through the values and post to our custom
' sales table we create each time...
' ------------------------------------------------------
For i = 1 To UBound(SlsData)
' ------------------------------------------------------
' Import sales id number.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Id = varTempVariable
' ------------------------------------------------------
' Import sales name.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).SlsName = varTempVariable
' ------------------------------------------------------
' Import sales quantity.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Qty = varTempVariable
' ------------------------------------------------------
' Import sales amount.
' ------------------------------------------------------
Input #intHandle, varTempVariable
SlsData(i).Amt = varTempVariable
Next i
The next code sample demonstrates how to actually retrieve the data from the file and to store it into the temporary variables and the SlsData array structure. Note how this code uses a reusable Variant data type variable to hold the value before assignment. This technique is useful in situations where you may actually want to validate the data type in the imported value.
For example, you could ensure that the value imported for the sales “Id” field is actually an Integer data type with the code modification show in Figure 2.4.
Figure 2.4. Data type validation example
' ------------------------------------------------------
' Import sales id number.
' ------------------------------------------------------
Input #intHandle, varTempVariable
If VarType(varTempVariable) = vbInteger Then
‘ ok
SlsData(i).Id = varTempVariable
Else
MsgBox “[Id] field import is not a required Integer data type!”
End If
The above examples provide you with the basics to create a custom import routine. The next sections examine the table creation process and how to put it all together.
The code example in Figure 3 shows how to use data access objects (DAO) to create a specific table to store the imported data that you have in the temporary variables. The code is standard flavor DAO programming and the table name is actually passed from the import routine and reflects the date and time of the current reset of the POS.
Figure 3. Table creation example
Function CreateNewSlsDataTbl(strTblName As String) As Boolean
On Local Error GoTo CreateNewSlsDataTbl_Err
Dim db As DATABASE
Dim tdf As New TableDef
Dim fld As New Field
Dim idx As New Index
Dim fldName As String, fldAttrib As Integer, fldSize As Integer
Dim idxName As String, idxFields As String
Dim idxPrimary As Integer, idxUnique As Integer
Dim i As Integer, Msg As String
Set db = CurrentDb()
' ------------------------------------------------------
' Create table.
' ------------------------------------------------------
With tdf
.Name = strTblName
' ------------------------------------------------------
' Create table fields.
' ------------------------------------------------------
For i = 1 To 6
' ------------------------------------------------------
' Set field name list.
' ------------------------------------------------------
fldName = Choose(i, "StoreId", "CurrResetDateTime", _
"Id", "SlsName", _
"Qty", "Amt")
' ------------------------------------------------------
' Set field attributes list.
' ------------------------------------------------------
fldAttrib = Choose(i, dbInteger, dbDate, _
dbLong, dbText, _
dbLong, dbCurrency)
' ------------------------------------------------------
' Set field size list.
' ------------------------------------------------------
fldSize = Choose(i, dbInteger, dbDate, _
dbLong, dbText, _
dbLong, dbCurrency)
Set fld = tdf.CreateField(fldName, fldAttrib, fldSize)
' ------------------------------------------------------
' Set default value status.
' ------------------------------------------------------
Select Case i
Case 1
fld.DefaultValue = "0"
Case 3
fld.DefaultValue = "0"
Case 5
fld.DefaultValue = "0"
Case 6
fld.DefaultValue = "0"
End Select
tdf.Fields.Append fld
Next i
End With
' ------------------------------------------------------
' Append new table and refresh collection.
' ------------------------------------------------------
db.TableDefs.Append tdf
db.TableDefs.Refresh
CreateNewSlsDataTbl = True
CreateNewSlsDataTbl_End:
Exit Function
CreateNewSlsDataTbl_Err:
MsgBox Err.Description, vbInformation
Resume CreateNewSlsDataTbl_End
End Function
Once you have tested the basic routines used to import and post to your database table, you need to provide an interface to help the user navigate the routines.
A wizard that implements the following would accomplish this:
Page 1: Uses the Common Dialog Control for browsing and selecting the files.
Page 2: Confirms the last and current reset date and times.
Page 3: Finishes by importing and posting the data to the table.
Page 4: Confirms successful completion of import.
The textwiz.mda included with the sample download accompanying this article implements all of the concepts outlined in this document.