11

I am trying to write a DBF file from scratch in my program. I want to create it, add some columns, and then add data to the columns X amount of times. My program will not need to read it in again, but other programs will.

I've looked around for a solution to this, but all seem to assume an existing DBF file, whereas I want to make a new one.

The purpose of this is to make the DBF part of an ESRI ShapeFile.

Does anyone know how to do this?

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Greg
  • 1,673
  • 4
  • 20
  • 27
  • The only option I see would be to issue a `create table` command directly using the vfpoledb provider. It's cumbersome, but it might work. – alex Feb 08 '11 at 11:58
  • I have just found out you need the DBF file in a **dBase IV** format so I have edited my answer. Try it, it should work with the GIS (not sure if you open it with MS Access, though). – Jaroslav Jandek Feb 09 '11 at 08:08

3 Answers3

10

Download Microsoft OLE DB Provider for Visual FoxPro 9.0 and use:

string connectionString = @"Provider=VFPOLEDB.1;Data Source=D:\temp";
using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = connection.CreateCommand())
{
    connection.Open();

    OleDbParameter script = new OleDbParameter("script", @"CREATE TABLE Test (Id I, Changed D, Name C(100))");

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "ExecScript";
    command.Parameters.Add(script);
    command.ExecuteNonQuery();
}

Edit: The OP does not want a FoxPro DBF format but dBase IV format:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp;Extended Properties=dBase IV";

using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = connection.CreateCommand())
{
    connection.Open();

    command.CommandText = "CREATE TABLE Test (Id Integer, Changed Double, Name Text)";
    command.ExecuteNonQuery();
}
Jaroslav Jandek
  • 9,463
  • 1
  • 28
  • 30
  • How do you figure that "Microsoft OLE DB Provider for Visual FoxPro 9.0" is built in to the .NET Framework? That's no more built-in than my suggestion to use Microsoft Excel to do the heavy lifting. (But yes, +1, still a better suggestion than mine.) – Cody Gray - on strike Feb 08 '11 at 12:00
  • Note: The `ExecScript` command can be used to copy the table file to other formats as well. @Cody: Thank you. Well, the interface is built-in, the provider may not be. We had it pre-installed, I added the download link anyway. – Jaroslav Jandek Feb 08 '11 at 12:07
  • Thanks. I have tried this but when I try and open it in Access it says "External table is not in the expected format.". Any ideas? – Greg Feb 08 '11 at 14:11
  • Remove the `@` escape character and add `\r\n COPY TO TestInOldFormat TYPE fox2x` to the script string - it will copy the FoxPro DBF to the old format that also opens in Excel. I am not sure it will open in Access, though. See http://msdn.microsoft.com/en-us/library/aa977446.aspx for other formats. – Jaroslav Jandek Feb 08 '11 at 14:38
  • Thanks again. I tried that and it did work in Excel, but like you thought not in Access. The other ShapeFile DBFs do work in Access, so if mine cannot then it will not be recognised within the GIS program I am using. I don't think any of the other formats on the link you sent will change this either. I've made a start on creating the DBF myself with a binary writer, though it looks like it is going to be tricky beyond the header. – Greg Feb 08 '11 at 18:45
  • @Greg: Yeah, you should have provided the exact format the GIS requires. M$ Access doesn't support **DBF** databases anyway and only barely importing them, it uses its own **MDB** format, doesn't it? Did you want to create **MDB** or **ACCDB** instead (you can do that using `CatalogClass`)? – Jaroslav Jandek Feb 09 '11 at 07:44
  • @Jaroslav: Yes, Access converts files to MDB format, but as it wasn't doing it with mine originally I thought something was wrong, but it is the DBF format I want. I've done what you said, and it now seems to be starting to work. I still have some things to iron out, like it seems to limit the filename length it makes to 8 characters, and also it is creating a DBT file which I don't need. Do you know how to fix either of those things? Anyway though, it's on the right track, so thanks a lot for your help! – Greg Feb 09 '11 at 10:47
  • @Greg: it only supports 8.3 (name.extension) filenames. But you can easily rename or delete any files after the creation using the `File.Move` and `File.Delete` methods respectively. `DBT` is a storage for content of dBASE memo fields - you probably won't need memo fields. – Jaroslav Jandek Feb 09 '11 at 18:00
2

I don't know anything about ESRI ShapeFile... but you can create a dbf using OleDb.

Here is an example using the VFP OleDb provider:

    string dbfDirectory = @"c:\";
    string connectionString = "Provider=VFPOLEDB;Data Source=" + dbfDirectory;
    using (OleDbConnection connection = new OleDbConnection(connectionString)) {
        connection.Open();
        OleDbCommand command = connection.CreateCommand();

        command.CommandText = "create table Customer(CustId int, CustName v(250))";
        command.ExecuteNonQuery();
        connection.Close();
    }
Tom Brothers
  • 5,929
  • 1
  • 20
  • 17
1

If you want to completely eliminate external dependencies, you will have to resort to creating the file manually. And in order to do that, you'll need to spend some quality time with the whitepaper describing the file format's specifications to understand the fields you need to implement and what they should contain. You can find that online here: http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf

Of course, this isn't really an undertaking for the faint of heart. Make sure that you understand the work that is entailed here before embarking on the journey.

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574