Transferring data from FoxPro dbf to Sql

Transferring data from FoxPro dbf to Sql

• Thelostcircuit

  mahes wrote:

  i'm sure, this code will work in VB.net. if possible, can you please provide me VB 6.0 code.

  Hi,

  Sorry mahes, but this forum is dedicated on the .Net language. SO VB6 is not quite supported here. I suggest scanning the microsoft newsgroups to see a newsgroup which isVB6 specific.

  http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

  cheers,

  Paul June A. Domag

  
  
  

• Nishanth_1980

  First here is an idea for you. Create a DTS package on the SQL server you want to do this on.

  Import the DBF by programatically executing the package after you copy the file up to the SQL.

  As long as you have known inputs and destinations this should give you more than enough.

  If the files have different names going in and make different tables you can do this by also by setting up global variables for the package. As you mentioned you just wanted to sync some stuff and this works well and doesnt take much code.

  That said... I have a differnt but similar problem in VB .net.

  I want to import a Visual Fox Pro file into an MDB. I am making a small utility in .NET.

  So far No LUCK.

  Here is a code snipet of what I am trying to do. I hooked up to Access via the Office then I wanted to import the DBF via the transfer command option.

  MAccessApp = New Microsoft.Office.Interop.Access.Application

  MAccessApp.OpenCurrentDatabase(DestMDB.Text)

  MAccessApp.DoCmd.TransferDatabase(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, "ODBC", "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & SrcPath & ";SourceType=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine;null=Yes;Deleted=Yes", Microsoft.Office.Interop.Access.AcDataObjectType.acDataTable, "C:\TEMP\CrossTabe.dbf", "CrossTab")

  MAccessApp.CloseCurrentDatabase()

  All I get when I execute the TransferDatabase is

  The Microsoft Jet database engine could not find the object 'CrossTab'.

  Make sure the object exists and that you spell its name and the path name correctly.

  Any ideas as to what I am suppossed to put in as the last item

  Thanks, Hope the DTS thing helps. Start doing DTS mid last year and found it useful.

  
  
  

• Hossam Abdel Wahab

  Hi,

  I guess what KeithM is trying to point out is that you would have to manually open your Foxpro database (as shown in the sample) and also your SqlServer (see MSDN for opening SqlServer tables) then manually copying them to the Foxpro datatable and use a dataadapter to insert all of the new values in your foxpro database.

  cheers,

  Paul June A. Domag

  
  
  

• Joe Kehnast

  i'm sure, this code will work in VB.net. if possible, can you please provide me VB 6.0 code.
  

• roundthebend

  I use code like the following to transfer from FoxPro to Access. This code may be dependent on the Jet engine but there should be a similar method for transfering to SQl Server.

  Public Sub Import()

  Dim ODBCDSN As String = ""

  Dim FoxProTableName As String = ""

  Dim AccessDatabaseFileName As String = ""

  Dim TargetTableName As String = ""

  Dim SourceTable As String = "[odbc;DSN=" & ODBCDSN & ";]." & FoxProTableName

  Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

  & AccessDatabaseFileName & ";"

  Dim sql As String = "INSERT INTO " & TargetTableName _

  & " SELECT DISTINCT * FROM " & SourceTable

  Dim MDBConnection As OleDbConnection = Nothing

  Dim Cmd As OleDbCommand = Nothing

  Try

  MDBConnection = New OleDbConnection(ConnString)

  Cmd = New OleDbCommand(sql, MDBConnection)

  MDBConnection.Open()

  Cmd.ExecuteNonQuery()

  Finally

  If MDBConnection IsNot Nothing Then MDBConnection.Close()

  If Cmd IsNot Nothing Then Cmd.Dispose()

  End Try

  End Sub

  
  

• ozhonetech

  Are you using VB to do this

  If not then perhaps the

  Foxpro forums
  http://forums.microsoft.com/MSDN/default.aspx forumgroupid=17&siteid=1

  Or SQL Server forums
  http://forums.microsoft.com/MSDN/default.aspx forumgroupid=19&siteid=1

  Would be a better place to ask a generic question about moving Foxpro data to SQL database

  
  

• Daniele Balducci

  I'm using VB.Net 2005 to do this so the Foxpro or SQL forums would not help me in this issue.

  I didn't mean for my question to sound generic. I was hoping for an answer something like...

  Here is how to access the .DBF free table:

  Here is how to write to the SQL Datbase:

  If I had the answers to the above questions, I could probably figure out the rest.

  I've been in the business for over 25 years and the .NET language has so far been the most difficult for me to grasp, primarily because of the lack of sample code and the over abundance of theory. In my research, I found very few samples of how to do a procedure that lists the steps from start to finish. And of the ones I did find, they came up short. Let's face it. How many times do we write business code that only displays a message box "Hello World!". I understand we have to start somewhere, but most all programs that I work with moves data into or out of some type of database. Finding examples of this is where I'm having the most trouble.

  Thanks for letting me vent. I feel much better now. Until next time, I'll keep following the the tried and true UFO method of learning. (U Figure it Out).

  
  

• ShEi

  Hi.
  
  Thanks Paul for a good suggestion. I'm not migrating from FoxPro Dbf to SQL. I need to synchronize some of the data from FoxPro Dbf to SQL.
  
  Any other suggestions
  
  
  

• LouisPeter

  Hi,
  
  Are you migrating your Forxpro database to sql If so, try using the Sql DTS. Data Transformation Service. This tool could import data from a foxpro database to a sql table and you can also specify what fields to import. To use, in your Enterprise manager right click the database and choose Import Data.
  
  
  
  cheers,
  
  Paul June A. Domag
  
  
  

• Brian Kinder

  I was just browsing the forum hoping to find help in transerring data from a Foxpro dbf to SQL and found this thread.

  My question is the same. How can I do this through code The reason for my question is that we have a lot of FoxPro data and I will be doing a lot of Data Transfers and synchronization.

  
  

• su45937

  I understand that I manually open both databases. What I'm having a little trouble grasping is how to move the data from the foxpro table into the SQL table. The above example shows a Select from Source and insert into Target. I'm sure that will work fine if the data tables are identical. In my case, they are not.

  For example, I have a Free table in Foxpro called ASSETS. Two of the The field values are

  AS_WELL_ID Character(12),

  AS_WELL_NM Character(35)

  In a new SQL Database, I have a table called ASSETS, but the field names are

  WellID Integer(04),

  Zone Character(03),

  WellName Character(35).

  In the Foxpro Datatable, AS_WELL_ID is 12 characters. The first 9 represent one part of the well. The last 3 represent another part. THE SQL normalizes the data better so I now have to manipulate my Foxpro data prior to updating the SQL.

  From what I've read, it looks like I have to create a Dataset and move my Foxpro field values into this dataset then do the update through the data adapter. Is that correct or is there a better way

  
  
• Transferring data from FoxPro dbf to Sql