Yeah nice, but can your add-in deal with very large DBF files?

Of course it can. Mind you the maximum is 1,048,575 records...which is the limitation of the Excel 2007 worksheet.

Check out the YouTube video below.



Quite a few people are using the add-in with engineering applications (like SCADA) and with geographic information systems (ArcInfo, ArcMap etc.).

I already have plans for an even more advanced version specially for engineers. Planned features will likely include:

• ability to overwrite the original dbf file (of course a date stamped backup file would be created to ensure no loss of data)
• ability to over-ride the add-in defined field sizes. The add-in is pretty good at defining the field types and sizes from the data, however engineering systems might require a pre-defined field size. So the add-in will make suggestions, a popup shows with the suggestions, user can over-ride or just click ok.
• batch processing capabilities
• even more improved error-handling capabilities

So what d'ya say? What's your fancy? Suggestions are welcome!!!

You can download the add-in on the right in the sidebar.

UPDATE: Save DBF in Excel 2007

I have upgraded the Excel 2007 add-in to address a few requests:

1. Now you can add/insert new fields, create calculated fields in addition to adding new records or editing existing records in your native DBF file!

2. If you start with an Excel file the software now have enhanced capabilities to determine the field types (better than Microsoft's own in earlier Excel versions).

3. The add-in checks DBase field naming conventions and also identifies duplicate fields. All problem field names are visually identified with a cell comment!

4. If you start out with a brand new file and forget to save it, the add-in will ask before the conversion.

5. Large files are supported. I edited files over 500,000 records with no problem.

KNOWN ISSUES:

- Microsoft Excel 2007 has a bug related to file names used in worksheet functions...If you experience any issues (i.e. clicking SaveDBF does not seem to work), try renaming your file to a short and sweet name and try again. An example on the nature of the problem is described here. (in case you REALLY wanted to know...)
Update: Do not use spaces or dashes (-) in the file name or in the worksheet name you are trying to convert! Better replace them with underscores (_).

I invested significant time and effort into developing this software.

PLEASE SUPPORT ME by donating on the right sidebar of this blog!

DOWNLOAD the new version from here.

Please read the installation instructions in the PDF file!

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.