Resolved DBF Files To Sql

[kwhelchel]

IS there anyone familiar with migrating DBF files to SQL? I have an application that is going to dump data to a DBF file then I need to get it into SQL for reporting on the data.

Thanks

 

[Skydiver]

If it's a one time operation, you could manually import into Access or Excel and then upload from there to SQL. If it's going to be something that you do regularly, then if memory serves, there should be an ODBC or OLEDB driver available from Microsoft that can read .DBF files.

 

[kwhelchel]

Not a one time thing daily if not more per day,

 

[NoUserHere]

You could do something like the below which will require you to use the foxpro driver. Syntax may not be up to scratch, but I'm sure you could work it out. Try :

SELECT * into Yourtable
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual foxpro driver here;
SourceDB =\Your server path here;
            SourceType = DBF',
'SELECT * FROM YourDBF')

Perhaps this might help you. Fox pro reads these DB file types. So you can find the driver on there too : Visual FoxPro Downloads

 

[kwhelchel]

Not having much luck with it on my end for my setup I am on a 64bit version

 

[Skydiver]

DBF / FoxPro connection strings - ConnectionStrings.com

Connection strings for DBF / FoxPro. Connect using Microsoft.Jet.OLEDB.4.0, Microsoft.ACE.OLEDB.12.0, OleDbConnection, MS dBASE ODBC and ODBC .NET Provider.

www.connectionstrings.com

 

[NoUserHere]

Yea, Skydiver, I thought to give that too, you beat me to it.

@kwhelchel Have you installed this ? Download Microsoft OLE DB Provider for Visual FoxPro 9.0 from Official Microsoft Download Center

If you can provide a little more detail as in what is your current problem and what exactly you are not having much luck with, maybe we can try to help resolve your current problem so you can begin your migration process?

 

[kwhelchel]

this is what I have done
did install the ole db provider for foxpro
ran this

select * into dbf
from openrowset('MSDASQL','C:\rcs\ORDERS.DBF';'';
'','SELECT * FROM ORDERS')

and get this at this time

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

keith

 

[NoUserHere]

This error is either one of four possibilities :

1. Your connection string is wrong
2. You don't have full permissions to the path
3. You haven't specified the necessary information for the linked server
4. You have a conflict between 32bit and 64bit drivers

Things to try :

• Double check your connection string.
• Try the steps in this topic : Cannot create an instance of OLE DB Provider VFPOLEDB object
• Since you are using 64 bit, refer to this post which explains how to use 32bit driver on 64bit : LessThanDot - Working with 32 bit providers and 64 bit SQL Server
• Try using an older version of SQL server if you continue to have problems with this driver. Perhaps rollback to 2008.