DBF File

This menu item contains DBF file handling commands. They are listed below.

 

Create DBF file (Ctrl+N)

Opens the File Structure dialog box:

create dbf file

To create new DBF file you need to fill corresponding fields:

  • File Name - full file path and it's name. Click "..." button to invoke the Save Dialog. Then specify the file name and click Save.
  • File Type - new DBF file type. It may be "Visual FoxPro" or "dBase III plus".
  • Fields (<current field number> of <field count>) - the grid contains information about DBF file fields:
  • Name - field name (up to 10 characters). Duplicate field names are not allowed!
  • Type - field type. You can choose it from the drop-down list.
  • Width - field length (in bytes). If the value you specified is more than allowed, it'll be cut to maximum acceptable value.
  • Decimal - field length (in bytes). If the value you specified is more than allowed, it'll be cut to maximum acceptable value.
  • Null - allows or forbid null-values at the field. By default, null-values are acceptable.
It is recommends to use exactly Visual FoxPro DBFfile format because of the reasons:
  • The native DBF format of DBF Commander is "Visual FoxPro" (by default). It means, all temporary and new DBF files creates in Visual FoxPro format. So, to make DBF file dBase type it's need to do additional activities. Although, they executes automatically by DBF Commander, it's need more time and resources.
  • More stable work.
  • Visual FoxPro format supports more field types than dBase III plus:
    • Visual FoxPro - Character, Currency, Date, DateTime, Double, Float, General, Integer, Integer autoincrement, Logical, Memo, Numeric.
    • dBase III plus - Character, Date, Float, Numeric, Logical, Memo.

After you have defined needful fields, you can to move by the grid to do some changes. If your file contains integer autoincrement field, the according edit boxes will enabled:

  • Next Value - the beginning value of the autoincrement field. By default, is zero.
  • Step - while new records adding value at the field will be automatically incremented by Step. By default, is one.

Also, File Structure window contains additional operations toolbar:

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

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

For example:

CREATE TABLE "D:\Table1.dbf" (field1 C(10,0),field2 C(20,0))

Otherwise, if you change the structure of the DBF file, the SQL queries list to alter file structure will be generated founding your changes. So, the needful file will be altered and those queries added to main window's SQL Panel:

ALTER TABLE "D:\Table1.dbf" ALTER field1 C(15,0)
ALTER TABLE "D:\Table1.dbf" ALTER field2 C(25,0)
ALTER TABLE "D:\Table1.dbf" ADD field3 C(10,0)
ALTER TABLE "D:\Table1.dbf" ALTER field3 I(4,0)

Click Cancel button (or press Escape) will close File Structure window and discard all structure changes.

 

Open DBF file (Ctrl+O)

Shows the Open dialog box. Specify the DBF file you need and click Open. Also, you can select several files you need to open. All of them will be opened.

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

  • DBF Files (*.DBF) - Data Base File. Can be Visual FoxPro or dBase format.
  • Memo Files (*.FPT, *.DBT) - Visual FoxPro (*.FPT) and dBase (*.DBT) MEMO files. When you open that file, actually opens corresponding DBF file, whose memo-fields data stores in this FPT/DBT file.
  • All Files (*.*) - force DBF Commander to open non-DBF file. If it's header and structure is DBF- or dBase-like and it is correct - the file will be opened usually, as a DBF file. Otherwise, invokes "Not a table" error.

Also, to execute Open command you can:

  1. Click at main window's Tables List combo box, in case no one file have been opened yet.
  2. Select and drag-and-drop files to DBF Commander's window.


Next to Open tool button there is Recent Files submenu. You can open a recent file with click at it's item. The submenu stores up to 20 recently opened DBF files. And it's items sorted by the order files were opened.

 

DBF File structure (Ctrl+S)

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

As in case of creating DBF file, you can modify the structure of current file, change DBF-format (Visual FoxPro <-> dBase III plus), export or preview the structure.

After clicking OK button the changes will be applied. Click Cancel button (or press Escape) will close File Structure window and discard all structure changes.

If current DBF file contains deleted records, they'll be packed automatically while the file structure will be saved.

 

Import to DBF file (Ctrl+I)

Use this command to import data from one of the following formats:

  • XML Files (*.XML)
  • Comma Separated Files (*.CSV). In the dialog box appears type the delimiter to be used. By default, it is semicolon.

In the open dialog box appears select the source file (CSV or XML) you want to import from, in the save dialog box - the destination DBF file. It will be created automatically, based on field definitions stores in source file.

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

 

Export DBF file (Ctrl+E)

Use this command to export 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 appears type the delimiter to be used. By default, it is semicolon.

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

 

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

Use it to save current DBF file to another one. This feature is very useful if you want to save a SQL query result dataset to the DBF file.

 

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

This feature is intended to provide the data transfer between a server (under management of a DBMS) and a DBF file. The server, the database, and the table to be imported/exported are defines with the Connection String. If you have appropriate connection string - just paste it into the edit box, otherwise you can use the Build... button to create the new one.

In this case, the Data Link Properties standard dialog window appears. You'll need to select the Provider, choose the server, the database, specify login and password. Some of this parameters may be optional. It depends on the provider type. After you have state the parameters, click the Test Connection button to check the connection. If connection was successful, you can use the result connection string to import or export data from/to DBMS. Click OK button to close the Data Link Properties window.

Also, you can use an UDL (Universal Data Link) file instead of the connection string. Just click the corresponding button. Actually, an UDL file - it is a common text file, containing all of the information about connection - connection string. Thus, you can create an UDL file by oneself.

After you have specified the correct connection string, click the Connectbutton to create the connection to appropriate database. The drop down tables list will fills with table names of the database. This drop down list has 2 options:

  • Use double quotes - some of DBMS are needs the table name to be in double quotes
  • Show system tables - check it if you need to work with system tables of the database
  • Server-side cursor - some of databases (e.g. MySQL 5.1+) does not support fully-functional server-side cursors. That's why DBF Commander Professional uses client-side cursors while connecting to a database by default. But if you want to use server-side cursor - enable this option, then click Connect button again.
Please, be very careful while export data to any of system table - it can becomes the reason of the database malfunction!

Select the table name at the drop down list you want to export to server or import to the currently opened DBF file. The table will be shown at the Table Preview grid.

The only fields with equal names are participates in transfer. E.g., you have a DBF file with field names Field1, Field2, Field3. The table on the server consists of Field1, Field2, Field4 fields. In this case, Field1 and Field2 will be exported (or imported) to the database table only. Such identical field names are listed under the Table Preview grid. Different names field's values will filled with NULLs.

After you have prepared all required information, click Import (or Export) button.

Also, you can to export (or import) data to a DBF file. For that purpose, you can use MS OLE DB Provider for Visual FoxPro while constructing the connection string.

import (export) dbf file from (to) DBMS

 

Encrypt DBF file and Decrypt DBF file

This allows you to store your DBF data in secured state. Encryption realizes with Advanced Encryption Standard (AES, aka Rijndael) algorithm, with the key size of 256 bit.

The password is stored nowhere. When you try to decrypt the file, the provided password just applies to the file. If it is correct - the DBF file header, structure, and data will be restored properly. Otherwise, the file stays not decrypted. This provides impossibility to "crack" the file and get secured information.

After you've executed the Encrypt command, the standard Save Dialog window opens. Type the file name you'd like to save to your encrypted file, and click Save button.

In either of cases (Encrypt or Decrypt commands), the password dialog windows appears after:

Encrypt DBF file

After you have entered the password and confirmed it, click OK button. When the encryption (or decryption) process will have completed, the appropriate information box will appears.

The DBF file has been encrypted is a totality of unreadable binary data. You'll cannot open the encrypted DBF file with DBF Commander Professional, because of it's header and structure are encrypted too.

After you've encrypted a DBF file, please ensure, there are no neither backup of the DBF file (BAK-extension) has been encrypted nor backup of the Memo-file (TBK-extension), if any. This remain files may contain the encrypted data in unencrypted state.

Since the encrypted file cannot be opened with DBF Commander Professional, you need to decrypt this file. On the Decrypt command execution - the standard Open Dialog window appears. Choose a file you want to decrypt, and click Open. Then, in the Save Dialog window appears, enter the file name you need to decrypt to.

After that, provide the password to decrypt. When the decryption is completed, there will be the confirmation. Click Yes, if you want to open the file just have been decrypted.

 

Convert DBF file

These menu items intends for converting current DBF file internal data to necessary characters set (from MS Windows to MS-DOS, and vice versa) and applying the encoding flag. There are DBF Commander supported Code Pages.

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

Also, there is a possibility to convert DBF file from ANSI to UTF-8 encoding, and UTF-8 to ANSI. If the DBF file data is in MS-DOS-encoding, the conversion to UTF-8 encoding is not allowed, because the translation would be incorrect and the information would be corrupted. To convert MS-DOS file to UTF-8, at first convert the file to Windows encoding.

Before convert DBF file to the UTF-8encoding, please note:

Character in UTF-8 encoding occupies more bytes than in ASCII or ANSI. Thus, to not to lose the data please ensure, your character fields width is more than the length of the data it contains, at least in 2 times.

In all the conversion types

  • from ANSI code page to MS-DOS code page
  • from MS-DOS code page to ANSI code page
  • from Windows ANSI encoding to UTF-8 encoding
  • and from UTF-8 encoding to Windows ANSI encoding,

the only fields of Character and Memo types participates in the conversion.

 

Preview and print DBF file (Ctrl+P)

This command destined for preview DBF table layout at the paper page:

print dbf file

The following commands are available at this window:

  • Print - the appropriate dialog box appears to print the DBF table.
  • First, Previous, Next and Last page - to move by all of the pages. Number and count of the printable pages shows at the status bar.
  • Vertical and horizontal page orientation (switching between vertical/horizontal may affect on page count).
  • Apply row colors while printing - defines odd/even row colors will applying while printing.
Deleted rows are printing with it's light-red color in anyway.

 

Reopen (F5)

Refreshes the DBF file data without recreating it's MDI form. For example, if you work with file, which is already opened file by another instance of the DBF Commander Professional, or by another user from a LAN source (or a virtual machine shared source). To retrieve the new data just press F5 key.

 

Close (Alt+C) and Close All

These commands closes current opened file and all opened DBF files, respectively.

 

Exit

Close all of the opened DBF files and DBF Commander Professional, as well as delete temporary files and save your SQL queries to the log-file ("DBF Commander.log"). At Options dialog box you can set the shortcut (Escape button) for this command.