Reading Data from Text Files

Just as you can write data to text files, you can use legacy VB statements, FileSystemObject code, or ADO code to read data from text files. The Import Data from Text File form (frmTextlmport) has a command button for selecting a text file to import (Figure 9.13 shows the form, and Figure 9.14 shows the File Picker dialog opened by this button), and an option group with a choice of ADO, FSO (FileSystemObject), and VB-type text imports; the imported data is written to the large Imported Text textbox when you click the "Load Data From File" button. A "Clear Imported Data" button clears the Imported Text textbox so you can start over.

A form with a choice of loading data from a text file using three different methods.

Pullman Railroad Cars Blueprints

Selecting a text file for importing data.

Selecting a text file for importing data.

The cmdLoadData_Click event procedure that reads data from a text file is simpler than the code that writes data, because two of the three methods (in their own way) can import all the data from a text file without needing to process each line separately. The cmdLoadData_Click procedure starts by setting a reference to the textbox that holds the name of the selected text file, and checks that there is a file name in the box. If there is a file name, it is saved to a variable, and then a Select Case statement is set up to process the import separately depending on whether the user selects ADO, FSO, or VB in an option group:

Private Sub cmdLoadData_Click()

On Error GoTo ErrorHandler

Dim fso As Scripting.FileSystemObject

Dim strText As String

Dim strFile As String

Dim intTextType As Integer

Dim strTitle As String

Dim strPrompt As String

Dim txt As Scripting.TextStream

Dim stm As ADODB.Stream

Dim txtData As Access.TextBox

Dim strTextFile As String

Dim strData As String

Dim strLine As String

Set txtData = Me![txtSelectedTextFile] strTextFile = Nz(txtData.Value) If strTextFile = "" Then strTitle = "No text file selected"

strPrompt = "Please select a text file"

MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, title:=strTitle GoTo ErrorHandlerExit Else

Debug.Print "Text file: " & strTextFile End If intTextType = Nz(Me![fraTextType].Value, 2) Select Case intTextType Case 1

Set stm = New ADODB.Stream

With stm

.Charset = "ASCII"


.LoadFromFile strTextFile .Position = 0

strData = .ReadText(adReadAll) End With

Close Stream object.


Case 2

Set fso = CreateObject("Scripting.FileSystemObject") Set txt = fso.OpenTextFile(FileName:=strTextFile, _ IOMode:=ForReading)

Read all data from file.

strData = txt.ReadAll

Close file.

txt.Close Case 3

Open text file for reading data.

Open strTextFile For Input As #2 Do While Not EOF(2)

Save data from a line in the text file to a variable.

Input #2, strLine strData = strData & IIf(strData <> "", _ vbCrLf, "") & strLine


Close file.

Close #2 End Select

Write data to text box on form.

Me![txtImportedText].Value = strData

ErrorHandlerExit: Exit Sub


& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The next sections describe the specific techniques used in the code for the ADO, FSO, and VB methods for loading data from a selected text file.

0 0

Post a comment