How to create dbf excel file. Converting data from Microsoft Excel to DBF format

DBF is a popular format for storing and exchanging data between various programs, and primarily between applications that serve databases and spreadsheets. Although it has become obsolete, it continues to be in demand in various fields. For example, accounting programs continue to actively work with it, and regulatory and government bodies accept a significant part of reports in this format.

But, unfortunately, Excel, starting with the version of Excel 2007, has stopped full support for the specified format. Now, in this program, you can only view the contents of a DBF file, and you will not be able to save data with the specified extension by the built-in application tools. Fortunately, there are other options for converting data from Excel programs in the format we need. Let's see how this can be done.

In Excel 2003 and in earlier versions of this program, you could save data in DBF (dBase) format in a standard way. To do this, you had to click on the item "File" in the horizontal menu of the application, and then in the list that opens, select the position "Save as…"... In the opened save window from the list, it was required to select the name of the desired format and click on the button "Save".

But, unfortunately, starting with the version of Excel 2007, Microsoft developers considered dBase to be obsolete, and modern Excel formats are too complex to spend time and money on ensuring full compatibility. Therefore, Excel still has the ability to read DBF files, but support for saving data in this format by built-in software tools has been discontinued. However, there are some ways to convert data saved in Excel to DBF using add-ins and other software.

Method 1: WhiteTown Converters Pack program

There are a number of programs that allow you to convert data from Excel to DBF. One of the most simple ways converting data from Excel to DBF is to use a package of utilities for converting objects with various WhiteTown Converters Pack extension.

Although the installation procedure for this program is simple and intuitive, we will nevertheless dwell on it in detail, pointing out some of the nuances.

After you have downloaded and run the installer, a window immediately opens Installation Wizards, in which it is proposed to select the language for the further installation procedure. By default, the language that is installed on your Windows instance should be displayed there, but you can change it if you want. We will not do this and just press the button. "OK".

Next, a window is launched, which indicates the location on system disk where the utility will be installed. By default, this is the folder "Program Files" on disk "C"... Here it is also better not to change anything and press the key. "Further".

Then a window opens in which you can choose which directions of transformation you want to have. By default, all available conversion components are selected. However, some users may not want to install all of them, as each utility takes up space on the hard drive. In any case, it is important for us that there is a check mark next to the item XLS (Excel) to DBF Converter... The user can choose to install the remaining components of the utility package at his discretion. After the setting is made, do not forget to click on the button "Further".

After that, a window opens in which a shortcut is added to the folder "Start"... By default, the shortcut is named "WhiteTown", but you can change its name if you want. Click on the key "Further".

Then a window is launched asking whether to create a shortcut on the desktop. If you want it to be added, then leave a check mark next to the corresponding parameter, if you do not want, then uncheck it. Then, as always, press the key "Further".

After that, another window opens. It lists the main parameters of the installation. If the user is not satisfied with something, and he wants to edit the parameters, then press the key "Back"... If everything is in order, then click on the button "Install".

The installation procedure begins, the progress of which will be displayed by a dynamic indicator.

Then an informational message opens on English language, which expresses gratitude for the installation of this package. Click on the key "Further".

In the last window Installation Wizards the WhiteTown Converters Pack is reported to have been successfully installed. We just have to press the button "To complete".

After that, a folder will appear on the Desktop called "WhiteTown"... It contains shortcuts to utilities for specific directions of conversion. We open this folder. We are faced with a large number of utilities included in the WhiteTown package for various conversion directions. Moreover, each direction has a separate utility for 32-bit and 64-bit operating systems. Windows systems... Open the application with the name XLS to DBF Converter corresponding to the bitness of your OS.

XLS to DBF Converter starts up. As you can see, the interface is in English, but, nevertheless, it is intuitive.
The tab opens immediately "Input" ("Enter"). It is intended to indicate the object to be converted. To do this, click on the button "Add" ("Add").

After that, a standard window for adding an object opens. In it, you need to go to the directory where the Excel workbook we need with the xls or xlsx extension is located. After the object is found, select its name and click on the button "Open".

As you can see, after that, the path to the object was displayed in the tab "Input"... Click on the key "Next" ("Further").

After that, we automatically move to the second tab "Output" ("Conclusion"). Here you need to specify in which directory the finished object with the extension DBF will be displayed. In order to select the folder for saving the finished DBF file, click on the button "Browse ..." ("View"). A small list of two items opens. Select File ("Select a file") and "Select Folder" ("Select folder"). In fact, these items only mean a choice of various kinds navigation windows for specifying the save folder. Making a choice.

In the first case, it will be a regular window. "Save as…"... It will display both folders and existing dBase objects. Go to the directory where you want to save. Further in the field "File name" we indicate the name under which we want the object to appear after conversion. After that, click on the button "Save".

If you choose the option "Select Folder", then a simplified directory selection window will open. It will display only folders. Select the folder to save and click on the button "OK".

As you can see, after any of these actions, the path to the folder for saving the object will be displayed in the tab "Output"... To go to the next tab, click on the key "Next" ("Further").

IN last tab "Options" ("Parameters") there are a lot of settings, but we are most interested in "Type of memo fields" ("Memo field type"). Click on the field in which the default setting is "Auto" ("Auto"). A list of dBase types for saving the object opens. This parameter is very important, since not all programs working with dBase can handle all types of objects with this extension. Therefore, you need to know in advance which type to choose. There are six different types to choose from:
- dBASE III;
- FoxPro;
- dBASE IV;
- Visual FoxPro;
- > SMT;
- dBASE Level 7.
We make a choice of the type that is needed for use in a specific program.

After the choice is made, you can proceed to the direct conversion procedure. To do this, click on the button "Start" ("Start").

The conversion procedure starts. If the Excel book contains several sheets with data, then a separate DBF file will be created for each of them. The completion of the conversion process will be indicated by a green progress indicator. After he reaches the end of the field, press the button "Finish" ("The finish").

The finished document will be located in the directory specified in the tab "Output".

The only significant drawback of the method using the WhiteTown Converters Pack is that it will be possible to carry out only 30 conversion procedures for free, and then you will have to purchase a license.

Method 2: add-in XlsToDBF

You can convert an Excel book to dBase directly through the application interface by installing third-party add-ons. One of the best and most convenient of these is the XlsToDBF add-on. Let's consider the algorithm for its application.

After downloading the XlsToDBF.7z archive with the add-on, unpack an object called XlsToDBF.xla from it. Since the archive has a 7z extension, unpacking can be done either standard program for this extension 7-Zip, or using any other archiver that supports working with it.

After that, run the Excel program and go to the tab "File"... Next, we move to the section "Parameters" via the menu on the left side of the window.

In the opened parameters window, click on the item "Add-ons"... Move to the right side of the window. At the very bottom there is a field "Control"... We move the switch in it to the position Excel Add-ins and click on the button "Go ...".

A small window for managing add-ons opens. We press the button in it "Overview…".

The window for opening the object is launched. We need to go to the directory where the unpacked XlsToDBF archive is located. We go into the folder with the same name and select the object with the name "XlsToDBF.xla"... After that, click on the button "OK".

Then we return to the add-on management window. As you can see, the name has appeared in the list "XLS -> DBF"... This is our superstructure. There should be a check mark next to it. If there is no check mark, then put it on, and then click on the button "OK".

So, the add-in is installed. Now open the Excel document, the data from which needs to be converted to dBase, or simply type them on the sheet, if the document has not yet been created.

Now we need to do some data manipulation to get it ready for conversion. First of all, we add two lines above the table header. They should be the very first on the sheet and have names on the vertical coordinate bar "one" and "2".
In the uppermost left cell, enter the name that we want to assign to the created DBF file. It consists of two parts: the name itself and the extension. Only the Latin alphabet is allowed. An example of such a name is "UCHASTOK.DBF".

In the first cell to the right of the name, you need to specify the encoding. There are two options for encoding using this add-in: CP866 and CP1251... If the cell B2 empty or it has any value other than "CP866", then the default encoding will be applied CP1251... We put the encoding that we consider necessary or leave the field empty.

Next, go to next line... The point is that in the dBase structure, each column called a field has its own data type. There are such designations for them:
- N(Numeric) - numeric;
- L(Logical) - logical;
- D(Date) - date;
- C(Character) - string.
Also, in the string ( Cnnn) and numeric type ( Nnn) after the name in the form of a letter, indicate the maximum number of characters in the field. If decimal places are used in a numeric type, then their number must also be specified after a period ( Nnn.n).

There are other types of data in dBase format (Memo, General, etc.), but this add-in cannot work with them. However, Excel 2003 also did not know how to work with them, when it still supported the conversion to DBF.

In our particular case, the first field will be a string 100 characters wide ( C100), and the rest of the fields will be numeric, 10 characters wide ( N10).

The next line contains the names of the fields. But the fact is that they must also be entered in the Latin alphabet, and not in the Cyrillic alphabet, as we do. Spaces are also not allowed in the field names. We rename them according to these rules.

After that, data preparation can be considered complete. Select the entire range of the table with the cursor holding the left mouse button on the sheet. Then go to the tab "Developer"... By default, it is disabled, so before further manipulations you need to activate it and enable macros. Further on the ribbon in the settings block "Code" click on the icon "Macros".
You can make it a little easier by typing a combination of hot keys Alt + F8.

The macros window is launched. In field "Macro name" enter the name of our add-on "XlsToDBF" without quotes. The register is not important in this case. Next, click on the button "Run".

The macro is processing in the background. After that, in the same folder where the source Excel file is located, an object with the DBF extension will be formed with the name that was specified in the cell A1.

As you can see, this method is much more complicated than the previous one. In addition, it is very limited in the number of used field types and created object types with the DBF extension. Another disadvantage is that the directory for creating a dBase object can be assigned only before the conversion procedure, by directly moving it to the destination folder of the original Excel file. Among the advantages this method it can be noted that, unlike the previous version, it is absolutely free and almost all manipulations are made directly through the Excel interface.

Method 3: Microsoft Access program

Although newer versions of Excel do not have a built-in way to save data in DBF format, nevertheless, the option using Microsoft Access comes closest to being called standard. The fact is that this program is released by the same manufacturer as Excel, and is also included in the package Microsoft Office... In addition, this is the safest option, since you will not need to contact software third party manufacturers. Microsoft Access is specifically designed to work with databases.

After all the necessary data on the sheet in Excel are entered, in order to convert them to the DBF format, you must first save in one of the Excel formats. To do this, click on the floppy disk icon in the upper left corner of the program window.

The save window opens. Go to the directory where you want the file to be saved. It is from this folder that it will then need to be opened in Microsoft Access. The book format can be left by default xlsx, or changed to xls. In this case, this is not critical, since anyway we save the file only in order to convert it to DBF. After all the settings are done, click on the button "Save" and close the Excel window.

We launch the Microsoft Access program. Go to the tab "File" if it opened in another tab. Click on the menu item "Open" located on the left side of the window.

The file open window starts. Go to the directory where we saved the file in one of the Excel formats. To make it appear in the window, move the file format switch to the position "Excel workbook (* .xlsx)" or « Microsoft Excel(* .xls) ", depending on which one the book was saved to. After the name of the file we need is displayed, select it and click on the button "Open".

A window opens "Link to a spreadsheet"... It allows you to move data from an Excel file to Microsoft Access as correctly as possible. We need to select the Excel sheet from which we are going to import data. The fact is that even if the Excel file contained information on several sheets, then you can import it into Access only separately and, accordingly, then convert it to separate files DBF.
It is also possible to import information of individual ranges on sheets. But in our case it is not necessary. Set the switch to position "Sheets", and then select the sheet from which we are going to take the data. The correctness of the information display can be viewed in the lower part of the window. If everything satisfies, click on the button "Further".

In the next window, if your table contains headers, you need to check the box next to "The first row contains the column headings"... Then click on the button "Further".

In the new Spreadsheet Link window, you can optionally change the name of the linked item. Then click on the button "Ready".

This will open a dialog box with a message stating that the linking of the table to the Excel file is complete. Click on the button "OK".

The name of the table, which we assigned to it in the last window, will appear on the left side of the program interface. We double-click on it with the left mouse button.

After that, the table will be displayed in the window. Moving to the tab "External data".

On the ribbon in the toolbox "Export" click on the inscription "Additionally"... In the list that opens, select the item "DBase file".

A window for exporting to DBF format opens. In field "File name" you can specify the location of the file and its name, if the default ones do not suit you for some reason.
In field "File format" choose one of three types of DBF format:
- dBASE III(default);
- dBASE IV;
- dBASE 5.
It should be borne in mind that the more modern the format (the higher the ordinal number), the more opportunities there are for processing data in it. That is, it is more likely that the file will be able to save all the table data. But at the same time, it is less likely that the program where you are going to import the DBF file in the future will be compatible with this type.

After all the settings are set, click on the button "OK".

If after this an error message appears, then try exporting the data using a different type of DBF format. If everything went well, a window will appear informing you that the export was successful. Click on the button Close.

The created file in dBase format will be located in the directory specified in the export window. Then you can perform any manipulations with it, including importing it into other programs.

As you can see, despite the fact that modern versions of Excel do not have the ability to save files in DBF format with built-in tools, nevertheless, this procedure can be done using other programs and add-ons. It should be noted that the most functional conversion method is to use the WhiteTown Converters Pack. But, unfortunately, the number of free conversions in it is limited. The XlsToDBF add-in allows you to do the conversion absolutely free, but the procedure is much more complicated. In addition, the functionality of this option is very limited.

The "golden mean" is a method using Access programs... Like Excel, it is developed by Microsoft, and therefore you can no longer call it a third-party application. Besides this option allows you to convert Excel file into several types of dBase format. Although in this respect Access is still inferior to the WhiteTown program.

how to create a dbf file from an Excel file using ADO in ADO I got confused ...... in the specific information .... 1. connecting to dbf (and what else can be connected with ado and how); 2. creating a dbf file (other options); 3.record; 4.saving; 5.closing. thanks for any info

what to look for on a given topic or a complete ignore ..... on a topic ADO can better create a topic ..... extensive use of ADO

sparingly how it turns out

Code for the task: "Creating a dbf file from Excel"

Textual

Program listing

"::: Step 1 - Determine where we are running from homeDir = Wscript.ScriptFullName" ::: This is the full path of our file k = Instrrev (homeDir, "\") "::: Look for" \ "from the end homeDir = left (homeDir, (k-1)) "::: This is the clean directory name" ::: Step 2 - Create an ADO connection and an empty recordset Set Conn = CreateObject ("ADODB.Connection") Set RS = CreateObject (" ADODB.Recordset ") DSNName =" DRIVER = Microsoft dBase Driver (* .dbf); DBQ = "DSNName = DSNName & HomeDir" ::: Stage 3 - Open the connection Conn.Open DSNName "::: Stage 4 - Prepare SQL statement for creating a table SQL = "create table Testtable (N1 Float, N2 Float)" "::: Stage 5 - we execute it RS.Open sql, Conn, 3,3" ::: Stage 6 - Add 100 records to the table For i = 1 to 100 SQL = "insert into testtable values (" & cstr (i) & "," & Cstr (2 * i-1) & ")" RS.Open sql, Conn, 3,3 Next ":: : Stage 7 - summarizing ... SQL = "Select sum (N1), sum (N2) from Testtable" RS.Open SQL, Conn, 3,3 SS1 = RS (0) SS2 = RS (1) MsgBox SS1 MsgBox SS2 Rs.Close SQL = "Drop tabl e Testtable "RS.Open sql, Conn, 3.3 MsgBox" Table deleted! "

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 entirely ordinary :). I once worked 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 we had to be perverted in order to bring her back to life (which was not always possible, given the “abilities” of “wild juzverev” :)).

And now, several years later, as I no longer work there, they sent us a request by mail to add a new one. free program for editing DBF databases, which has an uncomplicated name - Sdbf... Let's talk about her :)

Comparison with 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 above plate, we 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 USB flash drive), and secondly, it is completely free!

Starting Sdbf for the first time

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 select one of the two available actions there: "Create" a new database or "Open" an existing one. Let's open the existing base:

Before us will open the contents of the selected database in the form of a table. The "zero" line displays the field names, and starting with the first line displays the content itself. Below the content is the toolbar and status bar at the bottom.

The latter displays a lot of useful service information, including the number of records in the database, encoding, creation date and automatically determined format. Of the Sdbf formats, it does not support only early versions of 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, the required cells still need to be found ... If the database is small and simple, then this can be done quite quickly and manually. However, if there are several dozen 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 navigate through the database (arrows), add / delete records, as well as 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 from the drop-down list the field that will be searched for. Now press the "Find Next" button and the program will automatically select the line following the current selection, which contains the desired text. Pressing the button again will highlight the line below the current one, which contains the same required data, etc.

Sometimes databases contain non-standard cell formatting: extra spaces, tabs and other characters that are not visually displayed, but affect the search result. If you are faced with such a case, then for the normal operation of the function, you will only need to uncheck the "Based on format" checkbox in the lower central part of the search window and the search lines will start 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 lines at once that contain only certain information. In this case, the second function (the button of which goes right after the search button) will help us - "Filter":

To use 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 composing a request is simple, but not entirely obvious. We need to first enter the name of the field by which we need to filter the database table, and then equate the filter value to the specific text by which we need to find all the 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 we have defined in the specified fields (in the example, we filtered all lines with the value "USA" in the "Country" field (the case of the name does not matter)). You can return the table to its original view simply by pressing the "Filter" button again (it becomes released again).

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

By the way, in order not to enter the filtering request manually, you can use the following trick: select any record in the field by which you will filter and press the Alt + F key combination (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 type of masks that can be used in Sdbf and, what's even sadder, one query 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 formation of a query by pressing "CTRL + ALT + F") or "or" ("SHIFT + ALT + F").

Executing SQL queries against database tables

Searching 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, for this case, Sdbf has a great feature - support for SQL queries!

With these queries in Sdbf, we can:

Dynamically format our table, creating a selection only for certain required fields (SELECT and SELECT TOP statements);
Copy data from one database to another (INSERT FROM and INSERT INTO);
Modify and delete the contents of certain lines (UPDATE and DELETE, respectively);
Group parallel queries (UNION);
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 newly edited database will disappear, and another empty window with two fields will appear on top of the working window (at the top - the data output field, and below - the input field).

Do not be afraid :). You just need to immediately enter the required SQL query in the bottom field with the name the necessary base data. If you do not remember this name, you can activate the Sdbf sidebar (by clicking on the gray vertical strip on the right), which displays in the form of a list all the databases that you opened before:

Also, to speed up the work, it is advisable 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), we will get a list of operators supported by Sdbf.

Let's go back to our previous screenshot and take a look at the simplest query that was made 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 indicate there, separated by commas, the names of any fields of the database table.

The fetch we looked at above is the simplest, but Sdbf allows for 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 this request, then we will see the already mentioned method of selection by the fields specified by commas after the SELECT statement. Further, the same indication of the name of the database, but now the request does not end there.

The next step is to list the functions of the main operator that will need to be executed as a result of processing the request. There are two of them here.

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 obtained by one of the fields (in this case, by the field with the customer's name - "NAME") in alphabetical ("asc" parameter) or inverted ("desc") order.

Naturally, the given 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 table obtained as a result of the query as a new database or as 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 figured out the main features of Sdbf, but it doesn't 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) can be found in the "Table" menu:

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

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

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 basic N, C, D) are different. For example, xClipper databases do not support image fields (P, B), and xBase up to the seventh version do not support auto-incrementing fields (+). That is, you should always remember about compatibility :).

While we're on the menu bar, don't forget to look at the Field menu:

There are only two functions here, but they can be very useful! Especially the first one is "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 function "Calculate" is an analogue of Excel's auto-calculation formulas and allows us to perform such mathematical and statistical operations as finding the auto-sum of a field, calculating the arithmetic mean, as well as the maximums and minimums of values. Naturally, to perform calculations, the 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 needs to be changed and select the last item in the context menu - "Open in hex". That's it - you can edit and save or print the result right away!

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 specifying more than one mask in a filter;
there is no undo function.

conclusions

The Sdbf program, with its modest size and system requirements, allows you to do with DBF databases, if not all, then very, very much ... And the implementation of support for SQL queries generally 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 “Cancel last action"In the world" CTRL + Z ":( The most annoying thing is that through an oversight or ignorance, the user may, for example, accidentally apply autocorrect across the entire field, and it will be impossible to return the data :(

For 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 cite this article, provided that an open active link to the source is indicated and the authorship of Ruslan Tertyshny is preserved.

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

1C: Enterprise uses a special software object, xBase, to work with files in the DBF format (dBase III version). Working with this object is usually straightforward.

Attention!

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

Attention!

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

Reading a DBF file

Reading data from a DBF file is performed in several sequential stages:

XBase object creation;
Opening a file;
Sequential enumeration of all lines of the file and reading the field values;
Closing the file.

DBF = New XBase; DBF. OpenFile ("D: \ MyFile.dbf"); // Stage 2. Opening the file While Truth Cycle // Stage 3. Looping through the lines of the file Report (DBF. NAME); If NOT DBP. Next () Then // Positioning on the next record Abort; EndIf; End of Cycle; DBF. CloseFile (); // Step 4. Closing the file

You can use a slightly modified algorithm for iterating over the lines of the file:

Not yet DBP. At the end () Loop Report (DBF. NAME); DBF. Next(); End of Cycle;

Upload to DBF file

Stages of uploading to a DBF file:

XBase object creation;
Specifying the encoding (if not specified, the ANSI encoding will be used);
Description of fields;
File creation;
Loop with adding and filling lines;
Closing the file.

Let's look at this process with an example:

DBF = New XBase; // Stage 1. Creation of the XBase object DBF. Encoding = EncodingXBase. OEM; // Stage 2. Specifying the encoding DBF. Fields. Add ("CODE", "S", 9); // Stage 3. Description of the name and type of the field DBF. Fields. Add ("NAME", "S", 40); DBF. CreateFile ("D: \ MyFile.dbf"); // Step 4. Create a file Sample = Directories. Nomenclature. Choose(); While Sampling. Next () DBF cycle. Add(); // Add a line DBF. CODE = Sample. Code; // Fill in the field value DBF. NAME = Sample. Name; DBF. Write (); // Record the line EndCycle; DBF. CloseFile(); // Stage 6. Closing the file

When specifying an encoding, the EncodingXBase type is used, which can take two values:

ANSI- Windows format;
OEM- DOS format.

Adding a new field when describing a structure has the syntax

Add (< Имя>, < Тип>, < Длина>, < Точность>)

The following types are available:

"N" is a number;
"S" - string;
"D" - date;
"L" - boolean;
"F" - similar to "N" - number.

Field length is required for field types "N", "F" and "S".

Working with indexes

An index file may be used in conjunction with the DBF file, which may contain information about one or more indexes. The presence of indexes makes it possible to use search, and not only sequential enumeration of all lines of the file.

When creating an index file, you should specify:

List of indices;
The path to save the index file (at step 4 of uploading).

An example of creating an index file:

DBF ... Indices. Add ("INDCODE", "CODE"); DBF. CreateFile("D: \ MyFile.dbf", "D: \ index.cdx");

The procedure for adding a new index has the syntax:

Add (< Имя>, < Выражение>, <Уникальность>, < Убывание>, < Фильтр >)

To use indexes when reading from a DBF file:

Specify the path to the index file (at stage 2 of loading);
Set the current index.

An example of opening a DBF file using an index file:

DBF ... OpenFile ("D: \ MyFile.dbf", "D: \ index.cdx"); DBF. CurrentIndex = DBF. Indices. INDCODE;

Attention!

When opening a DBF file, positioning occurs on the first record in the file. The first record in the file does not match the first record in the index. Therefore, when using indices, before traversing the rows sequentially, it is necessary to position on the first row of the index. This can be done using the First () method, for example:

DBF. First ();

One of two functions can be used for searching:

To find (< Ключ>, < Режим >) ;
FindByKey (< Режим >) .

As a result of the operation of both functions, a value of the boolean type is returned (whether a record with the specified conditions was found or not). If the search is successful, the current pointer is set to the found string. One of the following values can be used as the search mode:

«>=»;
«>»;
«<=»;
«<«.

Let's look at a search in a DBF file using examples:

DBF = New XBase; DBF. OpenFile ("D: \ MyFile.dbf", "D: \ index.cdx"); // When opening a DBF file, the index file is additionally specified DBF. CurrentIndex = DBF. Indices. INDCODE; // Set the current index // search using the Find method: If DBF. Find ("000000003", "=") Then Report (+ DBF. NAME); Otherwise Report ("Not Found"); EndIf; // search using the FindByKey method: DBF. Key. CODE = "000000002"; If DBF. FindByKey ("=") Then Report ( "Found. Item name:"+ DBP. NAME); Otherwise Report ("Not Found"); EndIf; DBF. CloseFile();

Deleting entries in a DBF file

The record is deleted using the Delete () method:

DBF ... Delete();

However, this method does not permanently delete the entry from the file, it is marked as deleted. When iterating over the lines, records marked for deletion are skipped. If you need to traverse the entire file, including records marked for deletion, you must set the property to True DisplayRemoved xBase object. You can find out whether a record is marked for deletion or not using the Record Deleted () function. To unmark the deletion, use the Restore () method.

DBF ... DisplayRemoved = True; Not yet DBP. At the end () Loop If DBF. Record Deleted () Then DBF. Restore(); EndIf; DBF. Next(); End of Cycle;

To directly delete the marked records, use the Compress () method:

DBF ... Shrink();

If you need to delete all records in the file directly, you can use the ClearFile () method:

DBF ... ClearFile();

Loading from DBF with ADO

ADO technology can be used to work with DBF files. ADO Drivers are included with operating system Windows and you do not need to install them additionally.

Let's consider an example of code for reading from a DBF file using ADO technology:

ADO = New COMObject ("ADODB.Connection"); // Create a COM object ADO. Open ( "Provider = Microsoft.Jet.OLEDB.4.0; | Data Source =" "D: \" "; | Extended Properties = DBASE III"); DB = ADO. Execute ("Select * from MyFile"); // request to get all records from the file MyFile.DBF While the DB. EOF = 0 Cycle // Loop through the records in the DBF file Report (DB. Fields ("Name"). Value); // An example of accessing a field value DB. MoveNext (); // Go to the next entry End of Cycle; ADO. Close ();

In the above example, the connection string "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" D: \ "; Extended Properties = DBASE III" is used. In this line:

Provider is the driver to use;
Data Source is the path where the DBF file is located. The path is specified with directory precision. The file name is used as the table name in queries;
Extended Properties - required parameter when referring to DBF files. File format can be specified

Attention!

When reading in the specified way, the default encoding is OEM. To change the encoding to ANSI, set the HKEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ Microsoft \ Jet \ 4.0 \ Engines \ xBase \ DataCodePage parameter to "ANSI" in the Windows registry.

Still have questions?
Ask in the comments to the article.

Netex24 - cryptocurrency exchanger without registration

Cryptocurrency exchanger EL-CHANGE with the best rates

Black SEO - Basic Techniques

Rating of Russian-language YouTube channels about business