Popular for many years among engineers, designers and economists, DBF files typically contain databases created in the dBASE application and similar programs. This format was invented back in the 80s of the last century, but it is still supported by various services and applications for working with databases, including xBase, Visual FoxPro, Clipper, FoxBase and the entire line of 1C products. To view a DBF file on a Windows computer, you will need a special program or application. Today we will look at 5 programs that are best suited for working with the DBF format:
Ways to open DBF files in Excel
You should know that there are several modifications in the DBF format itself:
- dBase II;
- dBase III;
- dBase IV;
- FoxPro and others
The type of document also affects the correctness of its opening by programs. But it should be noted that Excel supports correct work with almost all types of DBF files.
It should be said that in most cases Excel copes with opening this format quite successfully, that is, it opens this document in the same way as this program would open, for example, its “native” xls format. But Excel stopped saving files in DBF format using standard tools after Excel 2007. However, this is a topic for a separate lesson.
Lesson: How to convert Excel to DBF
Method 1: launch through the file open window
One of the simplest and most intuitive options for opening documents with the DBF extension in Excel is to launch them through the file open window.
- Launch Excel and go to the “File” tab.
After entering the above tab, click on the “Open” item in the menu located on the left side of the window.
A standard document opening window opens. We move to the directory on the hard drive or removable media where the document that needs to be opened is located. In the lower right part of the window, in the field for switching file extensions, set the switch to the “dBase files (*.dbf)” or “All files (*.*)” position. This is a very important point. Many users cannot open the file simply because they do not comply with this requirement and the element with the specified extension is not visible to them. After this, documents in DBF format should be displayed in the window, if they are present in this directory. Select the document you want to launch and click on the “Open” button in the lower right corner of the window.
After the last action, the selected DBF document will be launched in Excel on the worksheet.
Method 2: Open by double clicking on the file
Another popular way to open documents is to launch them by double-clicking the left mouse button on the corresponding file. But the fact is that by default, unless specifically specified in the system settings, the Excel program is not associated with the DBF extension. Therefore, without additional manipulations in this way, the file cannot be opened. Let's see how this can be done.
- So, double-click with the left mouse button on the DBF file that we want to open.
- If on this computer in the system settings the DBF format is not associated with any program, then a window will open that will inform you that the file could not be opened. It will offer options for action:
- Search for matches on the Internet;
Selecting a program from the list of installed programs.
- When you select any of these three options, the file open window opens. Again, if the name “Microsoft Excel” is present in the list of recommended programs at the top of the window, then click on it, and otherwise click on the “Browse...” button at the bottom of the window.
- In case of the last action, the “Open with...” window opens in the directory where the programs are located on the computer in the form of Explorer. In it you need to go to the folder that contains the Excel program launch file. The exact path to this folder depends on the version of Excel that you have installed, or more precisely on the version of the Microsoft Office package. A general path pattern would look like this:
C:Program FilesMicrosoft OfficeOffice#Instead of the “#” symbol, you need to substitute the version number of your office product. So for Excel 2010 it will be number “14”, and the exact path to the folder will look like this:
C:Program FilesMicrosoft OfficeOffice14
For Excel 2007 the number will be “12”, for Excel 2013 – “15”, for Excel 2016 – “16”.
So, move to the directory indicated above and look for a file called “EXCEL.EXE”. If you do not have extension display running on your system, then its name will simply look like “EXCEL”. Select this name and click on the “Open” button.
- After this, we are automatically transferred again to the program selection window. This time the name “Microsoft Office” will definitely be displayed here. If the user wants this application to always open DBF documents by double-clicking on them by default, then you need to make sure that the “Use the selected program for all files of this type” option is checked. If you plan to open a DBF document in Excel only once, and then intend to open this type of file in another program, then, on the contrary, this checkbox should be unchecked. After all the specified settings are completed, click on the “OK” button.
- After this, the DBF document will be launched in Excel, and if the user has checked the appropriate place in the program selection window, then now files of this extension will be opened in Excel automatically after double-clicking on them with the left mouse button.
Since it is assumed that the Microsoft Excel spreadsheet processor is already installed, we move the switch to the second position and click on the “OK” button at the bottom of the window.
If this extension is already associated with another program, but we want to run it in Excel, then we do things a little differently. Right-click on the document name. The context menu launches. Select the “Open with” option in it. Another list opens. If it contains the name “Microsoft Excel”, then click on it, but if you do not find such a name, then go to the item “Select a program...”.
There is another option. Right-click on the document name. In the list that opens after the last action, select the “Properties” item.
In the “Properties” window that opens, move to the “General” tab if the launch occurred in some other tab. Next to the “Application” parameter, click on the “Change...” button.
As you can see, opening DBF files in Excel is quite simple. But, unfortunately, many novice users get confused and don’t know how to do this. For example, they do not think to set the appropriate format in the window for opening a document through the Excel interface. An even greater difficulty for some users is opening DBF documents by double-clicking the left mouse button, since this requires changing some system settings through the program selection window. We are glad that we were able to help you solve the problem. Describe what didn't work for you. Our specialists will try to answer as quickly as possible.
Did this article help you?
Users who frequently work with databases may encounter a file in the .dbf format. Since there is sometimes a lack of publicly available information about this extension, users often do not know what the file is. How can I view it, and how can I convert it to other file types? Below we will look at what kind of file this is and how to open the DBF extension.
Content
Data file header structure for a dBASE IV 2.0 table.
| File structure | ||
| Byte | Content | Description |
| 0 | 1st byte | dBASE IV file control: bits 0-2 indicate the version number, bit 3 - presence of a dBASE IV MEMO file, bits 4-6 - presence of an SQL table, bit 7 - presence of any MEMO file (or dBASE III Plus, or dBASE IV ) |
| 1-3 | 3 bytes | Date of last update in YYMMDD format |
| 4-7 | 32-bit number | Number of records in the table |
| 8-9 | 16-bit number | Number of bytes occupied by the header |
| 10-11 | 16-bit number | Number of bytes occupied by a record |
| 12-13 | 2 bytes | Reserved area, filled with zeros |
| 14 | 1 byte | Flag indicating the presence of an pending transaction |
| 15 | 1 byte | Encoding flag |
| 16-27 | 12 bytes | Reserved area for multi-user use dBASE IV |
| 28 | 1 byte | MDX file presence flag: 01H - file present, 00H - file absent |
| 29 | 1 byte | Language driver ID |
| 30-31 | 2 bytes | Reserved area, filled with zeros |
| 32-n* | 32 bytes each | An array with field descriptions (the structure of each such description is shown below) |
| n+1 | 1 byte | 0DH as a terminator |
n is the last byte of the array with field descriptions. The size of the array depends on the number of fields in the table file.
| Table field description | ||
| Byte | Content | Description |
| 0-10 | 11 bytes | Field name in ASCII (filled with zeros). |
| 11 | 1 byte | Field type in ASCII (C, D, F, L, M or N) |
| 12-15 | 4 bytes | Reserved area |
| 16 | 1 byte | Field size in binary format |
| 17 | 1 byte | Field serial number in binary format |
| 18-19 | 2 bytes | Reserved area |
| 20 | 1 byte | Workspace ID |
| 21-30 | 10 bytes | Reserved area |
| 31 | 1 byte | MDX field flag: 01H if the field has an index mark in the MDX file, 00H if it does not. |
What is DBF?
File with .DBF
is a database file used by the dBASE data management system.
The data is stored in the specified file as an array with a set of records and fields. DBP files are used in the work of the “dBase” database tool.
Since in this case the file structure is quite simple, this format began to be used in the early stages, when software tools for working with databases just began to appear. DBP was positioned as a standard format for structuring the data accumulated by the system.
DBF is a standard tool for structuring data
Not all DBP files are the same. Since its inception, the DBF format has undergone quite a number of changes. While the format is backwards compatible with dBase III+, you will still need to ensure that your software can open and play your DBF files correctly.
Esri's ArcInfo tool also stores data in dbf files, but in this case it is called a "fileform attribute." These files use the dBASE format to store the shape attributes they need.
The ArcInfo tool also uses .dbf files to store data
FoxPro Table files also use DBF when working with Microsoft Visual FoxPro databases.
Data file header structure for dBASE 5.0 table under DOS.
| Table file header | ||
| Byte | Content | Description |
| 0 | 1st byte | Control of the dBASE file under Windows: bits 0-2 indicate the version number, bit 3 - the presence of a dBASE IV or dBASE MEMO file under Windows, bits 4-6 - the presence of a dBASE IV SQL table, bit 7 - the presence of any .DBT MEMO file (dBASE III Plus, dBASE IV, or dBASE table MEMO file on Windows) |
| 1-3 | 3 bytes | Date of last update in YYMMDD format |
| 4-7 | 32-bit number | Number of records in the table |
| 8-9 | 16-bit number | Number of bytes occupied by the header |
| 10-11 | 16-bit number | Number of bytes occupied by a record |
| 12-13 | 2 bytes | Reserved area, filled with zeros |
| 14 | 1 byte | Flag indicating the presence of an pending transaction |
| 15 | 1 byte | Encoding flag |
| 16-27 | 12 bytes | Reserved area for multi-user use |
| 28 | 1 byte | MDX file presence flag: 01H - file present, 00H - file absent |
| 29 | 1 byte | Language driver ID |
| 30-31 | 2 bytes | Reserved area, filled with zeros |
| 32-n* | 32 bytes each | An array with field descriptions (the structure of each such description is shown below) |
| n+1 | 1 byte | 0DH as a terminator |
n is the last byte of the array with field descriptions. The size of the array depends on the number of fields in the table file.
| Table field description | ||
| Byte | Content | Description |
| 0-10 | 11 bytes | Field name in ASCII (filled with zeros). |
| 11 | 1 byte | Field type in ASCII (B, C, D, F, G, L, M or N) |
| 12-15 | 4 bytes | Reserved area |
| 16 | 1 byte | Field size in binary format |
| 17 | 1 byte | Field serial number in binary format |
| 18-19 | 2 bytes | Reserved area |
| 20 | 1 byte | Workspace ID |
| 21-30 | 10 bytes | Reserved area |
| 31 | 1 byte | MDX field flag: 01H if the field has an index mark in the MDX file, 00H if it does not. |
How to open files with the DBF extension
The main program that works with .dbf files is “dBASE”. This file format is also supported in other databases and database applications. For example, these are “Microsoft Access”, “LibreOffice Calc”, “OpenOffice Calc”, “Microsoft Excel”, “HiBase Group DBF Viewer” “Quattro Pro” (Corel WordPerfect Office element), “DBFView”, “Astersoft DBF Manager", and a number of others. These tools will help you easily launch a DBF file.
Use the popular “Microsoft Access” program to open .dbf files
It is also recommended to save the Microsoft Works database files in the dBASE in question if you later want to open them in Microsoft Excel.
For Mac and Linux, “GTK DBF Editor” is useful, as well as “NeoOffice” (Mac), “multisoft FlagShip” (Linux) and also known to many “OpenOffice”.
For Mac use “GTKDBF Editor”
The Microsoft Visual FoxPro database system development environment (now discontinued) can also run DBP files in the database file format or as a FoxPro table.
The required file will also help you open “FoxPro”
Related programs
Now let's look at the DBF file itself. How can I open it without using standard office suite programs? Yes, by the same utilities included in similar software products.
For example, it could be the same Open Office, Lotus, etc. In principle, there is not much difference here, since the only thing that matters is that the program used is capable of working with tabular electronic data. Reading the DBF format is done exactly the same in all cases. By the way, this does not depend on the developer of the office suite, since everyone has support for this type of data, including even platforms like Oracle.
Sources
- https://TwNews.ru/soft-info/format-dbf.html
- https://MyComp.su/obzory/format-dbf.html
- https://GeshTV.ru/programmy/dbf-fajl.html
- https://TexTochka.ru/soft/prosmotr-dbf.html
[collapse]
Performing DBF file conversion
Most of the programs described above, in addition to being able to open or edit a DBP file, can also convert this file to other formats. For example, MS Excel can save DBF in any format supported by this program, for example, CSV, XLS, XLSX, PDF and others.
Convert from DBF to XLS
The famous "HiBase Group" also has a converter that converts DBF file to CSV, Excel formats such as XLS and XLSX, standard text, HTML, XML, SQL, PRG, SDF, RTF, TSV and others.
DBF Converter can only export fifty records in its free trial version. You may prefer the paid version if you need to export more files.
A DBF converter will also come in handy
The dbfUtilities tool transforms dbf files into XML, JSON, CSV and Excel formats. It works using the "dbfExport" functionality, which is included in the "dbfUtilities" package.
“dbfUtilities” will also help in converting the format we need.
You can also convert DBF online using DBF Converter. The latter supports file export to TXT, CSV and HTML.
This will come in handy: MPP extension - how to open it.
Table entries
Records in a table file are located immediately after the table header. The record data is preceded by a byte indicating that the record was deleted: the value 20h (space) indicates that the record was not deleted, the value 2Ah (asterisk) indicates the record was deleted. Fields are packed into records without field separators or record terminators. The end of the file is marked with a single byte (with an EOF marker), the OEM code of which corresponds to the value 26 (1Ah).
| Valid data type for dBASE tables | ||
| Type designation | Type | Range of values |
| C | Symbols | All OEM code page characters |
| D | date | Numbers and separator character for month, day and year (internal recording format is 8 digits in YYYYMMDD format) |
| F | Binary floating point numbers | — . 0 1 2 3 4 5 6 7 8 9 |
| N | Decimal conversion of binary numbers | — . 0 1 2 3 4 5 6 7 8 9 |
| L | Logical | ? Y y N n T t F f (? - not initialized) |
| M | Memo | All OEM code page characters (internal recording format is 10 digits containing the .DBT block number) |
Opening process
You can open DBF format files in several programs. In addition to opening the file in these programs, you can also edit it there.
Program 1: DBF Commander
This is a paid program that allows you to open DBF files and carry out primitive manipulations with them if necessary. It has a trial period, during which the program's capabilities are not limited in any way. The program also has Russian language and a user-friendly interface.
Instructions for opening files in DBF Commander:
- Open the program and in the top control panel click on the folder icon. You can also use the combination Ctrl+O.
An Explorer window will appear where you need to select a file to open.
Now you can edit the table and see its contents.
Program 2: DBF Viewer Plus
This is a slightly simpler program that has a similar interface to the previous software. However, it is completely free and does not require installation. The functionality is almost similar to the previous software.
The step-by-step instructions for this program are as follows:
- Launch the program and in the top control panel, click on the open folder icon labeled “Open”. For speed, you can again use the Ctrl+O key combination.
Open the desired file through Explorer.
You can view and edit the contents of the file.
Program 3: DBF Viewer 2000
This is a program with a very simple interface, translated into Russian. It has wide functionality for working with DBF files. Here you can process files larger than 2GB. However, the software is paid, however, it has a trial period during which you can use all the functions without restrictions.
Instructions for opening files in this program are as follows:
- In the Top Control Panel, click on the open folder icon. Again, you can use the keyboard shortcut to quickly launch Explorer - Ctrl+O.
Select the desired file in Explorer and open it.
You can now view and edit the document.
Program 4: CDBF
This solution provides very broad functionality for working with databases and their derivative files. In addition to editing database files, you can create various reports and supporting documents. The interface can be translated into Russian, and the functionality can be expanded using third-party plugins from the network. CDBF is a paid program, but it has both a demo period and a stripped-down free version, the functionality of which will be sufficient for opening DBF files.
Instructions:
- Click on the open folder icon in the top panel or use the Ctrl+O key combination.
In Explorer, select and open the desired file.
You can view or edit the table.
If problems arise
What can be done if the database flatly refuses to open in Excel and Access is not installed? How to open DBF in this case? Don't rush to look for other software. If MS Office is installed on your computer in a minimal configuration, then Excel may not have a special add-in for working with our format. Therefore, you need to first search the network and download the required library to your computer.
The library files are archived, and there are also detailed installation instructions. A user who is still puzzled by the question of how to open DBF needs to perform a few simple steps by copying the files to the folders specified in the instructions. After this, you should go to the “Add-Ins” section in Excel and check the box next to the “Add-in for working with DBP” item.
Macro (add-on) XlsToDBF and its description
Allows:
- set a custom name for the output DBF file
- set the type and size for each field at the user's discretion
- set the code page (WINDOWS-1251 or DOS-866) for the output DBF file
- set the MAC parameter to output the DBF file correctly on Macintosh
In order for the add-in to understand what is required of it and create the required DBF file, you must specify the type, size, field names for the final DBF file, as well as its name.
The add-in can work with four main DBF field formats: Character (Cnnn), Numeric (Nnn or Nnn.n), Date (D) and Logical (L), i.e. fields such as General, Memo, Currency, etc. it will not be able to create in the final file. Actually, the native converter of previous versions of Excel had the same limitation.
For example, we use the Price.xls file from the archive, Sheet: Price. There is an EXCEL table that needs to be saved in a DBF file.
In the final DBF file we assign the necessary field names and formats so that:
- The Identifier field had an integer type (i.e. Numeric) with a width of 14 characters and was called ID.
- The Name field is a string type (Character) 100 characters wide with the name NAME
- Unit of measurement field - string, 20 characters, named ONE_NAME
- The Price field is a numeric type (Numeric) 14 characters wide with two decimal places with the name PRICE
- The Include in price field is a logical type (Logical) with the name L_PRICE
- The Update Date field had a Date type called D_UPDATE
We also want to save the final DBF file with the name PRICE.DBF in the DOS-866 Code Page.
In order to get the expected result, you need to change the table header as follows (Price.xls file from the archive, Sheet: Price_pre).
The first cell of the first row, the exported area, contains the name of the destination file. The extension in the file name does not affect the conversion process, i.e. The final file will in any case have a DBF (dBASE) structure.
The second cell (B1) tells the add-in macro in which code page to save the DBF file. If the cell value (B1) is different from CP866 or not specified at all, then the final DBF file will be saved in the WINDOWS CP1251 code page.
The second line describes the field formats that we want in the final file. I would like to note here, because... Many people are confused that a number format with a fractional decimal part, for example N14.2, is interpreted as follows: the field width is 14 characters, including the decimal point and 2 characters after it. Those. 11 whole characters, a decimal point and 2 characters after it, rather than 14 whole characters and 2 after a period.
The third line describes the names of the fields (maximum 10 characters). I think there is nothing more to comment here.
Starting from the fourth line and until the end of the continuous area, the data is located. It is desirable that the data matches the specified type. I marked in red those fields in which the values cannot be converted correctly. In such cases, the add-in sets them to default values. For example, the value YES cannot be converted to a boolean type. Therefore, this field will be set to FALSE (F) in the final DBF file. In this example, the same will happen with dates marked in red (they will be empty in the DBF file).
In the macro of the Converter.xlsm file, these operations are written in VBA code, the macro is “Output_to_dbf()”
Running the XlsToDBF add-in macro
After we have prepared the data for uploading, we can run the add-in macro. To do this, select any cell in the data or header area and click. In the “Macro” dialog box, in the “Macro Name:” field, you must MANUALLY enter XlsToDBF (case does not matter) and click <Run>. If everything was done correctly at the previous stage, the DBF file will be saved with the name PRICE.DBF in the same folder where the source Excel workbook is located. If the book is new and has not been saved, the DBF file will be saved in the default folder (most likely in the “My Documents” folder). Be careful: when you run the macro again, the DBF file will be overwritten without any warnings.
If you are satisfied with the work of the add-in, and you expect to use it often, then for convenience, create a custom button on the ribbon and assign the XlsToDBF macro to it.
Monday, October 27, 2014
How to force Excel 2007, 2010 to save to dbf. (page 1) - howto - how to solve problems - “the real edge of the Russian land” Far East - forums (18+)
Due to the fact that starting from MS Office 2007, support for reading and saving sheets in the DBF format has been discontinued, meanwhile, in many organizations in Russia this connection between Excel and dbf is still necessary. Databases in the dbf format are still supported by the popular accounting program 1C; many organizations also still use Foxpro solutions. There are several ways to get around this problem. 1 METHOD 1. Open MS Access 2010 or MS Access 2007.
2. Create a new database by clicking “New Database”.
3. On the “External Data” tab, click the “Excel” button and import data from an Excel file. 4. Select a file and click OK. 5. Click “Next”. 6. Check the “First row contains column headers” checkbox and click “Next”. 7. Check the data type in the columns, if necessary, change the type and click “Next”. 8. Check the “Do not create a key” checkbox and click “Next”. 9. Set the table name and click “Finish”. 10. Click the “Close” button. 11. On the “External Data” tab in the “Export” tab, click “Advanced” and select “dBase File”.
12. Select the dBase version and click OK.
13. Click the “Close” button.
14. A dbf table has appeared in the same directory where the Excel file is located.
2 METHOD Let's use a package that compensates for Microsoft's conscious decision to stop supporting DBF starting with MS Office 2007, 2010. 1. Download the archive containing the library zjvcst.dll and the add-in Dbf_Operations.xla>>>
%SYSTEMROOT% folder (C:Windows) .
3. Copy from the archive DBF_OPERATIONS.XLA File in C: Program Filesmicrosoft OfficeOfficelibory for MS Office 97 in C: Program Filesmicrosoft OfficeOffice11Library for MS Office 2003 in C: Program Files Icrosoft OfficeOffice12Library for MS Office 2007 in C: Program Filesmicrosoft OfficeOffice14Library for MS Office 2010
4. Open Excel.
5. Tab “File” -> “Options”.
6. “Add-ons” tab -> “Management:” click the “Go” button. ". 7. Check the “Add-in for working with DBF” checkbox and click “OK”.
8. In Excel, in the “Add-Ins” tab, three items will appear for working with DBF.
Read DBF into the current sheet starting from the selected cell.
Read DBF into the newly created workbook. Save selected cells in DBF format. Post's attachments
Library.rar 48.13 kB,
4449
times. uploaded from 2013-05-06
