dBASE is one of the oldest database management systems, and the dBASE (DBF) file format has been in use for a long time. Microsoft Access supports data export to the following dBASE file formats: dBASE III, dBASE IV, dBASE 5 and dBASE 7.

Please note that dBASE support has the following requirements.

Office 365 subscription If you are an Office 365 subscriber, make sure you have the latest Office version. If you are an IT professional managing the Office 365 update process, visit the Channel Release page to see what updates are provided in each channel.

Office 2016 Install the following updates in the order listed: May 2, 2017 Update for Office 2016 (KB3115501) and May 2, 2017 Update for Access 2016 (KB3178700).

Office 2016 Click-to-Run editions Updates are applied automatically.

Exporting data to a dBASE file

This will create a dBASE file in the specified location and format.

Access 2013 does not support dBASE format

Exporting to dBASE is not supported in Access 2013. To work with dBASE files, try upgrading to your Office 365 subscription. Exporting to dBASE is available in Office 365 subscription versions of Access.

• Tutorial

In this article I will tell you how to load many huge dbf files consisting of millions of records into your database on an ms sql server in an acceptable time.

The task is trivial at first glance. You can use the wizard in sql management studio or the OPENROWSET function via a query.

But the first option, after several attempts, was dropped due to various glitches and the need to load many files into one table (about 100 files). In addition, when loading for a long time, an error occurred.

The second option was also not suitable due to the different bitness of the drivers and the bitness of the server.

Since the file is simply huge, it was decided to read it through a stream and write it to the database. Next, after reading a line in the file, you need to write this line into a table. The first thing that came to mind was to use insert, but writing in this case would take too much time.

And then I remembered about another recording mechanism via SqlBulkCopy, which allows you to upload a huge number of records without insert queries.
In fact, this is the use of the SqlBulkCopy class, to write through which you only need to implement the IDataReader interface.

So let's start with the implementation of the public class BDFBulkReader interface: IDataReader

Let's start with a function that returns the value of the current record:
public object GetValue(int i) ( return R]; )
Let me draw your attention to the fact that the fields in the file and the fields in the table may be in different orders. And from the index I would like to get the value for the corresponding table field. Therefore, I additionally used the FieldIndex dictionary, where the mapping of field names to numbers in the sql table. The field name is taken by number, and the value from the read line of the dbf file is taken by name from the R dictionary. As a result, for the nth index in the database, GetValue will return the corresponding value.
Dictionary R = new Dictionary (); Dictionary FieldIndex = new Dictionary ();

We will pass FieldIndex already filled in for the table, and R will fill it when the reader calls the Read function, which we will also implement in the future.

So, the constructor:

System.IO.FileStream FS; byte buffer; int_FieldCount; int FieldsLength; System.Globalization.DateTimeFormatInfo dfi = new System.Globalization.CultureInfo("en-US", false).DateTimeFormat; System.Globalization.NumberFormatInfo nfi = new System.Globalization.CultureInfo("en-US", false).NumberFormat; string FieldName; string FieldType; byteFieldSize; byteFieldDigs; int RowsCount; int ReadedRow = 0; Dictionary R = new Dictionary (); Dictionary FieldIndex = new Dictionary (); public BDFBulkReader(string FileName, Dictionary FieldIndex) ( FS = new System.IO.FileStream(FileName, System.IO.FileMode.Open); buffer = new byte; FS.Position = 4; FS.Read(buffer, 0, buffer.Length); RowsCount = buffer + (buffer * 0x100) + (buffer * 0x10000) + (buffer * 0x1000000); buffer = new byte; FS.Position = 8; FS.Read(buffer, 0, buffer.Length); (buffer * 0x100)) - 1) / 32) - 1; FieldName = new string[_FieldCount]; byte; FS.Position = 32; FS.Read(buffer, 0, buffer.Length); for (int i = 0; i< _FieldCount; i++) { FieldName[i] = System.Text.Encoding.Default.GetString(buffer, i * 32, 10).TrimEnd(new char { (char)0x00 }); FieldType[i] = "" + (char)buffer; FieldSize[i] = buffer; FieldDigs[i] = buffer; FieldsLength = FieldsLength + FieldSize[i]; } FS.ReadByte(); this.FieldIndex = FieldIndex; }

Its tasks are to open the file, determine the names of the fields, their number and their types. The second parameter of the constructor, as I wrote above, is a dictionary of correspondences, so that, for example, by the first field number we are guaranteed to get the required field from the file.

Now let's move on to the implementation of bool Read(). It will return true if the line was successfully read. And false if the line was not read and at the same time the end of the data was reached.

Public bool Read() ( if (ReadedRow >= RowsCount) return false; R.Clear(); buffer = new byte; FS.ReadByte(); FS.Read(buffer, 0, buffer.Length); int Index = 0 ; for (int i = 0; i< FieldCount; i++) { string l = System.Text.Encoding.GetEncoding(866).GetString(buffer, Index, FieldSize[i]).TrimEnd(new char { (char)0x00 }).TrimEnd(new char { (char)0x20 }); Index = Index + FieldSize[i]; object Tr; if (l.Trim() != "") { switch (FieldType[i]) { case "L": Tr = l == "T" ? true: false; break; case "D": Tr = DateTime.ParseExact(l, "yyyyMMdd", dfi); break; case "N": { if (FieldDigs[i] == 0) Tr = int.Parse(l, nfi); else Tr = decimal.Parse(l, nfi); break; } case "F": Tr = double.Parse(l, nfi); break; default: Tr = l; break; } } else { Tr = DBNull.Value; } R.Add(FieldName[i], Tr); } ReadedRow++; return true; }

Let me remind you once again that after calling it, the read line will be written to the R dictionary for subsequent reading by the reader.
So, all that remains is to implement the method that returns the number of fields:

Public int FieldCount ( get ( return _FieldCount; ) )

And stubs for the interface:

Public void Dispose() ( FS.Close(); ) public int Depth ( get ( return -1; ) ) public bool IsClosed ( get ( return false; ) ) public Object this ( get ( return new object(); ) ) public Object this ( get ( return new object(); ) ) public int RecordsAffected ( get ( return -1; ) ) public void Close() ( ) public bool NextResult() ( return true; ) public bool IsDBNull(int i) ( return false; ) public string GetString(int i) ( return ""; ) public DataTable GetSchemaTable() ( return null; ) public int GetOrdinal(string name) ( return -1; ) public string GetName(int i) ( return ""; ) public long GetInt64(int i) ( return -1; ) public int GetInt32(int i) ( return -1; ) public short GetInt16(int i) ( return -1; ) public Guid GetGuid(int i) ( return new Guid(); ) public float GetFloat(int i) ( return -1; ) public Type GetFieldType(int i) ( return typeof(string); ) public double GetDouble(int i) ( return -1; ) public decimal GetDecimal(int i) ( return -1; ) public DateTime GetDateTime(int i) ( return new DateTime(); ) public string GetDataTypeName(int i) ( return ""; ) public IDataReader GetData(int i) ( return this; ) public long GetChars(int i, long fieldoffset, char buffer, int bufferoffset, int length) ( return -1; ) public char GetChar(int i) ( return " "; ) public long GetBytes(int i, long fieldOffset, byte buffer, int bufferoffset, int length) ( return -1; ) public byte GetByte(int i) ( return 0x00; ) public bool GetBoolean(int i) ( return false; ) public int GetValues(Object values) ( return -1; )

Where in Dispose() I simply close the file.

Once the interface is implemented, you can write a method to load the file:

Void SaveToTable(FileInfo dir, string TableName, string connestionString, Dictionary FieldIndex) ( using (var loader = new SqlBulkCopy(connestionString, SqlBulkCopyOptions.Default)) ( loader.DestinationTableName = TableName; loader.BulkCopyTimeout = 9999; loader.WriteToServer(new BDFBulkReader(dir.FullName, FieldIndex)); ) )

That's it. All that remains to pass to this function is the file location, table name, connection string and the corresponding matching dictionary, for example:

Dictionary FieldIndex= new Dictionary (); FieldIndex.Add(0, "POSTALCODE"); FieldIndex.Add(1, "IFNSFL"); FieldIndex.Add(2, "TERRIFNSFL"); FieldIndex.Add(3, "IFNSUL"); FieldIndex.Add(4, "TERRIFNSUL"); FieldIndex.Add(5, "OKATO"); FieldIndex.Add(6, "OKTMO"); FieldIndex.Add(7, "UPDATEDATE"); FieldIndex.Add(8, "HOUSENUM"); FieldIndex.Add(9, "ESTSTATUS"); FieldIndex.Add(10, "BUILDNUM"); FieldIndex.Add(11, "STRUCNUM"); FieldIndex.Add(12, "STRSTATUS"); FieldIndex.Add(13, "HOUSEID"); FieldIndex.Add(14, "HOUSEGUID"); FieldIndex.Add(15, "AOGUID"); FieldIndex.Add(16, "STARTDATE"); FieldIndex.Add(17, "ENDDATE"); FieldIndex.Add(18, "STATSTATUS"); FieldIndex.Add(19, "NORMDOC"); FieldIndex.Add(20, "COUNTER");

That's it, thank you all for your attention, happy downloading.

Even thoughdbf has long been considered legacy format, the subject still remains an urgent task judging by the number of questions on the Internet. In particular, I encountered it when trying to insert a card into the table. Map ArcGIS contained metadata in the format dbf . It made sense to read them at the same time in SQLServer , so as not to manually write labels for polygons, lines and other cartographic objects. In ancient times VisualFoxPro 6 and SQLServer 7.0 arewasn't a problem , but a lot has changed since then. With the exit SQLServer 2005 on MSDN information appeared that the import and export wizard in SQL Server does not support import and export of dBASE files and other DBF files. As a solution, it is recommended to use SQL Server Integration Services or staging import into Access or Excel.The same situation formally remains to this day,including SQLServer 2012. This is not always convenient because, in addition to SQLServer , requires additional installation MSOffice , and development tools ETL -packages are not included in the free edition SQLServerExpress . In this note I will try to import dbf V SQLServer without using anything other than SQLServer.

There is a file regions2010_wgs.dbf takenfrom here . Open SQLServerManagementStudio in ObjectExplorer select the database into whose table we will import dbf , and from context menu we talk ImportData:

Fig.1

We indicate as the data source.NetFrameworkDataProviderforODBC, since ODBCnow our everything again, as ConnectionString- the following connection string:

Driver=(Microsoft dBase Driver (*.dbf));SourceType=DBF;SourceDB=NA;Exclusive=No; NULL=No;Deleted=No;BackgroundFetch=No

Fig.2

ClickNext . If you now press Back, we will see that the connection properties have expanded from row to column so that we can see a list of them and see what each of them is equal to:

Fig.3

Connection string examples for ODBC -dBase drivers are given, for example, inMicrosoftKnowledgeBase or on the resource connectionstrings.com . In general, the purpose of certain properties is easy to guess from their names, except, perhaps, the Deleted property, which hasexactly the opposite meaning . As is known, the operation of deleting a row in dBase /FoxPro does not lead to its immediate physical removal from the file. The line is only marked that it has been deleted. Physical cleaning of lines that have a deletion flag and reorganization of the file are performed with the command PACK . The value NO tells the driver to include the deleted rows in the returned result set. To, on the contrary, not show them, you need to set YES. Click Next.

The next screen is simple. Sets up a connection withSQLServer , including the database in which the table with the results of import from dbf:

Fig.4

Let's move on. You are prompted to selectdbf -th table from the list of tables or write a query manually. Makes sense, for example, for FoxPro shny base, which, like any normal database, is a container that contains several tables, in this case in the form of separate dbf files. For individual dbf -file this does not work - see, for example,OdbcConnection . GetSchema(" tables") allwrongfor . dbfile, and support staff Microsoft recommended to use in this situation OLEDBProviderforVisualFoxPro . Firstly, the incident took place long before the radical change in the general line of the party. OLEDB then it was all ours aODBC , on the contrary, applied to old legacy interfaces. Secondly, I don't understand why you need to browse the list dbfwhen he is already alone.

In case of scattereddbf , located in the same directory, must be specified in the line ODBC-connections (Fig. 3) DefaultDir property, for example,

Driver=(Microsoft dBase Driver (*.dbf));sourcetype=DBF;DefaultDir=c:\Temp;exclusive=No;null=No;deleted=No;backgroundfetch=No

ThenYou can check Copy Data from one or more tables or views.

Fig.5

and a list will be displayed dbf in this directory, from which you will be asked to select:

Fig.6

But I didn't ask DefaultDir in Fig. 3, so I choose to write a request:

Fig.7

and I write:

Fig.8

Ain response I receive the error The Microsoft Jet database engine could not find the object "regions2010_wgs.dbf":

Fig.9

This error occurs because the driver is still stupidaccepts file names in MS-DOS 8.3 format . If you rename the regions2010_wgs.dbf file to, say,aaa . dbf , and the request in Fig. 8, accordingly, should be replaced withselect * from c:\Temp\aaa.dbf, the error disappears. You will be prompted to select an existing one or specify the name of the table that will be created on SQLServer in the database Database 1 (see Fig. 4) under the results of import from dbf. I leave the proposed name as it is:

Fig.10

By clicking the button herePreview , you can preview the contents dbf o it is supposed to be transferred to SQLServer:

Fig.11

Everything is fine, only abracadabra instead of Russian text is depressing. The reason for its appearance in a popular form is explained by the respected author Lalex. Russian characters are missing because the stupid driver is waiting dbf file in DOS oov encoding ( CP866, aka OEM ). He seems to consider the format dbfa very ancient, purely Dosov heritage. ArcView by default considers DBF to be a Windows format ( ANSI1251). So these two programs stand like two bulls, resting their foreheads .

So, the reason is clear, all that remains is to correct it. Enter dances with a tambourine in the connection linecollate=MachineorRussian / CodePage=ANSI / Collating Sequence=1251did not lead to success. Changed29th byte Vaaa . dbf on 0xC9 - zero emotions. Indeed, the code page sign in the header dbf is ignored by the driver. However, the driver setting can be changed in the registry. It is stored in DataCodePage on the way HKLM\ SOFTWARE\ Microsoft\ Jet\4.0\ Engines\ xBase or HKLM\ SOFTWARE\ Microsoft\ Office\14.0\ AccessConnectivityEngine\ Engines\ Xbase or, accordingly, HKLM\ SOFTWARE\ Wow6432 Node\ Microsoft\ Jet\4.0\ Engines\ xBase or HKLM\ SOFTWARE\ Wow6432 Node\ Microsoft\ Office\14.0\ AccessConnectivityEngine\ Engines\ Xbase depending on whether it was installed on the car Office and if so, how. By default, the property does have a value OEM which causes the driver to read everything dbf s based on this encoding. If you change it to ANSI

Fig.12

Cyrillic in ANSI dbf" e, naturally, will be read in human terms:

Fig.13

Fortunately, there is no need to restart, but the import wizard should be closed and repeated again from Fig. 1.

Click OKNext , we finish the wizard, as a result of which it is implicitly created and executed SSIS-plastic bag:

Fig.14

and we get garbage. Gee!

Fig.15

This, in fact, is also understandable why. In the tableQuery The wizard created a field for the import results region type varchar (200) without explicitly indicating the collation. Hence, it defaults to base collation. It so happened that the base Database1 had a non-Russian collation:

Fig.16

To correct the situation, you need to make a field region Unicode or adjust its collation. By the way, let's increase its length. Yes, just in case.

Fig.17

Save structure changes, clear datatruncate table Queryand repeat the import Fig. 1-14

Fig.18

Now everything is imported normally. The only thing is, I said “clear the data,” but I forgot to do this myself, and in the picture they were doubled. I won’t redo it anymore, because it’s unprincipled. The meaning is clear.

Alexey Shulenin

DBF - a database file, the ability to work with which was previously integrated into the environment Microsoft Office. Access and Excel applications worked with the format, later Access was removed from the package and became a separate program, and in Excel, since 2007, DataBaseFile support has been significantly limited.

If it is not possible to open a DBF file directly in Excel, you must first convert it.

However, DBF, although considered by many to be an outdated format, is still widely used in specialized programs in business, design, and engineering. Wherever it is necessary to work with large amounts of information, their structuring and processing, and executing queries. For example, the 1C Enterprise software package is entirely based on database management. And given that a lot of office documentation and data is processed in Excel, the issue of integrated work with these formats is relevant and in demand.

Excel problems when working with DBF

Excel 2003 had the ability to open and edit DBF, as well as save XLS documents in this format:

1. Select "File" from the menu bar.
2. Next, click “Save As”.
3. Select “*.dbf” from the drop-down list.

IMPORTANT. Since 2007, you can open and view in Excel format databases, but you cannot make changes or save .xls documents in it. Standard means programs no longer provide this option.

However, there are special add-ons for the application that add such a function to it. Programmers post their developments online on various forums, and you can find different options. The most popular add-on, called XslToDBF, can be downloaded from the developer’s website http://basile-m.narod.ru/xlstodbf/download.html. The download is free, but if you wish, you can support the project by transferring any amount to your wallet or card.

Installation and use:

1. Download the archive from the above site.
2. Extract XlsToDBF.xla from it and save it on your computer.
   
3. In Excel, go to the menu with the Microsoft icon on the left, “Options”.
   
4. Under Excel Options, select Add-Ins.
5. In the Manage/Excel Add-ins row, click Go.
   
6. Click Browse and locate the saved XlsToDBF.xla.
   
7. The entry “XLS -> DBF” should appear in the list of add-ons with the check box checked. Check if it is not there.
8. Now you can save .xls to .dbf format. You can download from the same site detailed instructions by use. The main thing is to prepare tabular data correctly.
9. Once the table is ready, select any filled cell and press Alt and F
10. In the macro window that opens, type XlsToDBF in the field, case is not important.
    
11. Click Run.
12. If you have prepared and formatted the data correctly, the database file will also be saved in the folder where the source XLS is located.

If you don't want to change anything in Office, don't trust add-ins, and third party applications, then we can propose a more labor-intensive way to convert XLS file in DBF:

1. Purchase and install Microsoft program Access.
2. In Excel, prepare and save the document.
   
3. Click the "Open" button in MS Access and select the file.
   
4. Now you need to configure the import correctly.
   
5. Select the sheet to start with. If there are several of them, you still have to do one at a time.
   
6. If the table has a header row, check the appropriate box.
   
7. Next, you can change the table name.
   
8. Now click on "External Data".
   
9. Click the “Export”, “Advanced” button.
   
10. Select dBase File.
    
11. Provide a name and save location.

This method does not always work successfully; errors often occur in data processing and subsequent saving. And it is very long and inconvenient.

Conversion

In order not to suffer with office programs yourself, many applications have been created that allow you to transfer data from one format to another. First of all, almost everything powerful programs for working with DBMS, they assume the ability to export to XLS and load from it. Secondly, there are small utilities that specialize in conversion. Here are some of them:

In all of these programs, the conversion comes down to opening the source file and then running the “Convert” or “Export” command.

There are also free services online conversions. On such sites you are asked to send (download) the source file, click “Convert”, after which a link to the converted document will appear. To what extent you can trust such services, the decision is individual, at your own peril and risk.

So open DBF in Excel program it’s possible, but if its version is 2007 and newer, then you won’t be able to do anything else with it, just look. There are special add-ons or programs for editing and saving in XLS, as well as for converting in the opposite direction. If you have experience converting and working with DBF in different applications, share your tips in the comments.

A simple program for transferring data from dbf files to tables Microsoft Excel.

The background of this utility is as follows: 1C has data uploaded into the ancient and powerful dBase format, for example, lists of goods with prices, etc. etc. So, some programs easily import data sets from Excel tables, but they don’t know about dBase.

Upload 1c saves data in WIN1251 encoding (of course, this may be configured somewhere, I haven’t checked), however, after opening such a file in Excel, the information is displayed in “crazy” ways. This occurs because Microsoft Excel opens dBase tables in CP866 (DOS) encoding by default.

Actually, what I mean by all this is that it turns out to be too difficult a test for the average user to figure out how, what, where and with what to recode, what buttons to press and what actions to perform. That is why the task was set as simply as possible and immediately.

P.S.: I never claim uniqueness or genius, since the Internet is full of similar software that change encodings and do much more, but most of them are paid, and the extra functionality only aggravates the torment of ordinary people.

A program for exporting data from dBase files to Microsoft Excel.

The main window of the program.

After the program is launched, you need to click on the "Open" button and in the file selection dialog, select the dbf that will be exported to Excel.

* * *


The main program window with the loaded dbf file.

If the file is successfully uploaded, its data will be displayed in the table. At the very top, in the "Source file" field, the full path and name of the downloaded dbf file will be shown.

After this, you need to click on the large “Export data” button located immediately below the table with the information.

* * *


A window asking you to scale columns.

If there were no problems with the dbf file, you should see a window asking you to scale the column sizes, this means whether in Excel you need to stretch the columns to fit the width of the data.

* * *

If all operations were successful, an Excel instance will be launched with all the data uploaded into it.

It may happen that the dbf file does not contain service information about the encoding, in which case the message "The dbf file does not contain information about the encoding" will be displayed.

In the pictures below, in red it is shown and written which of the “Yes” or “No” buttons should be pressed in a given case:

If the text in the table is unreadable.

* * *


If the text in the table is readable normally.

Features of the program

Microsoft Excel must be installed on the system.
Dbf files open in exclusive mode.
The contents of blob fields are not transferred.
The performance of the program was tested on Windows XP and Windows 7.
The utility is absolutely free and does not require installation, which means you download and use it.

WiFi