C# Open DBF file

Question

I'm having a problem opening a DBF file - I need to open it, read everything and process it. I tried several solutions (ODBC/OLEDB), several connection string, but nothing worked so far.

The problem is, when I execute the SQL command to get everything from the file, nothing gets returned - no rows. What's even more odd, the content of the DBF file being opened get deleted.

See the code I have:

public override bool OpenFile(string fileName, string subFileName = "")
{
    OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(fileName) + ";Extended Properties=dBASE IV;User ID=;Password=;");
    try
    {
        if (con.State == ConnectionState.Closed) { con.Open(); }
        OleDbDataAdapter da = new OleDbDataAdapter("select * from " + Path.GetFileName(fileName), con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        int i = ds.Tables[0].Rows.Count;
        return true;
    }
    catch
    {
        return false;
    }             
}

I debugged the code and watched the file being opend in Windows Explorer. When it reached this line:

da.Fill(ds);

the size of the file dropped to only a few Bytes (from hundreds of kB).

My next thought was to make the DBF file read only. That however cause an "unexpected exception from an external driver".

So my question is - what the heck? I'm sure the file is not corrupt, it is a direct export from some DB. (No, I do not have access to that DB). I can also open that file in MS Office no problem.

^{I cannot share the DBF file - it contains confidential data.}

Oleviaolfaction

Did You try to issue DbCommand (SELECT * FROM) instead of using data adapter? Just to check if it works. – Traverse

Silly question, but does the Jet driver understand DBF files? – Tushy

Loose the adapter and simplify the SQL. Currently in includes .DBF. Also you can get properties of an opened connection. – Fibrovascular

I tried the DbCommand version, same result. I also exluded the extension from the file name, also the same result. I guess I must be using wrong driver... – Ordnance

Answer 1

Two things... just because its a .DBF file extension might night mean its a Dbase IV file. It might actually be that of Visual Foxpro. That said, I would look into downloading and installing the Visual Foxpro OleDB driver from Microsoft download. Next, the OleDbConnection is pointing to the path that has the actual tables (you already have that).

The query itself, shouldn't care about the extension, so I would change your call to get just then name via "Path.GetFileNameWithoutExtension"

It might be a combination of the two.

Connection string for VFP provider

"Provider=VFPOLEDB.1;Data Source=" + FullPathToDatabase

Augean

I installed the driver you linked to, but I get "driver not registered on your PC"... What else do I have to do beside installing it? – Ordnance

@TomášBezouška, you shouldn't have to do anything else. What type of machine are you running on? Is it 64 bit? If 64 bit, that's your problem. The older DBF drivers were never upgraded for 64 bit and known not to work. Did you install with administrator permissions so it properly registered with registry? – Augean

Im running on Windows 7, 32 bit. I also tried a version without the ".1", but no success. I even tried rebooting, nothing. Where in the system can I see if it registered successfully? – Ordnance

Answer 2

This is not the exact answer but it will help you to find the issue.

Try to give an inline connection string and select query to make sure problem is not with building those. Catch the exception and check the details of it.

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=;Password=;"); // give your path directly 
try
{
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter("select * from tblCustomers.DBF", con); // update this query with your table name 
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    int i = ds.Tables[0].Rows.Count;
    return true;
}
catch(Exception e)
{
    var error = e.ToString();
    // check error details 
    return false;
}

Houghton

Only if there is an exception, not indicated in the question. – Fibrovascular

it doesn't throw any exceptions. Only when I mark the file read only, as indicated in my original post – Ordnance