Importing DBF files into SQL Server

By:   |   Updated: 2011-08-03   |   Comments (30)   |   Related: More > Import and Export


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. 

Files that comprise a shapefile 

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.

Select the driver to use

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:

The All tab of the Properties window

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:

Set the source directory

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

Set the driver

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:

Wrong driver error

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

Set the destination driver and location

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

Set the selection method

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

Set the table or other device that will house the data

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

Save the package

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

Ensure completion of the import
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-08-03

Comments For This Article




Tuesday, March 12, 2024 - 9:23:43 AM - Douglas da Silva Back To Top (92061)
Can't find "Microsoft Office 12.0 Access Database Engine OLE DB Provider"

Monday, September 12, 2022 - 10:06:37 AM - Greg Robidoux Back To Top (90465)
Hi Christian,
you may need to install the MS Office components to get these other options.

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Also, I noticed I get different options if I use the SQL Server Import/Export app versus right-clicking on a database and selecting Tasks > Import Data.

-Greg

Friday, September 9, 2022 - 3:07:02 PM - Christian Back To Top (90460)
Can't find "Microsoft Office 12.0 Access Database Engine OLE DB Provider"

Thursday, April 29, 2021 - 11:49:04 AM - Isaac Njiru K. R. Back To Top (88624)
Thank You for this article, it has enabled me overcome the challenge of importing from .dbf to MS SQL.
Something to note, MS SQL will throw and error if it puts single quotes on the source file name. To overcome this, perform a preview of the data map, copy the SQL that is being executed, then press back and instead of copying the data, write a query instead. Paste the SQL you copied as your query and edit to remove the single quotes on the FROM table. Once this is done, your import will now have a query as the source, specify the name of your destination table and proceed as guided in this article.
Once again, Thanks.

Wednesday, April 24, 2019 - 5:19:49 AM - RAMESH KUMAR M Back To Top (79676)

 Thanks a lot........................

Wednesday, July 18, 2018 - 9:47:32 AM - Dirlei Wiebech Senne Back To Top (76670)

 Thank´s, was very useful.

 

Wednesday, February 21, 2018 - 12:17:56 AM - Nivi Back To Top (75256)

 

 Hi Team,

 

From where i ill get the sample (.dpf ) file. how to create the file with extension.dpf.

Monday, June 12, 2017 - 3:25:43 PM - Trevor Eppel Back To Top (57267)

 Both the 32-bit as 64-bit version of SQL import don't have a Microsoft Office 12.0 Access Database Engine OLE DB Provider.

 I am using MSSQL11.SQLEXPRESS

How can I add this to the list?


Monday, October 24, 2016 - 9:38:00 AM - Rick Back To Top (43621)

Both the 32-bit as 64-bit version of SQL import don't have a Microsoft Office 12.0 Access Database Engine OLE DB Provider.

How can I add this to the list? I'm using SQL Server 2016.

Friday, December 18, 2015 - 8:57:13 AM - Alan Gerhard Back To Top (40276)

 

Unbelievably simple yet I would never have figured it out.

 

Many thanks for taking the time to post the step by step guide on how to get dBASE data into SQL.

 

I’ve been meaning to convert my Clipper 87 home grown accounting system app since 2000 but it wasn’t till I upgrade ALL my development software that I realized it’s now or never.

 

 

Friday, September 11, 2015 - 8:29:05 PM - James Back To Top (38659)

@Sune - I didn't see anything mentioning the import of shapefiles.

 

The tip title is "Importing DBF files into Sql Server" and that exactly what the article does!

Wednesday, July 1, 2015 - 1:34:26 AM - ABHI Back To Top (38088)

Thnak you Sir.

Tuesday, June 9, 2015 - 1:22:36 PM - Gene Wirchenko Back To Top (37872)
People needing more help with this might consider going to foxite.com. It is a FoxPro forum, and there are some people who have done a lot with FoxPro and SQL Server.

Tuesday, June 9, 2015 - 8:44:32 AM - David Back To Top (37866)

Just a possible short cut to avoid some of the version trial-and-error for the DBF:  If you have a HEX file viewer, the first HEX character is the dBASE version number, when viewed as-is.

Example:  a HEX '03' indicates dBASE III, and HEX '04' indicates dBASE IV and early Fox, and so on.

 

Sunday, January 11, 2015 - 2:30:46 PM - wabo Back To Top (35895)

This worked perfectly for me. Please, I would like to know if there is a way to do this with a SQL Statement. Thanks !!!

Saturday, September 6, 2014 - 3:21:40 AM - Rahul Back To Top (34404)

Giving error

 

TITLE: SQL Server Import and Export Wizard
------------------------------

Could not connect source component.

Error 0xc0202009: Source - TTDS0614 [1]: An OLE DB error has occurred. Error code: 0x80004005.


Error 0xc02020e8: Source - TTDS0614 [1]: Opening a rowset for "`TTDS0614`" failed. Check that the object exists in the database.


------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

 

 

Friday, July 18, 2014 - 7:23:25 AM - Rush Chet Back To Top (32770)

Nice one. worked like a charm!! Thanks

Tuesday, June 10, 2014 - 12:32:17 PM - Tim Cullen Back To Top (32175)

شكرا جزيلا

 

Thank you very much

Tuesday, June 10, 2014 - 10:36:22 AM - abbas Back To Top (32172)

خیلی خوب بود

Very Good

 

Monday, July 22, 2013 - 4:45:44 AM - Ty Back To Top (25933)

Thank you so much for the tutorial. I've read other articles that mentioned about the 32/64 bit problems with the import and such (cuz I'm using the 64 bit and I dont have some of the drivers) but I'm lucky to find this easiest way of importing the data. Once again thank you Sir.

Cheers!

Monday, March 18, 2013 - 1:22:12 AM - biju Back To Top (22839)

Update ShipmentTrack 

set 

ALNo=b.AL1,

ALDt=b.ALDT,  

TBNO=b.SBNO,

From SHIPMENTTRACK AS A

INNER JOIN

DBF_DDB...ddbam AS B ON B.CARRIER = A.INVNO

The above code is showing error .  

Msg 8114, Level 16, State 8, Line 1

Error converting data type DBTYPE_DBTIMESTAMP to datetime

 

How to update dbase date in sql table?

Wednesday, March 13, 2013 - 2:46:54 AM - anu Back To Top (22772)

Hi Tim,

I have created blank DB where i m trying to import data from DBF files. I am not sure of exact datatypes used in Foxpro DB as I receive these dbf files from our client.

I am getting same error on click of "Edit Mappings" & "Preview".

Mostly Datatypes are - char,bit,int,decimal, 129(varchar), 133(datetime).

In the .dbf file folder, I have .fpt files also which are for "memo" datatype I suppose.

 

Another solution I tried is as below:

I installed MS Visual Foxpro Driver and used the same for importing DBF files into SQL server 2008 but then I am able to "Edit Mappings" and also can "Preview" the data. When i clicked on "edit Mappings", then i found that against one column datatype was written 129 and so i changed that to "varchar" with length 4000. 

when i click on "Next" getitng below message:

[Source Information]

Source Location : E:\ABC\data\test.DBC

Source Provider : VFPOLEDB.1

Cannot locate the mapping file to map the provider types to SSIS types

[Destination Information]

Destination Location : .\sqlexpress

Destination Provider : SQLNCLI10

Mapping file (to SSIS type): C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

[Conversion Table]

SSIS conversion file: C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

 

"Finish" gives me belwo errors:

Setting Source connection Error - "Warning 0x80202066: Source - acndcode [1]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used."

Validating Error-"Warning 0x80202066: Data Flow Task 1: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used."

Preexecute Error- "Warning 0x80202066: Data Flow Task 1: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

 (SQL Server Import and Export Wizard) 

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

 (SQL Server Import and Export Wizard) 

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

 (SQL Server Import and Export Wizard) 

Error 0xc004701a: Data Flow Task 1: component "Destination - acndcode" (34) failed the pre-execute phase and returned error code 0xC0202025.

 (SQL Server Import and Export Wizard)

 

Is this information sufficient & useful for troubleshooting?

 

Thanks in advance,

Anu

 

 

 

 

 


 

 

Tuesday, March 12, 2013 - 12:38:03 PM - Tim Cullen Back To Top (22748)

Hi Anu:

 

I would additional information so that I can assist you; can you post the data types of the destination table.  In the meantime:

  1. You may have to modify the approach if you have an identity field in the destination table
  2. Do you have the columns mapped correctly?
Let me know-I'm importing some data from another DBF so that I can help you troubleshoot.
 
Tim

Tuesday, March 12, 2013 - 9:02:33 AM - Anu Back To Top (22735)

I am following the above steps but getting the below error:

TITLE: SQL Server Import and Export Wizard
------------------------------

Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


`<tableName>` -> [dbo].[tableName]:

       - External table is not in the expected format.

Please help me for the same.

Thanks in advance

Anu

Wednesday, March 6, 2013 - 1:18:02 PM - Johann Back To Top (22602)

Very good. You saved me. Thanks.

Wednesday, February 6, 2013 - 3:17:04 PM - Kael Dowdy Back To Top (21952)

Thanks so much for this tip!  Makes importing .DBF files out of ESRI into SQL Server a cinch!!!

Sunday, October 28, 2012 - 5:11:26 PM - Nardooneh Back To Top (20114)

verrrrrryyyy Gooooooooooood

Thank u very much

Good Luck

=)

Thursday, August 9, 2012 - 10:00:06 AM - Greg L Back To Top (18979)

Why would I not see the Microsoft Office 12.0 Access Database Engine OLE DB Provider in my data source list?  I checked in SSIS and I see it there.

Thanks.

Wednesday, August 3, 2011 - 4:51:12 PM - Tim Cullen Back To Top (14312)

Thanks for pointing that out.  We are working on a remedy!

Tim

Wednesday, August 3, 2011 - 10:48:58 AM - Sune Due Møller Back To Top (14311)

Do I miss something; I don't see how the describe procedure will ever be able to import the geography part from the shape file!? In my opinion: Either the Tip Title is incorrect or the question is missunderstood by the author of the tip.

I can recommend this litte tool from Morten Nielsen (@sharpgis) to do the full job!

http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx

- Sune














get free sql tips
agree to terms