If you are new to ODBC data provider, please try Understanding ODBC .NET Data Provider
To test text database connectivity, I've exported the Employees table of the Northwind database using the File Export option of Access 2000. In this section, I'll export a table to a text file, and I'll show you how to use it using the ODBC data adapter.
Exporting an Access Table to a Text File
You can export an Access database table to a text file using the Export option. As you can see from Figure 1, I select the Employees table and the selected the Export option from the File menu.
Note: You can also select the Export option by right-clicking on the table.
Figure 1. Exporting the Employees table of Northwind.mdb
The next dialog box lets you pick a path and filename you want to export. There are many export options are available. As you can see from Figure 2, I selected the Text Files option, left the exported filename as Employees, and saved the file to the C:\ root directory.
Figure 2. Selecting a path of exported file
Now, the Export Text Wizard lets you define the format of the text file. A dialog box lets you select either delimited or fixed width (see Figure 3).
Figure 3. Export Text Wizard options.
You can also select the Advanced option to set more options (see Figure 4).
Figure 4. The Advanced option of Export Text Wizard
The next screen lets you pick the delimiter including comma, tab, semicolon, space, and others. I left the Comma option checked (see Figure 5).
Figure 5. Delimiter options of Export Text Wizard
I also checked the Include Field Names on First Row check box. This option adds the first row of the text file as field names.
The last page asks you the filename (see Figure 6).
Figure 6. Filename page of the Export Text Wizard
Now click the Finish button. When the wizard done exporting, you'll see a message saying the export is finished. Click OK and close Access.
Now if you view C:\Employees.txt (see Figure 7).
Figure 7. 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. Either you can create a DSN from the ODBC Data Source Administrator or you access the text file directly in your application. To create a data source for a text file, you 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 8).
Figure 8. 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 Options to see more options (see Figure 9).
Figure 9. 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 10).
Figure 10. Selection of directory and file types
You can even define different formats by using the Define Format button. As you can see from Figure 11, 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 - 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. Defining a text file format and column settings
After creating a DSN, you can use DSN as a connection source for your connection:
OdbcConnection conn = 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
string ConnectionString = @"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:
OdbcConnection conn = 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 1 on the Form_Load event.
Listing 1. Accessing the TextDB.txt file
private void Form1_Load(object sender, System.EventArgs e)
{
// Connection string for a Text File
string ConnectionString = @"Driver={Microsoft Text Driver (*.txt; .csv)};DBQ=c:\";
// Query the Employees.txt file as a table
OdbcConnection conn = new OdbcConnection(ConnectionString);
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter("Select * FROM Employees.txt", conn);
DataSet ds = new DataSet();
da.Fill(ds, "TextDB");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close the connection
conn.Close();
}
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.
Next articles in this series are working with Excel, Oracle 8i, Oracle 9i, and Sybase databases.
aksPosted Jun 12, 2015, 6:47 AM
Is it possible to create new table using Microsoft Text Driver? I am getting the following error "Cannot modify the design of table 'OFFICES'. It is in a read-only database."
kishor daraPosted Dec 3, 2007, 5:16 AM
Microsoft ODBC TExt driver is not able to read the CSV file in Spainsh OS.
kishor daraPosted Dec 3, 2007, 5:11 AM
Microsoft ODBC TExt driver is not able to read the CSV file in Spainsh OS.
amit gautameditedPosted May 9, 2007, 1:38 AMEdited May 9, 2007, 1:43 AM
This is the code for read text file and bind with grid. there is no error but it is not working properly. pls give me solution. using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.IO; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { // DataSet ds; //DataTable table; SqlConnection con; protected void Page_Load(object sender, EventArgs e) { con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); if (!IsPostBack) { string OpenPath, contents; int tabSize = 4; string[] arInfo; string line; //Create new DataTable. DataTable table = CreateTable(); DataRow row; try { OpenPath = Server.MapPath(".") + @"\arvind.txt"; string FILENAME = OpenPath; //Get a StreamReader class that can be used to read the file StreamReader objStreamReader; objStreamReader = File.OpenText(FILENAME); while ((line = objStreamReader.ReadLine()) != null) { contents = line.Replace(("").PadRight(tabSize, ' '), "\t"); // define which character is seperating fields char[] textdelimiter = { ']' }; arInfo = contents.Split(textdelimiter); for (int i = 0; i <= arInfo.Length; i++) { row = table.NewRow(); if (i < arInfo.Length) row["Type"] = arInfo[i].ToString().Replace("[", " "); if (i + 1 < arInfo.Length) row["Source"] = arInfo[i + 1].ToString().Replace("[", " "); if (i + 2 < arInfo.Length) row["Time"] = arInfo[i + 2].ToString().Substring(1); if (i + 3 < arInfo.Length) { row["Description"] = arInfo[i + 3].ToString().Replace("[", " "); table.Rows.Add(row); } i = i + 2; } } objStreamReader.Close(); // Set to DataGrid.DataSource property to the table. GridView1.DataSource = table; GridView1.DataBind(); } catch (Exception ex) { lblError.Text = ex.Message; } } } private DataTable CreateTable() { try { DataTable table = new DataTable(); // Declare DataColumn and DataRow variables. DataColumn column; // Create new DataColumn, set DataType, ColumnName // and add to DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Type"; table.Columns.Add(column); // Create second column. column = new DataColumn(); column.DataType = Type.GetType("System.String"); column.ColumnName = "Time"; table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Source"; table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Description"; table.Columns.Add(column); return table; } catch (Exception ex) { throw new Exception(ex.Message); } }
hassan mohamedPosted Apr 19, 2007, 12:34 PM
hi error message: the data source did not contain any properties or attributes from which to generate cols...
KeithPosted Apr 10, 2006, 4:07 PM
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\;Extensions=asc,csv,tab,txt;" That's all.