none
importing Visual Foxpro tables into SS2005

    Question

  • Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver

     

    I have an origin data folder with many foxpro dbf files

     

    When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine.  The form shows me a list of the files in the folder - but I cannot scroll through the list.

     

    When I select the folder and continue, I test the connection and get a message that it is fine.

     

    Then I go on to actually import tables.  The list that is presented of tables that I can import does not include all the dbf files in the folder.  There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.

     

    Does anyone know what could be causing this strange behavior?

     

    Thanks.

    Thursday, June 28, 2007 7:33 PM

Answers

  • Cindy,

    I found the problem.

    My client gave me a cd with what he (and I) believed was all the database files that needed to be used.  The cd had only *.dbf files - certainly looked like all the database files.  However, some of the tables has associated *.fpt files (which hold associated "memo" content).  Apparently when any of the drivers are asked to present a list of database files to be connected to (for whatever purpose), if there ought to be an associated fpt file and one is not found (same base name and in the same folder) then even though a *.dbf file exists, it is not presented in the list.  Therefore, whatever method I used, I could not get to the data I needed.  Now that I tracked down the corresponding *.fpt files everything is working fine.

     

    Thanks for you help.

    Thursday, June 28, 2007 10:20 PM

All replies

  • Hi Dave,

     

    When connecting to FoxPro data, if your tables are free tables then you point your connection only to the directory where the DBFs are stored. You access specific tables in your query or import process. With a linked server it would look like this:

     

    Code Snippet

    EXEC master.dbo.sp_addlinkedserver
        @server = N'dTemp',  -- Your linked server name here
        @srvproduct=N'Visual FoxPro 9',  -- can be anything
        @provider=N'VFPOLEDB',
        @datasrc=N'"D:\Temp\"',
        @provstr=N'VFPOLEDB.1'

     

    Select * From dtemp...MyFreeTable

     

    If there is a DBC (Database Container) file present then you would point your connection toward it, and again, specify tables in your query or import process. Again, a linked server example:

    Code Snippet

    EXEC master.dbo.sp_addlinkedserver
        @server = N'dTemp',  -- Your linked server name here
        @srvproduct=N'Visual FoxPro 9',  -- can be anything
        @provider=N'VFPOLEDB',
        @datasrc=N'"D:\Temp\MyDBC.dbc"',
        @provstr=N'VFPOLEDB.1'

    Select TestDeleted.* From dtemp...MyJoinedTable

     

    It sounds to me like your directory has both Free tables and tables that are joined to a DBC. Is there a DBC file present? If you try connecting to it do you see the other tables in the list?

     

    Thursday, June 28, 2007 9:31 PM
  • Cindy,

    I found the problem.

    My client gave me a cd with what he (and I) believed was all the database files that needed to be used.  The cd had only *.dbf files - certainly looked like all the database files.  However, some of the tables has associated *.fpt files (which hold associated "memo" content).  Apparently when any of the drivers are asked to present a list of database files to be connected to (for whatever purpose), if there ought to be an associated fpt file and one is not found (same base name and in the same folder) then even though a *.dbf file exists, it is not presented in the list.  Therefore, whatever method I used, I could not get to the data I needed.  Now that I tracked down the corresponding *.fpt files everything is working fine.

     

    Thanks for you help.

    Thursday, June 28, 2007 10:20 PM