Creating a dbf file from Excel - VBA. Creating a dbf file from Excel - VBA How to make a dbf file

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

Uploading to a DBF file

Stages of uploading to a DBF file:

1. Creating an XBase object;
2. Specifying the encoding (if not specified, ANSI encoding will be used);
3. Description of fields;
4. File creation;
5. Loop with adding and filling lines;
6. Closing the file.

Let's look at this process using an example:

When specifying the encoding, the XBase Encoding 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

The following types are available:

• “N” – number;
• “S” – string;
• “D” – date;
• “L” – boolean;
• “F” – similar to “N” – number.

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

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 just sequential search of all lines of the file.

When creating an index file, you must specify:

• List of indices;
• Path to save the index file (at stage 4 of upload).

Example of creating an index file:

The syntax for adding a new index is:

To use indexes when reading from a DBF file:

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

Example of opening a DBF file using an index file:

Attention!

When opening a DBF file, positioning occurs on the first record in the file. The first entry in the file does not match the first entry in the index. Because of this, when using indexes, you must position yourself on the first row of the index before traversing rows sequentially. This can be done using the First() method, for example:

One of two functions can be used to search:

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

As a result of both functions, a value of type Boolean 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 line. One of the following values ​​can be used as the search mode:

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

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

Deleting entries in a DBF file

Deleting a record is done using the Delete () method:

But when using this method, the record is not permanently deleted from the file; it is marked as deleted. When iterating over rows, records marked for deletion are skipped. If you want to crawl the entire file, including entries marked for deletion, you must set the property to True. DisplayDeleted xBase object. You can find out whether a record is marked for deletion or not using the RecordDeleted() function. To remove the deletion mark, use the Restore() method.

To directly remove marked entries, use the Shrink() method:

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

Loading from DBF using ADO

ADO technology can be used to work with DBF files. ADO drivers are included in the Windows operating system and do not need to be installed additionally.

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

The example shown uses the connection string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="D:\";Extended Properties=DBASE III". In this line:

• Provider is the driver used;
• Data Source – path where the DBF file is located. The path is specified accurate to the directory. The file name is used as the table name in queries;
• Extended Properties – when accessing DBF files, this is a required parameter. You can specify the file format

Attention!

When reading using the specified method, the default encoding is OEM. In order to change the encoding to ANSI, you need to 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.

A free universal DBF editor that allows you to open existing and create new databases. It is very small in size, can be launched from a flash drive and at the same time has many advanced tools for working with DBF files, including 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 running on the FoxPro basis...

The main problem of all these applications was that a simple user could easily screw up the database so that it could not then be opened using standard means, so they had to use some perversion to bring it back to life (which did not always work, given the “abilities” of “wild users” :)).

And now, several years later, since I no longer work there, we received a request by email to add a new free program for editing DBF databases, which has a simple name - Sdbf. Let's talk about it :)

Comparison with a paid analogue

Sdbf, despite its portability (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 database, 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 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 launch of Sdbf

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

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

The contents of the selected database will open in front of us in the form of a table. The “zero” line displays the names of the fields, and starting from the first line - the contents themselves. Below the content there is a toolbar and a 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 determined format. Of the formats, Sdbf does not support only earlier 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 necessary 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 searching can be quite difficult.

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 located on the bottom toolbar. Here, first there are 8 navigation buttons that allow you to navigate through 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 to be searched from the drop-down list. Now click the “Find Next” button and the program will automatically select the line following the current selection that contains the text you are looking for. Pressing the button again will highlight the line below the current one, which contains the same required data, etc.

Sometimes in databases you encounter 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, you will only need to uncheck the “Based on format” checkbox in the lower central part of the search window and the searched strings will begin to be displayed.

The search function is convenient 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 that contain only certain information. IN in this case The second function will help us (the button of which is located immediately after the search button) - “Filter”:

To enable filtering, we first need to correctly compose a query and enter it in a specially designated field (immediately behind 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 need to filter the database table, and then equate the filter value to the specific text by which we need to find all rows.

We put the value in single quotes, after which we press the “Filter” button itself (it becomes pressed) and we get a table containing data only with the values ​​​​defined to us in the specified fields (in the example, we filtered out all rows with the value “U.S.A” in the “Country” field (the case of the name does not matter)). You can return the table to its original form 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” (for an alternative selection of data from different fields). Unfortunately, Sdbf does not support exclusion 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 key combination “Alt+F” (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 is 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 generation of a request by pressing “CTRL+ALT+F”) or “or” (“SHIFT+ALT+F”).

Executing SQL queries against database tables

Searching and filtering are 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 an excellent feature - support for SQL queries!

Using such 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. Modify and delete the contents of certain lines (UPDATE and DELETE, respectively);
4. Group parallel queries (UNION);
5. Generate 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).

Don't be afraid :). You just need to immediately enter the desired SQL query in the lower field indicating the name of the desired database. If you don’t remember this name, you can activate the Sdbf sidebar (by clicking on the gray vertical bar on the right), which displays all the databases that you have opened before in a list:

Also, to speed up your work, it is advisable to remember the hot keys, which can be viewed by calling up 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 in 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 we right-click on the input field (call up the context menu), we will get a list of operators supported by Sdbf.

Let's return to our previous screenshot and look at the simplest request that was compiled there. It is based on the “SELECT” operator, which means it returns a selection to us. The selection parameter is “*”, which means displaying any data, but we could indicate there, separated by commas, the names of any fields in the database table.

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

If we analyze this query, we will see the method of selecting by fields that I have already mentioned, separated by commas after the SELECT statement. Next, the same indication of the database name, but now the request does not end there.

The next step is to list the main operator functions that will need to be performed 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 client’s name - “NAME”) in alphabetical (“asc” parameter) or inverted (“desc”) order.

Naturally, the example given 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 a query 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 figured out the basic capabilities 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 :) Thus, 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 “Change structure” item. This item opens an additional window in which we can completely change all the fields of the open table, add new ones, or delete any more unnecessary sections (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 depending on the type of field).

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

While we're looking at 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 - “Replace”. It allows you to quickly and automatically replace values ​​in the entire column of a 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 autosum of a field, calculating the arithmetic mean, as well as maximum and minimum values. Naturally, to perform calculations, field types must be numeric (N, F, I, O, B).

The last feature that 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 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 you to specify more than one mask in the filter;
• There is no undo function.

conclusions

The Sdbf program, with its modest size and system requirements, allows you to do, if not everything, then very, very much with DBF databases... 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 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 offensive thing is that, through oversight or ignorance, the user can, for example, accidentally apply autocorrect throughout the entire field, and return the data will no longer be possible :(

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

P.S. Permission is granted 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 popular format for storing and exchanging data between various programs, and primarily between applications that maintain databases and spreadsheets. Although it has become outdated, 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 Excel 2007, stopped fully supporting this format. Now in this program you can only view the contents of the DBF file, and saving data with the specified extension using the application’s built-in tools will no longer be possible. Fortunately, there are other options for converting data from Excel into the format we need. Let's look at how this can be done.

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

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

Method 1: WhiteTown Converters Pack

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

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

1. After you have downloaded and launched the installer, a window immediately opens Installation Wizards, which prompts you to select a language for further installation procedures. By default, it should display the language that is installed on your copy of Windows, but you can change it if you wish. We won’t do this and just press the button. "OK".



2. Next, a window opens, indicating the location on the system disk where the utility will be installed. By default this is the folder "Program Files" on disk "C". Here it’s better not to change anything either and press the key "Further".



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



4. After this, a window opens in which you add a shortcut to the folder "Start". By default the shortcut is called "WhiteTown", but you can change its name if you wish. Press the button "Further".



5. A window then opens asking whether to create a shortcut on the desktop. If you want it to be added, then leave a checkmark next to the corresponding parameter; if you don’t want it, then uncheck it. Then, as always, press the button "Further".



6. After this, another window opens. It shows the basic installation parameters. If the user is not satisfied with something and wants to edit the parameters, then press the button "Back". If everything is in order, then click on the button "Install".



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



8. Then an information message opens in English, expressing gratitude for the installation of this package. Press the button "Further".



9. In the last window Installation Wizards It is reported that the WhiteTown Converters Pack program has been successfully installed. All we have to do is press the button "Complete".



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



11. The XLS to DBF Converter program starts. 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 that should be converted. To do this, click on the button "Add" ("Add").



12. After this, 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".



13. As you can see, after this the path to the object was displayed in the tab "Input". Press the button "Next" ("Further").



14. After this we are automatically moved to the second tab "Output" ("Conclusion"). Here you need to indicate in which directory the finished object with the DBF extension will be output. 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 points only mean selecting different types of navigation windows to indicate the save folder. We make a choice.



15. In the first case it will be a regular window "Save as…". It will display both folders and existing dBase objects. We go to the directory where we want to save. Next in the field "File name" We indicate the name under which we want the object to be listed 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 only show folders. Select the folder to save and click on the button "OK".



16. 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 button "Next" ("Further").



17. In the last tab "Options" ("Options") 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 to save the object opens. This parameter is very important, since not all programs that work with dBase can process all types of objects with this extension. Therefore, you need to know in advance which type to choose. There is a choice of six different types:
    • 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.



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



19. The conversion procedure starts. If an Excel workbook has 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, click on the button "Finish" ("Finish").

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

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

Method 2: XlsToDBF Add-in

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

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



2. After that, launch the Excel program and go to the tab "File". Next we move to the section "Options" through the menu on the left side of the window.



3. In the parameters window that opens, 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...".



4. A small add-on management window opens. Click on the button there "Review…".



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



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



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



8. Now we will need to do some manipulation of the data to prepare it 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 "1" And "2".

   In the top left cell we 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 Latin alphabet is allowed. An example of such a name is "UCHASTOK.DBF".



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



10. Next we move on to the next line. The fact is that in the dBase structure, each column, called a field, has its own data type. There are such designations:
    • N(Numeric) – numerical;
    • L(Logical) – logical;
    • D(Date) – date;
    • C(Character) – string.

    Moreover, in the string ( CNN) and numeric type ( Nnn) after the name in the form of a letter should be indicated maximum amount characters in the field. If the numeric type uses decimal places, then their number must also be indicated after the dot ( Nnn.n).

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

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



11. 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, like ours. Also, spaces are not allowed in field names. We rename them according to these rules.



12. After this, data preparation can be considered complete. Select the entire table range on the sheet with the cursor holding down the left mouse button. Then go to the tab "Developer". By default, it is disabled, so before further manipulations you need to activate it and enable macros. Next on the ribbon in the settings block "Code" click on the icon "Macros".

    You can make it a little easier by typing a hotkey combination Alt+F8.



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



14. Macro in background performs processing. After this, in the same folder where the source Excel file is located, an object with the DBF extension will be created with the name that was specified in the cell A1.

As you can see, this method is much more complicated than the previous one. It is also quite limited in the number of field types it can use and the number of object types it can create with the DBF extension. Another drawback is that the dBase object creation directory can only be assigned before the conversion procedure, by directly moving the source Excel file to the destination folder. Among the advantages this method It can be noted that, unlike the previous option, it is absolutely free and almost all manipulations are performed directly through the Excel interface.

Method 3: Microsoft Access program

Although new versions of Excel do not have a built-in way to save data in DBF format, there is still an option to use Microsoft applications Access is the closest thing to being called standard. The fact is that this program was released by the same manufacturer as Excel, and is also included in the package Microsoft Office. Moreover, this is the most safe option, since there will be no need to contact software third party manufacturers. Microsoft Access is specifically designed for working with databases.

1. After all the necessary data on the sheet has been entered into Excel, in order to convert it to the DBF format, you must first save it in one of the Excel formats. To do this, click on the floppy disk icon in the left top corner program windows.



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



3. Let's launch Microsoft program Access. 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.



4. The file open window opens. We 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 position « Excel workbook(*.xlsx)" or « Microsoft Excel(*.xls)", depending on which of them the book was saved in. After the name of the file we need is displayed, select it and click on the button "Open".



5. A window opens "Spreadsheet Link". 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, you can only import it into Access separately and, accordingly, then convert it into separate files D.B.F.

   It is also possible to import information from individual ranges into sheets. But in our case this 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 information display can be viewed at the bottom of the window. If everything is satisfactory, press the button. "Further".



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



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



8. After this, a dialog box will open indicating that linking the table to the Excel file is complete. Click on the button "OK".



9. The name of the table that we assigned to it in the last window will appear on the left side of the program interface. Double-click on it with the left mouse button.



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



11. On the ribbon in the toolbox "Export" click on the inscription "Additionally". In the list that opens, select the item "dBase File".



12. The export to DBF format window opens. In field "File name" you can specify the file storage location and its name if the default ones are not suitable for you for some reason.

    In field "File format" select one of three types of DBF format:

    • dBASE III(default);
    • dBASE IV;
    • dBASE 5.

    It should be taken into account that the more modern the format (the higher the serial number), the more opportunities there are for processing data in it. That is, there is a higher probability that all table data will be saved in the file. But at the same time, it is less likely that the program into which you are going to import a DBF file in the future will be compatible with this particular type.

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



13. If an error message appears after this, 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 that was 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 the DBF format using built-in tools, this procedure can nevertheless be carried out using other programs and add-ins. 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-on allows you to perform 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 the method using the Access program. Like Excel, it's a development Microsoft, and therefore third party application you can't call him anymore. In addition, this option allows you to convert Excel file into several types of dBase format. Although in this indicator Access is still inferior to the WhiteTown program.