Importing DBF files into SQL Server

Problem

The company I work for has many data files that are stored in a DBF format which I need to load into SQL Server.  I’ve looked through the drivers available in the Import/Export Wizard and cannot find one I can use to import the files. Do you know of any methods I can use to import these files?

Solution

The good news is that you were heading in the right direction with the Import/Export Wizard.

In my example I am going to update the DBF file listed below. 

 

When you open the Import/Export Wizard there are a number of data sources you can choose from, and if you are running on a 64-bit operating system then there may be fewer choices. Select the Microsoft Office 12.0 Access Database Engine OLE DB Provider. When you select this option a Properties button will appear as shown below.  Click on the Properties button.

The Data Link Properties window should appear. Select the All tab. Within this tab there are two properties to be set for importing the DBF file-the Data Source and Extended Properties values:

Unlike other import processes, the data source should be set to the directory in which the shapefile files are located, not the actual DBF file:

The extended properties should be set to whatever dBase version was used to create the DBF file:

If the version is not known then you can try each version until successful. If you enter the wrong version you will get an immediate error message similar to the one below:

The remainder of the import process is similar to other import processes. Select the destination type and SQL Server instance, if applicable:

Select the method through which the source data will be selected:

Either select a current table or create a new table to house the data:

Select whether you want to save the package and execute immediately:

Finally, check to make sure that all steps executed successfully and whether the number of imported rows match what was expected:

Next Steps

• Review information on using the Import/Export Wizard
• Look for additional tips on Spatial Data Storage

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)