how to import data from DBF to SQL using SQL script?

Question

I am trying to import data from DBF file to SQL table using the following command -

select *
from openrowset('MSDASQL', 
'Driver={Microsoft dBase Driver (*.dbf)};DBQ=E:\data\;',
'select * from E:\data\a.dbf')

But it is failing saying

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC dBase Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x120 Thread 0x3084 DBC 0x303dfbc Xbase'.".    
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC dBase Driver] Disk or network error.".    
Msg 7303, Level 16, State 1, Line 1    
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Any clue why it is giving error? For the second error I have given full access to temp folder as suggested by some blog. Still it is showing both error.

Answer 1

Your SELECT is specifying the path to the file - you should be specifying the table name instead, which is presumably 'a'.

Answer 2

To supplement CodeByMoonlight, the Driver information points to the path the data file is found, so then your subsequent query should only be "Select * should from a" since both the path and the .dbf extension should be implied