SaveDBF Excel Add-in future plans

Dear Fellow DBF Users,

The latest upgrade of the SaveDBF Excel 2007 add-in (used to be called XLSX2DBF) can be now downloaded from HERE!.

The changes include fixes related to minor bugs in Microsoft's OLEDB engine and some improved field type identification for users who start out with an Excel file.

Future plans:

I plan one more upgrade in Jan/Feb 2010. This will be a major upgrade with lots of planned improvements as listed below:

- Add a Preference window where users can set to:

• Overwrite the original DBF file (and create a date-stamped backup file in the same folder).

• Mimic Microsoft's Excel 2003 behaviour (i.e. automatically replace spaces in field names with an underscore, accept field names longer than 10 characters)
  

- Add a Data Conversion window where the user can change the field type and size from what the add-in determined from the data. This is useful for people who start with an Excel file with data that do not necessarily reflect the maximum field size for a character field, for example. The add-in will guess the maximum size from the data, but the user can over-ride it in the new window. Or you can simply click OK and the add-in will behave exactly as it does now.

- Batch processing capabilities (i.e. calling the macro from a third party program (e.g. Python or Perl or C++ etc) and mass-process many files without user interaction.

- Improved speed for very large DBF files and a progress bar with % complete reported.

If you have other improvement ideas this is the time to email them to me at gygulyas - at - yahoo - dot - ca!

Thank you very much for your continued support!

Long live the DBF!

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.

YouTube Demo 2: Excel file ---> DBF File

I recorded a short video on how to manipulate an Excel file using the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here).

YouTube Demo 1: How to manipulate a DBF file?

I recorded a short video on how to manipulate a native DBF file using the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here). More will follow...

YouTube: SaveDBF Installation Instructions

I recorded a short video on how to install the SaveDBF Excel 2007 add-in and uploaded it to YouTube (access it here). More will follow...

Excel 2003 - as a dbf editor

Some people asked my why they should switch to Excel 2007 for dbf file editing with the add-in instead of just 'sticking it out' with Excel 2003...

Some considerations:

- Excel 2003 can only handle 65535 records. Excel 2007 works with 1 million records in a worksheet.

- Excel 2003 is very bad at retaining variable types and variable precisions so your dbf file structure changes (lots of time bloats). The SaveDBF add-in will preserve the original field types and size will only change if your edit results in a longer field. I.e., you edit your data and enter 11 characters in a text field that was only 10 long.

- Excel 2003 does not warn the user about violations to field naming standards imposed by dBase. I.e., a field name is longer than 10 characters it will get truncated (which could create a duplicate field, a common issue with GIS). The addin warns the user about violations visually highlighting the problem fields and adds cell comments with suggestions to fix them.

- the add-in does a much better job at creating field types and sizes when the user actually starts from a native Excel file. Microsoft only looks at the first few records to decide on the field type.

AND...

- Eventually you will have to switch...:-)

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 OR here.

Please read the installation instructions in the PDF file!

Update: SaveDBF add-in

I have added a number of new features and fixed some minor bugs in the SaveDBF add-in:

- Some users experienced a "ghost link". Now it is removed.

- Added a Save As dialog box to give the user the ability to select their own file name and location (it still gives you a default name, and NO you CANNOT overwrite your source file).

- Better error handling, including the check of field sizes against the dBase limitations.

- Added suggestions when the User violates the dBase field naming standards.

- The add-in extends the original field size of the DBF only if the user edits an existing integer or character field and increases its length.

I will release likely the last version for the year in early October so come back and visit!

Thanks for all the suggestion!

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 OR here.

Please read the installation instructions in the PDF file!

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.