Solved

import foxpro DBF into Access or msSQL

Posted on 2014-01-28
7
3,674 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
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 24

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 41

Assisted Solution

by:pcelba
pcelba earned 250 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 29

Accepted Solution

by:
Olaf Doschke earned 250 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
February 24, 2017 — On February 23, Travis Ormandy, a vulnerability researcher at Google, reported on Twitter (https://twitter.com/taviso/status/834900838837411840) that massive stores of data have been leaked by CloudFlare, a company that provide…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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