How can you import a foxpro DBF file in SQL Server?
Add a comment
|
5 Answers
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')
4 Comments
sqlchild
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)".
JoshBaltzell
@sqlchild: I had to install this to get things working for me. msdn.microsoft.com/en-us/vfoxpro/bb190233
Zach
@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."
Vali Maties
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
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!
1 Comment
Mark Wagoner
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"
http://elphsoft.com/dbfcommander.html can export from DBF to SQL Server and vice versa
Comments
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.
1 Comment
Alan B
Yes you definitely want to be using OLEDB over ODBC in this.
This tools allows you to import to and from SQL Server.
