Accessing Text File Databases

To test text database connectivity, you can export the Employees table of the Northwind database using the File Export option of Access 2000. In the following section, you'll export a table to a text file, and you'll see how to use it with the ODBC DataAdapter.

Exporting an Access Table to a Text File

You can export an Access database table to a text file using the Export option. In other words, select a table and choose File Export. Another option is to rightclick a table and choose the Export option from the menu.

After clicking the Export option, the dialog box that appears lets you pick a path and the file you want to export. There are many export formats available. For this example, select the Text Files option.

Next, the Export Text Wizard lets you define the format of the text file—either delimited or fixed width (see Figure 10-2).

click to expand
Figure 10-2: Export Text Wizard options

You can also click the Advanced button to set more options, such as specifying the delimiters and other options (see Figure 10-3).

click to expand
Figure 10-3: The advanced options of the Export Text Wizard

The next screen lets you pick the delimiter, including a comma, tab, semicolon, space, and others. Figure 10-4 shows the Comma option checked.

click to expand
Figure 10-4: Delimiter options of the Export Text Wizard

Figure 10-4 also shows the Include Field Names on First Row check box selected. This option adds the first row of the text file as field names.

The last page of the wizard asks you for the filename of where you want to export the data. Enter the filename C:\Employees.txt.

Finally, click the Finish button. When the wizard is done exporting, you'll see a message saying the export is finished. Click OK and close Access.

If you view C:\Employees.txt, it looks like Figure 10-5.

click to expand
Figure 10-5: Exported Employees.txt file from Nothwind.mdb

Accessing a Text File

You can access a text file using the ODBC data provider. There are two ways to access text files: You can create a DSN from the ODBC Data Source Administrator, or you can access the text file directly from your application. To create a data source for a text file, go to ODBC Data 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 10-6).

click to expand
Figure 10-6: Selecting the Microsoft Text Driver (*.txt, *.csv) option

You define the DSN name and description in the ODBC Text Setup dialog box. If you don't want to use the current directory, uncheck the Use Current Directory option to enable the Select Directory button and click Options to see more options (see Figure 10-7).

click to expand
Figure 10-7: Setting the DSN name and description

Now you can select any directory you want to use. An entire text file is used as a database table (see Figure 10-8).

click to expand
Figure 10-8: Selecting the directory and file types

You can even define different formats by using the Define Format button in the ODBC Text Setup dialog box. As you can see from Figure 10-9, 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 assigns 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.

click to expand
Figure 10-9: Defining a text file format and column settings

After creating a DSN, you can use it as a connection source for your connection:

 Dim conn As OdbcConnection = 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 with the Microsoft Text Driver and the source directory c:\:

 Dim connectionString As String = _ "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\\" 

Every .txt or .csv file in the C:\ directory will be treated as a database table, which you pass in your SQL string:

 OdbcConnection conn = new OdbcConnection(ConnectionString); OdbcDataAdapter da = new OdbcDataAdapter ("Select * FROM Employees.txt", conn 

To test this code, create a Windows application, drop a DataGrid control onto the form, and use the code in Listing 10-3 for the Form_Load event.

Listing 10-3: Accessing the TextDB.txt File

start example
 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     ' Connection string for a Text File     Dim connectionString As String = _     "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()     ' Create a data adapter and fill a DataSet     Dim da As OdbcDataAdapter = New OdbcDataAdapter _     ("Select * FROM Employees.txt", conn)     Dim ds As DataSet = New DataSet()     da.Fill(ds, "TextDB")     ' Bind the DataSet to a DataGrid     DataGrid1.DataSource = ds.DefaultViewManager     ' Close the connection     conn.Close()     conn.Dispose()   End Sub 
end example


Don't forget to add a reference to the Microsoft.Data.Odbc namespace.

If you compile and run the application, you should see data in the DataGrid control.

Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214 © 2008-2017.
If you may any questions please contact us: