Accessing a Text File using ADO.NET in Vb.NET
In this article I will explain about Accessing a Text File using ADO.NET.
You can access a text file using the ODBC data
provider. There are two ways to access text files. Either you can create a DSN from the ODBCDataSource Administrator or you access the text file directly in your application. To create a data source for a text file, you go to the ODBC Source Admin, click the New button (or the Add button if you're using Windows XP), and select the Microsoft Text Driver (*.txt,*.csv) option (see Figure 11-29).
Figure 11-29: Selecting the Microsoft Text Driver (*.txt, *.csv) option
You define the DSN name and description in the ODBC Text Setup dialog box. Uncheck the Use Current Directory option to enable the Select Directory button and click on Option to see more options (see Figure 11-30).
Figure 11-30: Setting the DSN name and description
After that you select your DSN name. You can select any directory you want to use. An entire text file is used as a database table (see Figure 11-31).
Figure 11-31: Selecting directory and file types
You can even define different formats by using the Define Format button. As you can see from figure 11-32, all files are treated as a database table. From the Format drop-down box, you can select the type of format you want, such as comma- delimited or tab-delimited. The Guess button guesses the column names for you. If it doesn't find a proper format file, it creates F1 . . . Fn columns for you. You can also add, modify, and remove columns and their types.
Figure 11-32: Defining a text file format and column settings
After creating a DSN, you can use DSN as a connection source for your connection:
Dim conn As New OdbcConnection("DSN=TxtDSN")
Another way to access text files is directly using the text ODBC driver in the connection string. For example, ConnectionString in the following code defines a connection having the Microsoft Text Driver and source directory as C:\.
' Connection string for a Text file
Dim ConnectionString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\"
Every text or .csv file in the C:\directory will be treated as a database table, which you pass in your SQL string:
Dim connAs OdbcConnection = New OdbcConnection(ConnectionString)
OdbcDataAdapter da =New OdbcDataAdapter
("Select * FROM Employees.txt", conn)
To test this code, I created a Windows application, dropped a DataGrid control on the form, and used the code shown in listing 11-3 on the Form_load event.
Listing 11-3: Accessing the TextDB.txt file
Private Sub Form1_load(ByVal senderAs Object)
' connection string for a text file
string ConnectionString =
"Driver={Microsoft Text Driver (*.txt, *.csv)};DBQ=c:\"
' Query the Employees.txt file as a table
Dim conn As OdbcConnection = New OdbcConnection(ConnectionString)
conn.Open()
OdbcDataAdapter(da = New OdbcDataAdapter)
("Select * FROM Employees.txt", conn)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "TextDB"
dataGrid1.DataSource = ds.DefaultViewManager
' Close the connection
conn.Close()
End Sub
Note: Don't forget to add a reference to the Microsoft.Data.Odbc namespace.
Now compile and run the application, and you should see data in the DataGrid
Conclusion
Hope this article would have helped you in understanding Accessing a Text File using ADO.NET. See my other articles on the website on ADO.NET.