How to import a DBF file in SQL Server

R

5

18

How can you import a foxpro DBF file in SQL Server?

Rawdan answered 9/9, 2008 at 20:26 Comment(0)

H

19

Use a linked server or use openrowset, example

SELECT * into SomeTable
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\SomeServer\SomePath\;
SourceType=DBF',
'SELECT * FROM SomeDBF')

Highly answered 9/9, 2008 at 20:28 Comment(4)

i used it , but this error occurs----OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Driver does not support this function". Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)". – Petes 22/3, 2011 at 5:52

@sqlchild: I had to install this to get things working for me. msdn.microsoft.com/en-us/vfoxpro/bb190233 – Epistrophe 10/6, 2011 at 13:13

@JoshBaltzell: I get the following error when I try to install that ODBC driver: "Error 1918: Error installing ODBC driver: Microsoft Visual FoxPro Diver, ODBC error 13. The setup routines for the Microsoft Visual FoxPro Diver ODBC Driver could not be loaded due to system error code 1114: a dynamic link library (DLL) initialization routine failed. (C:\Windows\system32\vfpodbc.dll).. Verify that the file Microsoft Visual FoxPro Driver exists and that you can access it." – Selfopinionated 12/1, 2012 at 20:46

What happens with deleted records from dbf file?! How can make the select command to work with two tables (dbf files) from different locations on the same PC? These are my problems with those dbf files. Regards, Vali – Cosmology 23/12, 2015 at 12:56

O

6

I was able to use the answer from jnovation but since there was something wrong with my fields, I simply selected specific fields instead of all, like:

select * into CERTDATA
from  openrowset('VFPOLEDB','C:\SomePath\CERTDATA.DBF';'';
    '','SELECT ACTUAL, CERTID,  FROM CERTDATA')

Very exciting to finally have a workable answer thanks to everyone here!

Outlet answered 15/8, 2012 at 16:51 Comment(1)

This worked for me. However I would get an "Access denied" error until I went to Server Objects -> Linked Servers -> Providers -> VFPOLEDB, clicked on Properties and checked "Allow inprocesss" – Doddering 10/12, 2015 at 21:11

C

2

What finally worked for us was to use the FoxPro OLEDB Driver and use the following syntax. In our case we are using SQL 2008.

select * from 
    openrowset('VFPOLEDB','\\VM-GIS\E\Projects\mymap.dbf';'';
    '','SELECT * FROM mymap')

Substitute the \\VM-GIS... with the location of your DBF file, either UNC or drive path. Also, substitute mymap after the FROM with the name of the DBF file without the .dbf extension.

Celina answered 14/6, 2011 at 16:10 Comment(1)

Yes you definitely want to be using OLEDB over ODBC in this. – Nerti 10/1, 2013 at 17:19

N

2

http://elphsoft.com/dbfcommander.html can export from DBF to SQL Server and vice versa

Neve answered 23/6, 2011 at 10:34 Comment(0)

H

0

This tools allows you to import to and from SQL Server.

http://www.download3000.com/download_17933.html

Hunan answered 9/9, 2008 at 20:31 Comment(0)

Recommended topics

Hot tags