Symptoms
The ImportText.exe sample demonstrates various ways to import text files into a Microsoft Access database. There are many ways to import text data to an Access database, and typically the best option is determined by the task requirements.
ADORDODAOFilesysAutomation The sample application attached details the above coding options.
Resolution
The following files are available for download from the Microsoft Download Center:
TextImport.exe(http://download.microsoft.com/download/vb60pro/sample/1/w9xnt4/en-us/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?(http://support.microsoft.com/kb/119591/EN-US/) 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.
Collapse this tableExpand this table
FileNameSizeImportText.vbp1,464ImportText.vbw56Sample.out3,346Sample.txt3,346Sample_Header.txt3,708Schema.ini422Schema_Header.ini420TextImport.frm28,678TextImport.frx84TextImport.mdb108,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 librariesThe RDO sample: RDO libraries + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driverThe ADO (the default example): ADO libraries (OLEDB + MSDASQL) + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driverThe 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 ErrHandlerlblAction.Caption = “DAO Import…”Dim daoDB As DAO.DatabaseDim strSQL As StringIf chkCreateTbl.Value = 1 ThenDBEngine.IniPath = App.Path & “\Schema_Header.ini”ElseDBEngine.IniPath = App.Path & “\Schema.ini”End IfSet 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.TextdaoDB.Execute strSQLElse’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.TextdaoDB.Execute strSQLEnd IfGoTo ExitSubErrHandler:lblAction.Caption = “DAO Import – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”daoDB.CloseSet daoDB = NothingEnd 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 ErrHandlerlblAction.Caption = “FileSys Import…”Dim daoDB As DAO.DatabaseDim daoRs As DAO.RecordsetDim fs As FileSystemObjectDim ts As TextStreamDim inLine As VariantDim strSQL As StringDim i As IntegerIf 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 strSQLSet daoDB = NothingSet daoDB = OpenDatabase(App.Path & “\” & txtDatabase.Text, False, False)ElseDBEngine.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, dbFailOnErrorEnd IfstrSQL = “SELECT * FROM [" & txtTable.Text & "] WHERE 1=0″Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)Set fs = New FileSystemObjectSet ts = fs.OpenTextFile(App.Path & “\” & txtFile.Text, ForReading, False, TristateUseDefault)’This skips the column header.If chkColHeader.Value = 1 TheninLine = Split(ts.ReadLine, “,”)End IfWhile Not ts.AtEndOfStreaminLine = Split(ts.ReadLine, “,”)daoRs.AddNewFor i = 0 To UBound(inLine) – 1daoRs.Fields(i).Value = Left(inLine(i), daoRs.Fields(i).Size)Next idaoRs.UpdateWendGoTo ExitSubErrHandler:lblAction.Caption = “FileSys Import – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”If Not ts Is Nothing Then ts.CloseIf Not daoRs Is Nothing Then daoRs.ClosedaoDB.CloseSet daoRs = NothingSet daoDB = NothingSet ts = NothingSet fs = NothingEnd 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 ErrHandlerlblAction.Caption = “Access Automation…”Dim AccessApp As access.ApplicationDim strDB As StringstrDB = App.Path & “\” & txtDatabase.TextSet AccessApp = New access.ApplicationAccessApp.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.TextAccessApp.CloseCurrentDatabaseGoTo ExitSubErrHandler:lblAction.Caption = “Access Automation – Error.”MsgBox “Error: ” & Err.Number & vbCrLf & Err.DescriptionExitSub:lblAction.Caption = “Complete…”appAccess.QuitSet appAccess = NothingEnd Sub For additional details and code refer to the sample application TextImport.exe.
