Connecting dBase (xBase)
Connecting dBase (xBase)
Intro: dBase (xBase) and OpenOffice. dBase is one of the oldest database programs, a venerable oldie but still active and useful and now generically referred to as xBase. The oldie-timer dBase programmers sometimes call themselves "Clipperheads" after the popular CA-Clipper dBase compiler. Today open source and Clipper compatible Harbour and xHarbour compilers have largely replaced Clipper and also GUI applications are available for Windows. If you want to learn more about xBase, you might start here. http://en.wikipedia.org/wiki/XBase .
xBase is not part of OpenOffice, instead OpenOffice has its own modern integrated database (HSQLDB - see http://hsqldb.org/) . While this is a very important capability, sometimes it is helpful to connect and use other databases. Now, thanks to the work of the oOo team both OpenOffice Calc and Base provides integration for other database systems, which includes basic integration for xBase data files too. Thanks oOo team!
So, if you already know something about xBase and want to get going with OpenOffice.org, here are a few simple things you'll want to know.
A. Open/Modify dbf files using Base Base will open all dbf files in a specified directory. This a GOOD thing because it is generally what you were thinking (assuming you were). So put all the dbf files related to your project in a single directory and proceed as follows:
1 Open Base (get Main Menu)
2. Select "Connect to an existing database" and select type "dBASE".
3. Select the folder with your dbf files.
4. Select final options as desired and "Finish".
5. Name your database connection file whatever you want (eg "MyxBasefiles.odb) and save it somewhere. Henceforth access your database files by clicking this file. ooOoo, Nice.
6. Select "Tables" on the left panel, and there are your data files (called "tables"). Mess with them. Try to edit, try to sort, try to mess them up. How else you gonna learn? [did I mention making a backup?]
B. Create/Open/Modify your dbf (or CSV) files using Calc
Don't have a dbf file to play with, bunkie? Not to worry, with OOO you can make one! CoOoL.
(These comments relate specifically to dBase III+ capabilities to keep it simple (KISS). Should be OK, but no warranty.)
You can define up to 128 fields (data holders) in your database. Each field requires the field name, data type, and field width. Numeric data also requires specification of the number of decimals. All data in ASCII.
Data types include character (C - a maximum of 256 characters), date (D - fixed 8 characters YYYYMMDD), logical (L - 1 character), Numeric (N - up to 19 characters with up to 15 decimals), and memo (M - 10 characters, formatted - a marker pointing to an entry in an associated DBT file with entry up to 4096 characters - Memo fields require separate files, not discussed here).
Example:
1.Open a blank sheet in Calc
2. In row 1 place the field name,type, width (, and decimals, if numeric), as shown below.
3. In row 2 begin the data entry (for dates use normal calc format , eg 10/22/2006, as conversion to dbf date format is handled automatically).
4. Save the file as test.dbf
Use Save As... from the menu and refer to the drop-down box in the dialog referring to file type and select dbf as the type. Don't forget to type in a name for the file... duh :)
You have created a simple .dbf file. This file may be accessed using your favorite xBase flavored program (some tweeking may be required, I have no difficulty using my official Ashton/Tate dBase III+ or with current versions of harbor or xharbor, but I'm just a simple guy with simple data so see advance info below ** if you run into a problem). This file may also be accessed using OOO Calc or Base.
5. More fun (Assuming you can stand it).
Save the test.dbf file from Calc as a CSV (comma separated value) file instead of dbf and then open the file using your favorite text editor. This is what you should see in Test.csv:
"Item,C,20","Purchased, D","Cost,N,10,2"
"Chair",10/22/01,98
"Table",09/16/00,482.5
Calc and xBase programs can also read and write these formatted files, giving even more compatibility, plus this means you can edit the data with a text editor (like NotePad++). While the latter is not necessarily the "recommended" method of editing dbf files (you can use Base or Calc!), nonetheless it IS possible and pretty easy when you are pressed. [did I mention making a backup?].
C. ** Advanced: Hex dump of example dbf file (in case of compatibility issues).
For advanced users a Hex dump of file test.dbf follows. Here using NotePad++ as hex viewer. See advanced info at http://www.clicketyclick.dk/databases/xbase/format/index.html
[Above is Copyright 2007 Pimpernel, hereby licensed by author under GNU Free Documentation License. Author is prepared to license under similar type open source license as needed for OOO Wiki. Advise here or by mail. --Pimpernel 21:37, 17 December 2007 (CET)]
