Post reply

SQL query to read in data from a DBF file

  • September 23, 2009 at 10:50 am

    #215606

    I have been trying for days unsuccessfully trying to find a way to run a SQL query to read in data from a dbf file. I can open the file (named punch.dbf) just fine using excell, and the tab for the sheet is called "PUNCH", and see all the data just fine. The file was generated by a time punch card system and then exported to this dbf file.

    After many tries of using openrowset and opendatasource, the closest I think I have come is using the following command from a SQL Query window.

    SELECT * FROM OPENDATASOURCE( 'sqloledb', 'Driver=Microsoft FoxPro VFP Driver;SourceType=DBF;SourceDb=C:\work\vantage\punchcard\punch.dbf;User Id=D3;Password=vantage' )...PUNCH

    The error I am getting is:

    "Server: Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI" for linked server "(null)" does not contain the table "PUNCH". The table either does not exist or the current user does not have permissions on that table."

    I have had a very hard time finding any documentation on any of the providers for either the openrowset or opendatasource, especially for the dbf file format.

    Any help is appreicated!

    Thanks in advance!

    ---JHG

  • September 23, 2009 at 9:46 pm

    #1057858

    You may want to check out this link.

    http://www.sqlservercentral.com/Forums/Topic285021-146-1.aspx

    I used the process similar to originsone in the thread, where I created a System DSN using the Driver do Microsoft dbase driver, then I created a linked server using the microsoft oledb drivers for ODBC referencing the new system DSN and product Microsoft Visual FoxPro Driver.

    Once complete I was able to access data in a *.dbf via openquery

  • September 24, 2009 at 10:15 am

    #1058138

    Thanks for the reply. I'll look that over.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply