Text driver not honoring FMT param

[pearsons_11114]

Trying to read a delimited text file using OLEDB text driver. It appears to
completely ignore the FMT param in the connection string. As long as the file
is comma delimited, it works fine, regardless of what delimiter I specify in
the FMT param. Any other delimiter in the file and it will successfully read
only the first column but return no errors. (BTW, you can put any param you
want in the connection string and it just ignores it. Makes it a bit hard to
debug). I've tested the following sample code in both VS2005 and 2008. Sample
data follows. Thanks!

string hdr = "No";
string fmt = "Delimited( )";
string folderPath = SpecialDirectories.CurrentUserApplicationData;
string fileName = "temp.tmp";

string connStr =
string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended
Properties='text;HDR={1};FMT={2}'",
folderPath, hdr, fmt);
string textFileSql = string.Format("SELECT * FROM [{0}]", fileName);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
OleDbDataAdapter inputAdapter =
new OleDbDataAdapter(textFileSql, conn);
DataTable inputTable = new DataTable();
inputAdapter.Fill(inputTable);
Console.WriteLine(inputTable.Columns.Count);
Console.WriteLine(inputTable.Rows.Count);
}

"717600438" "blat"
"717900172" "blat"
"718000182" " "
"718000238" " "

[pearsons_11114]

[Colbert Zhou [MSFT]]

Hello,

In the connection string, we can only specify "Fixed" or "Delimited" for
the FMT property. If we want to control over the delimiter type used in the
text file, we need use the Schema.INI file.

When the Text driver is used, the format of the text file is determined by
using a schema information file. The schema information file is always
named Schema.ini and always kept in the same directory as the text data
source. The schema information file provides the IISAM with information
about the general format of the file, the column name and data type
information, and several other data characteristics. A Schema.ini file is
always required for accessing fixed-length data.

More detailed information, please read the following articles,
http://support.microsoft.com/kb/326548
http://msdn.microsoft.com/en-us/library/ms709353.aspx

Please let me know if you have any future questions or concerns on this.
Have a nice weekend!

Best regards,
Colbert