Wednesday, July 01, 2009

Save DBF Files in Excel 2007

Microsoft has decided not to support dBase files in Excel 2007. Many GIS users and especially people in South America where FoxPro is still widely used were left without an easy-to-use data editing tool.

XLSX2DBF is an Excel 2007 add-in that helps convert and/or save a native DBF file that was edited with Excel 2007.

Two common scenarios are supported:

A) Open a native DBF file in Excel 2007 – Make changes – Save as a native DBF file with the changes.

B) Open/create an Excel file that looks like a database (headers/columns/rows) – Save as a native DBF file.

NOTES:

• The second scenario requires the header (field names) to meet dBase field naming standards (max. 10 characters, no spaces etc.).

• The native dBase file will not ‘bloat’ in size and all field types/sizes will be the same as the original.

• Excel 2007 still reads/opens DBF files; however it will not save them as DBF. You can associate the dbf extension with Excel and then you can open the dbf file in Excel by simply double-clicking on it (how to set the default program for a file type: http://www.youtube.com/watch?v=O7ateZy-DTE).

• Excel 2007 requires a slightly different process for add-in installation. See how at http://peltiertech.com/WordPress/installing-an-add-in-in-excel-2007/.

• Binary, MEMO, OLE fields are not (yet) supported.

• The original dbf file is never overwritten. Another dbf file with the same name and ‘_new’ suffix is created in the same folder. The original index files (NDX, MDX, CDX, IDX etc.) and memo files (DBT, FPT), if any are not copied with the ‘_new’ suffix. This is done to protect the integrity of the original data.

INSTALLATION INSTRUCTIONS:

1. Unzip xlsx2dbf.zip into a temporary directory.

2. Copy xlsx2dbf.xlam to one of the trusted locations (try C:\Program Files\Microsoft Office\Office12\XLSTART\)

3. Install the Excel 2007 add-in. See how at http://peltiertech.com/WordPress/installing-an-add-in-in-excel-2007/.

After successful installation, the add-in appears as XLSX2DBF on the right side of the menu. Clicking it will show a ribbon as presented below:



KNOWN ISSUES:

If the add-in does not execute after installation (i.e., upon opening Excel 2007, the macro is disabled despite being in a trusted location), you will need to install the Office 2007 Service Pack 2.

If you create some data 'from scratch' in a new Excel workbook, first you have to save it as an Excel file (any name will do if it is a temporary file). Once the file is saved, you can click the XLSX2DBF button on the ribbon and the active sheet will be saved as a DBF file. Unnamed workbooks are not 'known' to Excel until they are saved (even if they contain data you entered).

DOWNLOAD the add-in from the September 1, 2009 post (UPDATED version).

SUPPORT: If I helped you out, please donate any small amount by clicking on the DONATE button on the sidebar of this blog.

1 comment:

lisa sparx said...

You can use the containers Wizard to create an active document containers by selecting the Active document containers check box on the Compound Document Support page of the containers Wizard. For more information, see Creating an Active Document containers Application.