Importing and Exporting DBF Files

[gtroberson]

I need to import .dbf files, ecit them and then export back out as a .dbf file. When I open a .dbf file, it displays the field name in the first row, along with some apparent formatting information. Can this row de displayed with the field name only? If i create new columns, how do I apply the formatting?

[Villeroy]

The additional header info in Calc should remind you to not mess around with numbers and strings. This could spoil your database. How to import a dBase directory into a database. viewtopic.php?f=13&t=3459

[gtroberson]

The .dbf file I am editing is part of a shape file set used by GIS software. We typically use a spreadsheet, such as Excel 2003 to open the dbf in Excel and add columns that a geostatistical software package, such as Geoda, needs to perform its statistical analysis. The file is then saved back in the dbf format. Excel 2007 took away the save as dbf option. That is why I was investigating using Calc as an option.

When you open the dbf in Excell, you see only the field name, not the extra string behind it. For example, TWG_ID is the field name displayed in Excel. When the same file is opened in Calc, the cell is displayed as TWG_ID,N,11,0. I assume the N,11,0 string defines the field. If I add the new columns in Calc, how do I determine what string to add to it? What will be the consequence of not including the string characters? What do the N, 11, and 0 in the string define?

[Villeroy]

A spreadsheet can easily violate the constraints of the database table (dBase is a database format rather than a spreadsheet). A single value of wrong length or a text in a numeric column can spoil the underlying database. As far as I can tell (just edited and saved a database table in Calc), the additional field info won't be saved. Do it like Excel. Just ignore and trust your goddess.

[Villeroy]

Forgot to answer the question about the field info content:
"N,7,5" seems to refer to numeric values of lenght 7 with 5 decimals behind the comma.
"C,7" seems to refer to datatype "Char" with a maximum lenght of 7
"L" denotes all my boolean fields
"D" is date and/or time obviously

Notice that this has nothing to do with formatting. Data types specify the allowed content of a database field, which can be formatted one way or the other. When I save a spreadsheet with a text value in a field that used to be numeric before, all the values get converted to string. I did not do further experiments with numbers larger than specified, invalid dates and alike.

[Weatherlawyer]

Well I just tried it, downloaded with Firefox, clicked Open and changed the capitals .DBF to ordinary letters: .dbf.

The OS XP, couldn't find out how to open the file so I chose Open Office which needed prompting a few times. It eventually opened in Calc not Base.

[rangernemo]

I use OOo Calc to edit shapefile DBFs all the time... I have a shapefile with almost 9000 polygons. A couple times a month, I download three CSV files and join them to the shapefile, and export to a new shapefile. This gives me a snapshot of the shapefile at that date. The trouble is, the resulting DBF is 63 megabytes.

I open the DBF in OOo Calc and immediately delete all the formatting data... All the ",N,7,5", ",C,256", et al. The C,256 is the type that bloats my DBF. If you don't have similar data lower in the table, you can use Find and Replace to quick strip the formatting out. If you do have similar data in the table, you can select the top row and use the More Options / Current Selection Only option.

I found out through trial and error that you HAVE to remove the formatting data if you want to save back to a DBF. DBF doesn't allow commas in the column name, which it gets from the first row. So, if you open a DBF in Calc and try to immmediately save it, it won't.

When all the formatting data is gone, I format the data the way I want it. I might delete un-needed columns. I might create and fill new columns. I'll espcially format the cells' digit-lengths... I don't need to know acres out to the eighth decimal place. Once I save it, the 63MB file slims down to a svelte 1.7MB... 97% of the original file was padding for columns that where 256 characters wide.

I've not tried sorting the table data, or adding/removing rows. I don't know what that would do when it tried to join back to the spatial file. I also wouldn't recommend this for data files for programs that care about the DBF file format specs. None of the GIS programs I've tried (ArcGIS, QGIS, GRASS, and fGIS) care about the column formats of shapefiles, so changing them is OK. Of course, your mileage may vary, depending on any extensions you might be using.

The one problem I've run into is the new 2.4.0 gives an error when trying to save the DBF... It says it can't connect to the file. So, I uninstalled 2.4.0 and went back to 2.3.1.