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:

filter records

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:

filter dbf file

Though these filtering features are sufficient in some cases, the SQL queries mechanism is much more powerful.

An SQL query is applied to all DBF file records, not to the filtered ones only.

 

Sort DBF file (Ctrl+T)


You can use this dialog window for ascending or descending sorting of the current table by any fields:

sorting dbf file

  1. 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.
  2. Then you can choose the sort direction for all necessary fields. By default, the sorting is done in ascending order.
  3. 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:

backup dbf file

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.

If the current DBF file contains any deleted records, they will be packed automatically when sorting the file.

 

Set DBF file codepage


In the dialog window, select an item to set a new codepage flag for the DBF file:

dbf file code page

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:

compare DBF files

 

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:

SQL script

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:

dbf file advanced statistics

Summation or averaging cannot be applied to non-numeric fields.

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:

dbf file statistics

Executing this command may take a lot of time because of calculating all kinds of statistics for every field of the DBF file.

 

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:

Chart Types

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:

Data Source

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):

Chart Completed

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:

Chart Preview

 

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.

 

Options... (F12)


This dialog window consists of three sections: General, Appearance, and SQL.

dbf file options

dbf file appearance

sql dbf options

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.