DBF File

This menu item contains the commands for handling DBF files. The commands are listed below.

Create DBF file (Ctrl+N)

Opens the File Structure dialog box:

To create a new DBF file, you need to fill in the following fields:

• File Name – the full file name, including the path. Click the "..." button to invoke the Save dialog box. Specify the file name and click Save.
• File Type – the new DBF file type. It may be "Visual FoxPro" or "dBase III plus".
• Fields (<current field number> of <field count>) – the grid that contains information about the following DBF file fields:
• Name – the field name (up to 10 characters). Duplicate field names are not allowed!
• Type – the field type. You can select it from the drop-down list.
• Width – the field length (in bytes). If the value you specified is more than allowed, it will be reduced to the maximum acceptable value.
• Decimal – the field length (in bytes). If the value you specified is more than allowed, it will be reduced to the maximum acceptable value.
• Null – allows or forbids null values in the field. By default, null values are acceptable.

After defining the necessary fields, you can move in the grid and do changes. If your file contains an integer autoincrement field, the following edit boxes will be enabled:

• Next Value – the beginning value of the autoincrement field. By default, it is zero.
• Step – when adding new records, the value in the field will be automatically incremented by Step. By default, it is one.

The File Structure window also contains a toolbar for additional operations:

• Append New Field (Ins) – a blank record appears between the current record and the previous record. After you specify the field data, this record will be added at the end of the File Structure Grid (and will become the last record). You can also add a new record by pressing the "Arrow Down" key at the end of the fields list.
• Delete Field (Ctrl+Del) – removes the current field.
• Move Field Down (Shift+Arrow Down) and Move Field Up (Shift+Arrow Up) – a new field is always added at the end of the list, and this command lets you move the current field up/down in the list.
• Export Structure (Ctrl+E) – exports the DBF file structure to a file of any supported export formats: XLS, HTML, XML, or CSV, or to another DBF file.
• Preview and Print (Ctrl+P) – opens the respective dialog window to preview and print the DBF file structure.

After making sure that the new file structure is correct, click the OK button.
If you create a new DBF file, the necessary SQL query will be generated and executed, and its text will be added to the main window's SQL Panel.

Example:

Otherwise, if you change the structure of the DBF file, the program will generate the SQL queries list to alter the file structure based on the changes. The necessary file will be altered, and the SQL queries will be added to the main window's SQL Panel:

Clicking the Cancel button (or pressing the Escape key) will close the File Structure window and discard all structure changes.

Open DBF file (Ctrl+O)

Shows the Open dialog box. Select a DBF file and click Open. If you select multiple files, all of them will be opened.

The filter in the Open dialog box consists of the following file extensions:

• DBF Files (*.DBF) – Data Base File. The format can be Visual FoxPro or dBase.
• Memo Files (*.FPT, *.DBT) – Visual FoxPro (*.FPT) and dBase (*.DBT) MEMO files. When you open such a file, actually the corresponding DBF file opens, whose MEMO fields data are stored in an FPT/DBT file.
• All Files (*.*) – force DBF Commander to open a non-DBF file. If the file's header and structure are DBF- or dBase-like and correct, the file will be opened in the usual way, as a DBF file. Otherwise, the "Not a table" error will be invoked.

To open one or more DBF files, you can also select and drag-and-drop them to DBF Commander's main window.

You can find the Recent Files submenu next to the Open tool button. That submenu stores up to 20 recently opened DBF files, and lets you quickly open any of them. Its items are sorted in the order the files were opened.

DBF File structure (Ctrl+S)

Invokes the File Structure dialog box described above. But in this case, the File Name and File Type edit boxes, as well as the Fields grid, are filled by the current DBF file's values.

As in case of creating a DBF file, you can modify the structure of the current file, change the DBF format (Visual FoxPro to dBase III plus, or vice versa), and export or preview the structure.

Clicking the OK button will apply the changes. Clicking the Cancel button (or pressing the Escape key) will close the File Structure window and discard any structure changes.

Import to DBF file (Ctrl+I)

Launches the Import Wizard. This wizard allows you to import data from one of the following formats:

• XML Files (*.XML)
• Comma Separated Files (*.CSV)

On the first page of the wizard, select the source file (CSV or XML) that you want to import from, and select the destination DBF file. If the destination file does not exist, it will be created.

In case of an XML file (previously exported from DBF Commander Professional), no additional options are required. But importing from CSV is more complex, so the wizard lets you specify a few input file options.

CSV file options:

• 1st row contains field names and 2nd row contains field types – a CSV file, previously exported from DBF Commander Professional, contains field names separated by a delimiter in the first row, and field types in the second row. If you're importing such a file into a new DBF file, the latter will be created with the appropriate structure. Also, in this case the Start from row # option value should be set to 3 in order to avoid inserting field names and types as data into the resulting file.
  Otherwise, if the CSV file does not contain any field names or types (or if this option is unchecked), all fields in the resulting DBF file will be of the Character(254) type, and will be named as "field1", "field2", and so on.
  In case of importing into an existing DBF file, this option has no effect.
• Start from row number – all records before that number will be skipped when importing.
• Delimiter – the data fields separator of the source CSV file.

If you change any options in the wizard, you can use the Preview table to see what the resulting DBF file will look like. After setting the options, click the Next button.

At the last step of the wizard, you'll get a command line for the current import process. You can use that command line to import to DBF in batch mode.

If you need to import from other formats, please ask about it on our Forum.

Export DBF file (Ctrl+E)

Exports the current DBF file to one of the following formats:

• MS Excel Files (*.XLS)
• HTML Files (*.HTML)
• XML Files (*.XML)
• Comma Separated Files (*.CSV). In the dialog box, select the CSV file's encoding (ANSI or UTF-8), and enter the delimiter to be used (by default, semicolon).

If you need to export to other formats, please ask about it on our Forum.

Save DBF File As... (Ctrl+Shift+S)

Saves the current DBF file to another file. This feature is very useful for saving the resulting dataset of an SQL query to a DBF file.

Import to SQL server (DBMS) and Export from SQL server – Oracle, MySQL, MS SQL Server, PostgreSQL, etc.

This feature is intended to enable data transfer between a server (managed via a DBMS) and a DBF file. The server, the database, and the table to be imported/exported should be specified via a Connection String. If you already have a connection string, just paste it into the edit box. Otherwise, you can use the Build... button to create a new connection string.

In this case, the Data Link Properties standard dialog window will open. Select the Provider, choose the server and the database, and specify the login and the password. Depending on the provider type, some of these parameters may be optional. After specifying the parameters, click the Test Connection button to check the connection. If the connection has been established successfully, you can use the resulting connection string to import or export data from/to the DBMS. Click the OK button to close the Data Link Properties window.

To use a UDL (Universal Data Link) file instead of the connection string, just click the respective button. Actually, a UDL file is an ordinary text file that contains all information about the connection (just like a connection string), so you can create this file yourself.

After specifying the connection string, click the Connect button to establish a connection to the database. The drop-down tables list will be filled with the table names of the database. The drop-down list includes the following options:

• Use double quotes – a certain DBMS may need the table name to be enclosed in double quotes.
• Show system tables – enable this option if you need to work with the system tables of the database.
• Empty destination table - deletes all rows from the destination table.
• Omit deleted records - select this option to skip rows marked as deleted.
• Server-side cursor – some databases (e.g. MySQL 5.1+) do not support fully functional server-side cursors. That's why DBF Commander Professional uses client-side cursors when connecting to a database by default. If you want to use server-side cursor, enable this option, and then click the Connect button again.
• Enclose field names in – if any field names in the table that you want to export are the same as reserved words (e.g. "date", "time", "current", etc.), an error may occur during the data transfer. To avoid this issue, specify the characters for enclosing the field names in. This drop-down list contains such characters for the most common DBMSs: [Square brackets] (MS SQL Server), "Double quotes" (Oracle, Postgre SQL), and `Back quotes` (MySQL).

If the destination table on the database side is not exists and you want to create it on-the-fly, please choose <Create new table...> item of the drop down list, as well as the DBMS to be used - MS SQL Server, Postgre SQL, Visual FoxPro, MySQL, Oracle, or MS Access SQL:

Otherwise, if the data should be inserted into an existing table, please select the name of the table that you want to export to the server or import to the currently opened DBF file in the drop-down list, or type it in. The table will be shown in the Table Preview grid.

Only the fields that have a counterpart with the same name will be transferred. Here's an example. You have a DBF file with the field names Field1, Field2, and Field3. The table on the server consists of the fields Field1, Field2, and Field4. In this case, only Field1 and Field2 will be exported to (or imported from) the database table. Such identical field names are listed under the Table Preview grid. Any fields without same-name counterparts will be filled with NULLs. Any missing fields (in this case, Field4) will be shown, too.
At the bottom of the window, you can get a command line for the current transfer process. You can use that command line in batch mode via the command line interface or via .BAT files.

After preparing all necessary information, click the Import (or Export) button.

You can also export (or import) data to/from a DBF file. To do that, you can use MS OLE DB Provider for Visual FoxPro when creating a connection string.

Encrypt DBF file and Decrypt DBF file

This feature lets you protect your DBF data by encrypting it. Encryption is implemented using the Advanced Encryption Standard (AES, a.k.a. Rijndael) algorithm, with a 256-bit key.

The password is not stored anywhere. When you try to decrypt the file, the password that you enter will be used only for decrypting that file. If the password is correct, the DBF file header, structure, and data will be duly restored. Otherwise, the file will stay encrypted. It is virtually impossible to "crack" the encrypted file and extract the protected information.

If you execute the Encrypt command, the standard Save Dialog window will open. Enter the file name that you want to save to an encrypted file, and click the Save button.

After executing the Encrypt or Decrypt command, the password dialog window will open:

After entering the password and confirming it, click the OK button. When the encryption (or decryption) is complete, the appropriate information box will appear.

After encrypting a DBF file, make sure to delete all backups of the DBF file (with the BAK extension) and all backups of its Memo file (with the TBK extension), because these files are not encrypted and may contain the data that you want to protect.

Since DBF Commander Professional cannot open an encrypted file, you need to decrypt it first. If you invoke the Decrypt command, the standard Open Dialog window will appear. Select the file that you want to decrypt, and click Open. When the Save Dialog window appears, enter the name of the file to decrypt to.

After that, enter the password to decrypt. When the decryption is complete, a confirmation prompt will be displayed. If you want to open the file that has been decrypted, click Yes.

Convert DBF file

These menu items let you convert the current DBF file's internal data to a different codepage (from MS Windows to MS-DOS, and vice versa) and apply the encoding flag. DBF Commander supports a number of Code Pages.

The conversion applies to the current file. The BAK (backup) file will be created if the respective option is enabled. If the current DBF file's charset is of the MS Windows type, the conversion is only available to the MS-DOS type charset, and vice versa.

You can also convert a DBF file from ANSI to UTF-8, and from UTF-8 to ANSI. If the DBF file data is in the MS-DOS encoding, the conversion to UTF-8 is not allowed, because the translation would be incorrect and the information would be corrupted. To convert an MS-DOS file to UTF-8, first convert it to the Windows encoding.

Before converting a DBF file to UTF-8, please note the following:

In all conversion types:

• from the ANSI codepage to the MS-DOS codepage,
• from the MS-DOS codepage to the ANSI codepage,
• from the Windows ANSI encoding to the UTF-8 encoding,
• and from the UTF-8 encoding to the Windows ANSI encoding,

only the fields of Character and Memo types are converted.

Preview and print DBF file (Ctrl+P)

This command lets you preview a DBF table layout on a page:

The following commands are available in this window:

• Print – opens a dialog box for printing the DBF table.
• First, Previous, Next, and Last page – you can move from page to page. The number and count of the printable pages are shown in the status bar.
• Vertical and horizontal page orientation – please note that switching between vertical/horizontal may affect the page count.
• Apply row colors while printing – defines the odd/even row colors to be applied when printing.

Reopen (F5)

Refreshes the DBF file data without recreating its MDI form. You may want to refresh the data, for example, if you work with a file that has already been opened by another instance of DBF Commander Professional or by another user from a LAN source (or a virtual machine shared source). To retrieve the updated data, just press the F5 key.

Close (Alt+C) and Close All

These commands close the currently open file and all open DBF files, respectively.

Exit

Closes all open DBF files and DBF Commander Professional, and also deletes all temporary files and saves your SQL queries to the log file ("DBF Commander.log"). You can set the shortcut key (the Escape key) for this command via the Options dialog box .