Solved

import foxpro DBF into Access or msSQL

Posted on 2014-01-28
7
3,779 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 42

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
diarydate calendar in vfp retrun data to form 7 486
set order of a table by two fields vfp 4 1,674
Remove duplicates values. 14 67
Write Caching in the Cloud for VFP9 16 127
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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 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