MORE INFORMATION
The
following files are available for download from the Microsoft Download
Center:
TextImport.exe For additional information about how
to download Microsoft Support files, click the following article number to view
the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most
current virus-detection software that was available on the date that the file
was posted. The file is stored on security-enhanced servers that help to
prevent any unauthorized changes to the file.
|
| ImportText.vbp | 1,464 |
| ImportText.vbw | 56 |
| Sample.out | 3,346 |
| Sample.txt | 3,346 |
| Sample_Header.txt | 3,708 |
| Schema.ini | 422 |
| Schema_Header.ini | 420 |
| TextImport.frm | 28,678 |
| TextImport.frx | 84 |
| TextImport.mdb | 108,544 |
All files should reside in the same folder. Run the
sample application ImportText.vbp and examine the different import/export
options. The sample TextImport.mdb is used and should reside in the application
path. The default sample text file is Sample.txt. A Sample_Header.txt file is
included and contains the column header for the text file. An alternate schema
file, Schema_Header.ini, may be used to demonstrate using the
ColNameHeader=True option in the schema file corresponding to the
Sample_Header.txt file.
Among the data import options demonstrated,
DAO is probably the most efficient (fewest layers) or with the smallest memory
footprint; especially if importing to an Access database.
Refer to
the following list for an overview of the libraries loaded for each data access
method.
- The FileSys objects sample: Scripting Runtime + DAO
libraries + Jet libraries
- The RDO sample: RDO libraries + ODBC libraries + ODBC Jet
library + Jet libraries + Text ISAM driver
- The ADO (the default example): ADO libraries (OLEDB +
MSDASQL) + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM
driver
- The Automation sample: MSOffice Runtime library. For the
DAO sample: DAO libraries + Jet libraries + Text ISAM driver
The following function is the DAO object sample in the
TextImport.vbp application. This code is used in the application when you click
the DAO radio button before importing. You can modify the DAO sample by adding
a recordset and a loop for data manipulation just as in the FileSys objects
example.
Sub DAOOpenTextFileImport()
On Error GoTo ErrHandler
lblAction.Caption = "DAO Import..."
Dim daoDB As DAO.Database
Dim strSQL As String
If chkCreateTbl.Value = 1 Then
DBEngine.IniPath = App.Path & "\Schema_Header.ini"
Else
DBEngine.IniPath = App.Path & "\Schema.ini"
End If
Set daoDB = OpenDatabase(App.Path, False, False, _
"Text;Database=" & App.Path & ";table=" & txtFile.Text)
If chkCreateTbl.Value = 1 Then
'Use this if you do not already have a table created in Access.
'Creates and appends the data in one step.
strSQL = "SELECT * INTO [" & txtTable.Text & "] IN '" & _
App.Path & "\" & txtDatabase.Text & " '"
strSQL = strSQL & "FROM " & txtFile.Text
daoDB.Execute strSQL
Else
'Delete data before importing - use if necessary.
strSQL = "DELETE FROM [" & txtTable.Text & "] IN '" & _
App.Path & "\" & txtDatabase.Text & "'"
daoDB.Execute strSQL
'Append data to Access table.
strSQL = "INSERT INTO [" & txtTable.Text & "] IN '" & _
App.Path & "\" & txtDatabase.Text & "'"
strSQL = strSQL & "SELECT * FROM " & txtFile.Text
daoDB.Execute strSQL
End If
GoTo ExitSub
ErrHandler:
lblAction.Caption = "DAO Import - Error."
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
ExitSub:
lblAction.Caption = "Complete..."
daoDB.Close
Set daoDB = Nothing
End Sub
The following function is the FileSys object sample in the
TextImport.vbp application. This code is used in the application when you
select the FileSys radio button before importing. Notice in the sample code
that to create the table layout in Access, based on the Schema_Header.ini file,
there is no need to loop through the header file and create the table manually
if you use the Text ISAM driver. Although, if you are using the Text ISAM
driver then there is no need to use the FileSystemObject (and that is part of
the point) unless you
must use the FileSystemObject to import, then use DAO and do it in one
as shown in the DAO sample code. Since you must use DAO anyway (to create the
recordset object) even if you are doing data manipulation on import, then use
DAO for the entire process since you already have it loaded in memory to create
the recordset.
Private Sub FileSysImport()
On Error GoTo ErrHandler
lblAction.Caption = "FileSys Import..."
Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim fs As FileSystemObject
Dim ts As TextStream
Dim inLine As Variant
Dim strSQL As String
Dim i As Integer
If chkCreateTbl.Value = 1 Then
'This is an eazy way to create the Table layout in Access based on the Schema_Header.ini file.
DBEngine.IniPath = App.Path & "\Schema_Header.ini"
Set daoDB = OpenDatabase(App.Path, False, False, "Text;Database=" & App.Path & ";table=" & txtFile.Text)
strSQL = "SELECT * INTO [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & " '"
strSQL = strSQL & "FROM " & txtFile.Text & " WHERE 1=0"
daoDB.Execute strSQL
Set daoDB = Nothing
Set daoDB = OpenDatabase(App.Path & "\" & txtDatabase.Text, False, False)
Else
DBEngine.IniPath = App.Path & "\Schema.ini"
Set daoDB = OpenDatabase(App.Path & "\" & txtDatabase.Text, False, False)
strSQL = "DELETE * FROM [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & "'"
daoDB.Execute strSQL, dbFailOnError
End If
strSQL = "SELECT * FROM [" & txtTable.Text & "] WHERE 1=0"
Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)
Set fs = New FileSystemObject
Set ts = fs.OpenTextFile(App.Path & "\" & txtFile.Text, ForReading, False, TristateUseDefault)
'This skips the column header.
If chkColHeader.Value = 1 Then
inLine = Split(ts.ReadLine, ",")
End If
While Not ts.AtEndOfStream
inLine = Split(ts.ReadLine, ",")
daoRs.AddNew
For i = 0 To UBound(inLine) - 1
daoRs.Fields(i).Value = Left(inLine(i), daoRs.Fields(i).Size)
Next i
daoRs.Update
Wend
GoTo ExitSub
ErrHandler:
lblAction.Caption = "FileSys Import - Error."
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
ExitSub:
lblAction.Caption = "Complete..."
If Not ts Is Nothing Then ts.Close
If Not daoRs Is Nothing Then daoRs.Close
daoDB.Close
Set daoRs = Nothing
Set daoDB = Nothing
Set ts = Nothing
Set fs = Nothing
End Sub
The simplest example is the Automation sample. A sample TextImport.mdb
is used and the example import/export specifications have been created in the
sample .mdb file: Sample and sample w/columns. You can find the specification
property setting on the
Properties tab of the
Tab control. To import with or without the column names in the first
row create another import/export specification and put the name of that
specification in the text box txtSpecName on the tab control. An example
specification is included in the sample .mdb file: Sample w/columns. To import
the text file with Access Automation you can simply execute the
DoCmd.TransferText method of the Access object.
Private Sub AccessAutomateImport()
'Assumes table already exists.
On Error GoTo ErrHandler
lblAction.Caption = "Access Automation..."
Dim AccessApp As access.Application
Dim strDB As String
strDB = App.Path & "\" & txtDatabase.Text
Set AccessApp = New access.Application
AccessApp.OpenCurrentDatabase strDB
'To Import with/without Column names in first row create another Import/Export Specification
'and put the name of that specification in the Text box 'txtSpecName' on the Tab Control.
'An example Specification is included in the sample MDB - 'Sample w/columns'.
AccessApp.DoCmd.TransferText acImportDelim, txtSpecName.Text, txtTable.Text, App.Path & "\" & txtFile.Text
AccessApp.CloseCurrentDatabase
GoTo ExitSub
ErrHandler:
lblAction.Caption = "Access Automation - Error."
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
ExitSub:
lblAction.Caption = "Complete..."
appAccess.Quit
Set appAccess = Nothing
End Sub
For additional details and code refer to the sample application
TextImport.exe.