A free universal DBF editor that allows you to open existing and create new databases. It has a very small size, can be run from a flash drive and at the same time has many advanced tools for working with DBF files, among which there is even support for SQL queries!

Screenshot gallery

Usually on the site we cover programs that will be of interest to a wide range of readers, but today the case is not quite ordinary :). I used to work as a kind of "computer specialist" in several government offices and there I often had to deal with various programs based on FoxPro ...

The main problem with all these applications was that a simple user could easily screw up the database so that it could not be opened later. regular means, so I had to pervert in order to bring it back to life (which did not always work out, given the "abilities" of "wild users" :)).

And now, a few years later, as I no longer work there, they sent us an email asking us to add a new free program for editing DBF databases, which has a simple name - Sdbf. Let's talk about her :)

Comparison with a paid analogue

Sdbf, despite its portability (it can work from a flash drive!) and small size, is a fairly advanced database editor that allows you to create, edit and export any DBF format databases, from the xBaseIII specification to the modern xVisualFoxPro! Let's compare the functionality of Sdbf with the capabilities of one of the most advanced editors of this kind, DBF Commander Professional:

From the table above, we can see that the programs differ slightly in functionality, but Sdbf, firstly, is portable (which is usually important, since every computer master prefers to carry a set of necessary programs on a flash drive), and secondly, it is completely free!

First run of Sdbf

To run the program, just unpack it from the downloaded archive to any location and open the resulting EXE file. An empty window of the following type will appear in front of us:

Drag & Drop, alas, is not supported, so to get started we need to call the "File" menu and there already select one of the two available actions: "Create" a new database or "Open" an existing one. Let's open the existing database:

We will see the contents of the selected database in the form of a table. The "zero" line displays the names of the fields, and starting from the first - the content itself. Below the content at the bottom is the toolbar and status bar.

The latter displays quite a lot of useful service information, including the number of records in the database, encoding, creation date and automatically defined format. Of the Sdbf formats, it does not support only early versions xBase (I and II) and allows you to open and create DBF files of the following types:

• xBase III - VII;
• xClipper;
• xFoxPro;
• xVisualFoxPro.

Data search and filtering tools

The Sdbf program allows you to directly edit any cell of an open database, however, desired cells you still need to find ... If the base is small and unpretentious, then this can be done quite quickly and manually. However, if there are several tens or even hundreds of records, then it can be quite difficult to search.

But this is not such a problem, since Sdbf has several tools at once that allow you to filter out unnecessary data and display only what you need!

These tools are found on the bottom toolbar. Here, at the beginning there are 8 navigation buttons that allow you to move around the database (arrows), add / delete entries, and also confirm or cancel changes. The functions we need begin with the ninth button - "Search":

When the button is activated, a small window with a search form appears in front of us. We need to specify the text to be found and select the field that will be searched from the drop-down list. Now we press the "Find next" button and the program will automatically select the line following the current selection, which contains the searched text. Pressing the button again will highlight the line below the current one, which contains the same search data, and so on.

Sometimes in databases there is non-standard cell formatting: extra spaces, tabs and other characters that are not visually displayed, but affect the search result. If you encounter such a case, then for the function to work normally, it will be enough for you to uncheck the "According to the format" checkbox in the lower central part of the search box and the searched strings will begin to be displayed.

The search function is useful when we need to quickly find single occurrences of specific data. But there are times when it would be more convenient to display several rows at once, which contain only certain information. AT this case the second function will help us (the button of which comes immediately after the search button) - "Filter":

To enable filtering, we first need to correctly compose a request and enter it in a specially designated field (immediately after the button in the central part of the toolbar). The principle of making a request is simple, but not entirely obvious. We need to first enter the name of the field by which we want to filter the database table, and then equate the filter value to the specific text by which we need to find all rows.

We take the value in single quotes, after which we press the "Filter" button itself (it becomes clamped) and we get a table containing data only with the values ​​\u200b\u200bdefined for us in the specified fields (in the example, we filtered all rows with the value "U.S.A" in the "Country" field (the case of specifying the name does not matter)). You can return the table to its original form simply by pressing the "Filter" button again (it becomes pressed again).

The filtering field can contain the simplest conditions "and" (to refine the query on several fields) and "or" (for alternative selection of data from different fields). Unfortunately, Sdbf does not support exclusive queries (such as "not"), but we can get around this limitation in another way, which will be discussed below.

By the way, in order not to manually enter a filtering request, you can use the following trick: select any entry in the field by which you will filter and press the Alt + F key combination (but not F4 :))). The request will be automatically generated, and the value will contain the "*" sign, which is a search mask and matches any number of any characters.

Alas, this is the only mask type that can be used in Sdbf and, even sadder, one request can contain only one mask :(. Therefore, if you need to filter the same field by several parameters, you will have to use the "and" operator (quick query generation by pressing "CTRL+ALT+F") or "or" ("SHIFT+ALT+F").

Executing SQL queries against database tables

Search and filtering is undoubtedly good, but not always convenient. When the number of records is large, we can easily lose sight of the data we need when parsing tables. However, in this case, Sdbf has a great feature - support for SQL queries!

With these queries in Sdbf we can:

1. Dynamically format our table, creating a selection only for certain required fields (SELECT and SELECT TOP statements);
2. Copy data from one database to another (INSERT FROM and INSERT INTO);
3. Change and delete the contents of certain lines (UPDATE and DELETE, respectively);
4. Group parallel requests (UNION);
5. form pivot tables(PIVOT).

To start working with queries, we need to click the "SQL Query" button at the right end of the toolbar. However, the result of such an action will be somewhat discouraging: the contents of the just edited database will disappear, and another empty window with two fields will appear on top of the working window (at the top is the data output field, and at the bottom is the input field).

You should not be afraid :). You just need to immediately enter the desired SQL query in the lower field with the name desired base data. If you do not remember this name, you can activate sidebar Sdbf (by clicking on the gray vertical bar on the right), which lists all the bases that you have opened before:

Also, to speed up the work, it is desirable to remember the hot keys, which can be viewed by calling the help by pressing the F1 key. There are only three of them, but they can significantly speed up the process of creating the right queries.

So, the combination "CTRL + Space" - will display a pop-up window with a list of available database names (so as not to look at the sidebar), "CTRL + F" - will display a list of functions for the current operator, and "CTRL + Enter" will start the query execution process . In addition, if you right-click on the input field (call the context menu), then we will get a list of operators supported by Sdbf.

Let's go back to our previous screenshot and look at simple query which is compiled there. It is based on the "SELECT" statement, which means it returns a selection to us. The selection parameter is "*", which means the output of any data, however, we could specify there, separated by commas, the names of any fields of the database table.

The sample we looked at above is the simplest, but Sdbf allows you to run more complex queries. In the next screenshot, we will see a selection, a dynamically generated table consisting of fields that contain the names of customers, the city in which they live and the country. Moreover, customers are filtered by country of residence (U.S.A) and the results are displayed in alphabetical order:

If you disassemble given request, then we will see the method of selection already mentioned by me by the fields specified with a comma after SELECT statement. Further, the same indication of the database name, but now the request does not end there.

The next step is to list the functions of the main operator that will have to be performed as a result of processing the request. Here are two of them.

The first - "where" is similar to the filter we discussed earlier, and performs a similar action - it allows you to select only those records that match the condition.

The second function - "order by" is responsible for sorting the results by one of the fields (in this case, by the field with the client's name - "NAME") in alphabetical ("asc" parameter) or inverted ("desc") order.

Naturally, the above example is also quite primitive, but it reflects the general essence of creating and processing SQL queries. If you want to learn more about them, I advise you to read the manuals here: http://dimonchik.com/insert.html or https://www.sql.ru/articles/articles.aspx?g=SQL&s=0 .

By the way, the advantage of Sdbf is the ability to export the resulting table as a new database or as an HTML, RTF, or CSV file! To do this, just call the context menu of the table display field and select the appropriate item.

Additional functions from the menu bar

We have dealt with the main features of Sdbf, but this does not end there. A number of useful and even unique functions are hidden in the menu bar, the same one that we used to open the database :) So, many useful features(which sometimes allow you to do without SQL queries) you can use the "Table" menu:

Among other "useful things" such as changing the encoding and merging databases, at the very bottom we can find the "Change structure" item. This item calls up an additional window in which we can completely change all the fields of the open table, add new ones, or delete sections that are no longer needed (the same window opens when creating a database from scratch).

Specifically, we can change the order of fields, their names, types, sizes (number of characters in a cell) and precision (bit depth - usually set automatically and depends on the field type).

The only thing to remember when editing (and especially creating) databases is that each type of database has its own sets of field types and they often (if you do not take into account the main N, C, D) are different. For example, xClipper databases do not support image fields (P, B), and xBase up to the seventh version does not support auto-increment fields (+). That is, you must always remember about compatibility :).

Since we're looking at the menu bar, don't forget to take a look at the Field menu:

There are only two functions here, but they can be very useful! Especially the first one - "Replace". It allows you to quickly automatic mode replace values ​​in the entire column of the specified field, or specific words and symbols throughout the database!

The "Calculate" function is an analogue of Excel's auto-calculation formulas and gives us the opportunity to perform such mathematical and statistical operations as finding the field's autosum, calculating the arithmetic mean, as well as the maximums and minimums of values. Naturally, to perform calculations, field types must be numeric (N, F, I, O, B).

The last feature, which is not so obvious but can be useful, is the built-in hex editor:

It allows you to edit the data of any cell. To do this, just select the content that you want to change and in context menu select the last item - "Open in hex". Everything - you can edit and save or immediately print the result!

Advantages and disadvantages of the program

• portability and small size;
• support for almost all types of DBF files;
• the ability to create databases from scratch;
• support for SQL queries;
• built-in system for searching, filtering and editing data.

• does not allow to specify more than one mask in the filter;
• there is no undo function.

findings

The Sdbf program, with its modest size and system requirements allows you to do with DBF databases, if not everything, then very, very much ... And the implementation of support for SQL queries in general allows you to take the work of editing the database to a new level! With their help, you can easily compensate for many shortcomings in the work through the graphical interface.

The biggest disappointment, in my opinion, was the absence of the most trivial function “Undo last action” in the world of “CTRL + Z”: (The most annoying thing is that due to oversight or ignorance, the user can, for example, accidentally apply autocorrect throughout the field, and it will be impossible to return the data :(

Behind this, we can state that the program is functionally more than successful, but before working with it, always follow the ancient admin rule - CREATE BACKUPS! And you will be happy :)

P.S. It is allowed to freely copy and quote this article, provided that an open active link to the source is indicated and the authorship of Ruslan Tertyshny is preserved.

DBF is a widely used data storage format that appeared in the 80s of the last century. For the first time, the format was used in the dBase DBMS family. Due to the popularity and widespread use of dBase, many dBase-like software products, collectively called xBase. Despite the significant age of the format, it is still widely used. This article discusses how to work with DBF from 1C:Enterprise.

1C:Enterprise uses a special program object, xBase, to work with DBF files (dBase III version). Working with this object usually does not cause difficulties.

Attention!

When working with DBF files, remember that the file name must satisfy constraint 8.3.

Attention!

The xBase object is available on both the client side and the server side. You should think over the client-server interaction when solving each specific problem.

Reading a .dbf file

Reading data from DBF file produced in several successive steps:

1. Creating an XBase object;
2. Opening a file;
3. Sequential enumeration of all lines of the file and reading field values;
4. Closing a file.