DBF Tools
This submenu contains other special tools for handling DBF files.
Filter records (Ctrl+L)
Use this command to show or hide the Filter Panel. Enter the necessary value into the appropriate edit box (or enter several values into several edit boxes), and press the Enter key:
The filter will be applied, and the grid will contain filtered rows only. To reset the filter, change a value in any edit box of the filter.
In the picture above, the filter was set to show only records whose "on_order" field values are greater than 0 and whose "discontinu" field values are False.
The following 5 comparison filter commands are supported:
- ">" – show all rows whose field values are greater than the filter value.
- ">=" – show all rows whose field values are greater than or equal to the filter value.
- "<" – show all rows whose field values are less than the filter value.
- "<=" – show all rows whose field values are less than or equal to the filter value.
- ">><" – show all rows whose field values do not match the filter value.
Case Sensitive and Partial Compare are the filter match options.
You can also define the whole filter string (complex condition). It is a much more flexible and powerful feature. For example, you can:
- Use the "or" key word (not only "and").
- Set the priority of filter expressions by using the round brackets: "(" and ")".
- Etc.
Example:
Though these filtering features are sufficient in some cases, the SQL queries mechanism is much more powerful.
Sort DBF file (Ctrl+T)
You can use this dialog window for ascending or descending sorting of the current table by any fields:
- Click the toolbar button (or press Ctrl+Right) to add the selected field to the "order by" list. You can select multiple fields to add all of them.
- Then you can choose the sort direction for all necessary fields. By default, the sorting is done in ascending order.
- If you want to exclude some of the added fields, press Ctrl+Left or click the appropriate toolbar button. Multiple selection is supported, too.
After forming the sort list, click the OK button. The confirmation dialog box will appear:
The warning means that sorting of the current file cannot be undone: the existing DBF file will be re-created with the defined ordering. But before any changes are saved to that file, a backup file (with the BAK extension) will be created. For example, if the current file's full name is "D:\dbfs\table1.dbf", the backup file will be "D:\dbfs\table1.bak".
If you click the Yes button, the current file will be sorted, and the table column headers will be marked as sorted in ascending or descending order (see DBF File Viewer and Editor).
You can also click a column header to quickly sort the current DBF file. The file will be sorted by the field. By default, the sort direction is ascending.
If the file is already sorted by that field, the sorting will be done in descending order.
Set DBF file codepage
In the dialog window, select an item to set a new codepage flag for the DBF file:
If the Preview checkbox is checked, the new codepage will be immediately applied.
You can also view the complete list of DBF file codepages.
Compare DBF Files
When the Open dialog appears, select a DBF file to be compared with the current one.
The command will compare the two DBF files by their field names, and by field types too (if the Consider Field Types option is checked). Identical and different fields will be shown in the respective lists:
Generate SQL Scripts
This very useful tool has been implemented to let you create a database table with the required structure, and insert data into it. To do that, you can execute the resulting SQL script file in a Database Management System (DBMS).
There are a lot of SQL script options:
If you change any option, it will immediately affect the SQL script text in the Script Preview box:
- SELECT clause and CREATE TABLE clause – defines whether to generate the SQL clause.
- Field names in [] – encloses field names in brackets.
- Syntax – field type names depend on a particular DBMS's syntax. E.g., "varchar" in MS SQL Server corresponds to "character varying" in Postgre SQL. This option affects the CREATE TABLE clause syntax. If the Visual FoxPro syntax is selected, you'll get a script for creating the current DBF file.
- INSERT INTO clause – generate SQL clauses for every record of the DBF file, with the appropriate field values. In the Script Preview text box, you can preview the top 10 records. If you click the Save button, the INSERT INTO clauses for all of the DBF file's records will be added to the text box and saved to the specified *.SQL file.
- Skip deleted records – skips deleted records when forming the INSERT clauses.
- Character field quotes – defines which kind of quotes (single or double) will be used with character field values in the INSERT INTO clauses.
- Trim trailing spaces – in the Character fields of a DBF file, each value consists of the exact value with trailing spaces (" ") to fill the field width. For example, the field type is Character(10), and its value is displayed as "abcde". In this case, the exact value is "abcde ". To use the value without the trailing spaces in the INSERT INTO clauses, enable this option.
- Replace True/False with 1/0 – in some DBMSs, each logical (Boolean) value is represented as 1 or 0 (True or False, respectively). If this is the case, enable this option.
- Replace CR/LF with ¶ – affects the Memo field values containing line feed characters. If this option is disabled, each line feed character will be represented as two spaces (" "), and the whole Memo value will be transformed to a single string. If you want to mark line feed characters as "¶", enable this option.
- Insert into defined fields – a (comma-separated) list of custom fields the values are inserted into. The resulting SQL clause will be like this: "INSERT INTO table1 (field1,field2,field3) VALUES ...".
- Datetime format – formats the Date and DateTime fields as specified. By default, the edit box contains the system date and the time format mask.
- File encoding – defines the resulting *.SQL file's encoding: ANSI, Unicode, or UTF-8.
- Add GO clause – enabling this option will add the GO clause each time a certain amount of scripted records is processed. You can set the amount in the Step edit box.
Advanced Statistics
This window provides different statistics on the DBF file's fields. Double-click a cell or press the Enter key, and this dialog window will open:
You can also export the statistics grid to an XLS, HTML, XML, or CSV file, or print it. To do that, click the appropriate button.
Statistics
Actually, this command is a set of SQL queries that try to count all kinds of statistics (sum, minimum, maximum, average, and distinct) by every field of the DBF file. The result is a temporary, ordinary DBF file, which opens automatically after executing the queries:
Trim Spaces
This command quickly crops leading or trailing spaces (or both). Current field values are processed. This command can be applied to string fields only (Character and Memo).
Nullize Field
Sets a NULL value in every record of the selected field, provided that the field can hold NULL values.
Chart Wizard
This command executes the Chart Wizard.
At the second step, choose the chart type:
Then select fields of the current DBF file, to be used as the data source for the X and Y axes, and also (optionally) as labels for the X axis:
At the next step, the construction of the chart will be completed. You can Copy the chart image to the clipboard, or Export it to an image file (*.PNG, *.JPG, *.JPEG, *.GIF, or *.BMP):
Use the command line (at the bottom of the wizard winow) to generate the current chart in batch mode.
At the Chart Wizard's last step, you can preview and print the resulting chart. You can resize the chart to place it on the page as you need; to do that, drag-and-drop the chart's margins. Finally, select the Orientation of the page, and Print the chart:
Auto Fit Columns Width (Ctrl+W)
Fits the columns' width depending on the cell's text width in the current row.
Select Deleted Records
Run this command to select only records marked as deleted from current DBF file into a new table.
Delete Duplicates
Use this feature to find duplicate records and mark them as deleted. The search occurs among selected fields, Case Sensitive option available. Also, there is an opportunity to keep first and delete all last found duplicates. Or, vice versa, delete all the first found records, and keep only the last one.
To restore one or all deleted rows, use Recall command. To remove rows completely, use Pack command.
Database Scheme
This tool allows you to explore a DBF database stored in a directory.
In the dialog box appears, specify directory with DBF files. The scheme shows all the tables from this directory with field names and types. If two or more tables have the same field name, these fields are underlined (possible references, foreign keys, etc.) Clicking on a field, other fields with the same name are highlighted.
If the directory contains DBC file (Visual FoxPro database container), all relations from this file are drawn with a line from one field to another referenced one. Such field names are written in blue color. By clicking on one of the linked fields, the line becomes thick. If a field have an index, there is the arrow sign to the left of the field name.
Double-click a field in order to filter tables that have field with the specified name. Or, write fields comma-separated and press Apply button. Click Refresh to reset the filter.
Status bar of the window contains table, index, field, and record count summary for currently selected table, filtered tables, and for the entire folder.
Options... (F12)
This dialog window consists of three sections: General, Appearance, and SQL.
The General section contains the following options:
- Language – sets the user interface language for DBF Commander Professional.
- Associate to *.DBF files – creates a DBF file association with DBF Commander Professional without the need to reinstall the application.
- Check for updates - update checking occupies a few seconds and it will inform you about features implemented in new version. It's recommended to remain this option checked.
- Open new files in existing instance – creates a new process or opens a new DBF file from Windows Explorer in the existing instance of DBF Commander Professional (if any).
- Exit on Escape – enable this option if you want to close DBF Commander Professional by pressing the Escape key instead of by using the Alt+F4 shortcut key.
- Pack Immediately – if this option is enabled, each time you delete a record, the Pack Table command will be immediately executed.
- Do not confirm on delete and pack – use this option carefully: it disables the confirmation box ("Are you sure you want to...") that is otherwise displayed when you delete records or pack tables.
- Create backup files (*.BAK) – it is recommended to keep this option enabled. If you disable it, no backup files will be created after operations on the current DBF file, including the following: Sorting, convert from ANSI to UTF-8, convert from UTF-8 to ANSI, repair a DBF file, convert a DBF file from the Windows encoding to the MS-DOS encoding, convert a DBF file from the MS-DOS encoding to the Windows encoding, remove the Memo file flag, and so on.
Appearance related options:
- Grid Font – table grid's font.
- Grid Colors – odd, even, and current (cursor) row colors.
- File name at tab title contains path – disable it if you need a table tab to contain a DBF file name only (without the path).
SQL queries execution related options:
- Add structure changes as queries to SQL Panel – check this option if you need every DBF structure change to be added as a query to the SQL Panel.
- Automatically open DBF files participated in SQL queries – for instance, you may need to run the UPDATE query on a large DBF file. Opening the updated file may take a lot of time. Disable this option if you only want to run the SQL query, without opening the resulting file.
- SQL Panel Location – determines the location of the SQL Panel.