Solved

import foxpro DBF into Access or msSQL

Posted on 2014-01-28
7
3,511 Views
Last Modified: 2014-01-29
I want to import 2 DBF files into MsAccess or MS SQL
I downloaded MS Visual FoxPro OLE DB Proiver.
cant seem to figure out how to use it or some other method.
I have only 2 DBs I need to import, any suggestion of methods?
Or program I can use for a week?
 
thanks
john
0
Comment
Question by:john
7 Comments
 

Author Comment

by:john
Comment Utility
when trying to connect the does not show in visual studio, access ms sql either:
 Microsoft OLE DB Provider for Visual FoxPro.
thanks
john
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
you can always import the FoxPro files using a pre-installed ODBC driver (I think it exists on any Windows machine).
In your Import Wizard, select ODBC, and then type the following connection string:
Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\myvfpdbfolder;
Exclusive=No;Collate=Machine;NULL=NO;DELETED=YES;BACKGROUNDFETCH=NO;

Open in new window

0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 250 total points
Comment Utility
OLE DB provider should be visible in VS but you have several more options:
1) Download VFP 6 ODBC driver. The link is here: http://fox.wikis.com/wc.dll?Wiki~VFPODBCDriver~Wiki  (suppose your DBF tables are of version 6 or lower).

ODBC driver allows to link the DBF table from MS Access as an external table (Select External data sources in the menu and find out the VFP ODBC driver). MS Access must be 32 bit.

2) Create Linked server under MS SQL Server. It also requires 32 bit MS SQL Server and VFP OLE DB provider. Then you may query your DBF tables in SQL Server Management Studio. A script to create Linked server is e.g. here: http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer
and here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27951932.html
and some hints are here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26104121.html

3) You could also install free Visual FoxPro Command Window application and VFP run-time and convert your DBFs into a format which is better readable by Access or MS SQL or upload your DBFs to MS SQL database by a few FoxPro commands. This, of course, requires some FoxPro language knowledge. Run-time libraries are here: http://www.foxpert.com/runtime.htm, the Command Window application is here: http://leafe.com/dls/vfp  (look for vRunFox).

Commands necessary to convert DBF files to CSV are:
USE ?    ... select your DBF file in Open dialog
COPY TO SomeNewFile TYPE CSV    ... create CSV file

Upload to MS SQL Server requires a few more commands.

You may study other possibilkities of the COPY TO command in VFP help which is also available for free: http://vfpx.codeplex.com/wikipage?title=VFP%209%20SP2%20Help%20File&referringTitle=Home

The last option:
4) You may check DBF converters. Many of them are free and all of them can create CSV file.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:jrbbldr
Comment Utility
Since this appears to be a one-time operation and does not seem to need to be done periodically, I'd recommend Pavel's above #3 or #4 approach - especially since you are only talking about 2 FP/VFP data tables.

Do it once and then you are done.

Good Luck
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
Comment Utility
chaau, forget about the preinstalled foxpro driver, it's foxpro 1 or 2.

The OLEDB Provider is the right thing to use. I's a 32 bit driver and you can only see it in 32 bit Office or 32 bit SQL Server. Visual Studio and Management Studio are 32bit Applications and should see it, too.

You can load DBF data via OLEDB Provider using the ADODB.Connection and a connection string like given here: http://www.connectionstrings.com/visual-foxpro/

Eg oCon.Open("Provider=vfpoledb;Data Source=C:\MyDataDirectory\;")
You may also try vfpoledb.1 instead of vfpoledb.

Then use an ADODB.Recordset and use the connection object with it in oRS.open("Select * from table",oCon,3,3)

If you only have two DBFs the connection to a directory (of free tables) seems the right choice. If there is a DBC file in the table directory or perhaps it's parent directory make the connection to that DBC file.

Bye, Olaf.
0
 
LVL 9

Expert Comment

by:rinfo
Comment Utility
I have worked on multiple project that required me to export dbf tables to sqlserver.
I have always used dbf component(Tdbf) in delphi which binary reads dbf files and
has routines to get schema and  data from dbf files.
If you are considering using .net ,  there is a  .net version of tdbf  - it even has one for
php - that you can use.
Besides there is http://fastdbf.codeplex.com/ which is wrapper for reading and writting
to dbf files.
I always find using these then odbc , or ole or ado to read dbf files and export data
to sqlserver.
Google for these and you will have sufficinet information and example code to work .
0
 

Author Closing Comment

by:john
Comment Utility
Thank you all!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now