Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

import foxpro DBF into Access or msSQL

Posted on 2014-01-28
7
Medium Priority
?
4,082 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:john
ID: 39816472
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 25

Expert Comment

by:chaau
ID: 39816502
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 43

Assisted Solution

by:pcelba
pcelba earned 1000 total points
ID: 39816526
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
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 
LVL 12

Expert Comment

by:jrbbldr
ID: 39816842
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 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 39817012
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
ID: 39817342
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
ID: 39818123
Thank you all!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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