Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Importing DBF files into SQL Server


By:   |   Read Comments (23)   |   Related Tips: More > Import and Export


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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

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

 

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

@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 01, 2015 - 1:34:26 AM - ABHI Back To Top

Thnak you Sir.


Tuesday, June 09, 2015 - 1:22:36 PM - Gene Wirchenko Back To Top
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 09, 2015 - 8:44:32 AM - David Back To Top

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

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 06, 2014 - 3:21:40 AM - Rahul Back To Top

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

Nice one. worked like a charm!! Thanks


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

شكرا جزيلا

 

Thank you very much


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

خیلی خوب بود

Very Good

 


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

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

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

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

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

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 06, 2013 - 1:18:02 PM - Johann Back To Top

Very good. You saved me. Thanks.


Wednesday, February 06, 2013 - 3:17:04 PM - Kael Dowdy Back To Top

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

verrrrrryyyy Gooooooooooood

Thank u very much

Good Luck

=)


Thursday, August 09, 2012 - 10:00:06 AM - Greg L Back To Top

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 03, 2011 - 4:51:12 PM - Tim Cullen Back To Top

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

Tim


Wednesday, August 03, 2011 - 10:48:58 AM - Sune Due Møller Back To Top

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


Learn more about SQL Server tools