Creating Custom Text Import Wizards

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.

Overview

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.

Creating Text Import Code

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:

  1. Three header lines precede the data.

  2. The three header lines contain three unique pieces of data: the “Last Reset”, the “Current Reset” and the “Store Number.”

  3. The data consists of four fields: an “Id” number, a “Sales Item Name”, a “Quantity Sold” value, and a “Total Sales Dollar Amount.”

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 Import Functions

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 Table Creation Function

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

Putting the Functions Together

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.