How can I read a DBF file with incorrectly defined column data types using ADO.NET?
I have several third party generated DBF files that I need to query. I am having problems because all column types were defined as characters, but the data in some of those fields actually contains binary data. If I try to read these fields using the OleDbDataReader as nothing but a string or character array, I get an InvalidCastException, but I need to be able to read them as a binary value, or at least convert them after reading them, Columns are actually containing text are returned as expected.
For example, the very first column is defined as a 2-byte character field, but the field contains a 16-bit integer.
I read the following test code to read the first column and convert it to the appropriate datatype, but the value doesn't come out correctly.
The first row of the database has a value of 17365 (0x43D5) in the first column. By executing the following code, I get 17215 (0x433F). I'm sure it has to do with using ASCII encoding to get bytes from the string returned by the data reader, but I'm not sure of another way to get the value in the format I need, write my own DBF reader and bypass ADO.NET altogether which I don't want to do it unless I absolutely have to. Any help would be greatly appreciated.
byte[] c0;
int i0;
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ASTM;Extended Properties=dBASE III;User ID=Admin;Password=;";
using (OleDbConnection c = new OleDbConnection(con))
{
c.Open();
OleDbCommand cmd = c.CreateCommand();
cmd.CommandText = "SELECT * FROM astm2007";
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
c0 = Encoding.ASCII.GetBytes(dr.GetValue(0).ToString());
i0 = BitConverter.ToInt16(c0, 0);
}
dr.Dispose();
}
a source to share
I'm sure you have specified the ASCII character conversions correctly. I searched a bit for supported scalar functions for the Jet engine but couldn't find them ... Or rather, I found scalar functions but not syntax. The function CONVERT
is probably what you want. Sort of:
SELECT CONVERT(twobytefield, SQL_BINARY) from astm2007
Then you can call dr.GetBytes()
to read the raw data. However, I was unable to build the operator using this feature that Jet loved.
If you cannot get the transformation to work, another option is to use the Advantage.NET Data Provider . Or an OLE DB provider (but a .NET data provider might be better if you're using C #). This provider reads DBF files and supports the scalar CONVERT function. It has a free local engine.
Since you mention you're going to try it, and since I've tested it to make sure I'm not lying, here's the snippet I used:
AdsConnection conn = new AdsConnection(
@"data source=c:\path;chartype=ansi;ServerType=local;TableType=cdx;" );
conn.Open();
AdsCommand cmd = conn.CreateCommand();
cmd.CommandText = "select cast(somefield as sql_binary) from sometable";
cmd.CommandType = CommandType.Text;
AdsExtendedReader rdr = cmd.ExecuteExtendedReader();
rdr.Read();
byte[] c0 = rdr.GetBytes( 0 );
int i0 = BitConverter.ToInt16( c0, 0 );
Console.WriteLine( "val = {0}", i0 );
a source to share
What you can run is actually a memo based field ... These are the columns that actually have the source text in the ANOTHER file (usually .DBT (dBASE) or .FPT (FoxPro). This is the pointer offset in text content, which is free-form length and is written in blocks, but the pointer is stored in 4 bytes.
If you have access to a .dbf viewer and can see a few of it initially, this will probably help you.
a source to share