How to import a DBF file into SqlServer?

While the DBF file could have been produced by any number of applications, the most common thing in recent years has been the Microsoft Visual FoxPro application.

The good news is that there is an ODBC driver for VFP DBF files and you can use that along with SQL Server's "Import and Export Data" utility to read it in.

VFP ODBC Driver:

http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

jhance's solution holds true, but if u want to update the SQL server through VFP code you will need to connect to the SQL server and insert the records
if you need that, i have the code

I have used code like this to simply import DBF files.  This can be run from Query Analyzer.

select * Into myData from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\myServer\F$\Temp\','select * from myData.dbf')

Some pertinent notes:

1.  My SQL table, myData, had a comparable structure to the myData.dbf file.
2.  You have to have the dBaseIII driver on your system.  I'm sure this was installed via MDAC but I could be wrong.
3.  Make sure you replace my path statement with your path statement.  This is the section after DATABASE=.  Don't forget to include the trailing \.
4.  Obviously, myData.dbf is the exact DBF filename you want to import.
5.  If you're not working with a dBase DBF file, replace that section of the code to use the VFP driver.

cmangus,

Thanks. I will try it tonight.

Is there any way to use SqlServer's export/import data functionality to do
it instead of using the sql statement?

Thanks,

Jennifer

I'm sure you could work it up using the Import/Export Wizard or in DTS.  My task was a one time task that was more suited to Query Analyzer.

If your DBF file is dBase, you already have the driver on your machine if you've installed MDAC.  If your DBF is FoxPro you will need to install that driver separately.

Above, jhance has given you a link for the driver.

Thanks.
My DBF is FoxPro.

I will try both methods tonight.

For now, I try to use DTS. All I need is to load a file abc.dbf
into SqlServer.

After starting DTS, it asks for

1. Data Source, I chose Microsoft VFP Driver (*.dbf)
2. User/System DSN, which I don't know what to type
3. User id, which I don't know what to type since all I have is
   a abc.dbf.
4. Password. which I also don't know what to do with.

Does anyone know the answers to 2,3,4?

Thanks a lot,

Jennifer

I would use a system DSN.  You will likely have to create a new DSN pointing to the .DBF file.  You shouldn't need a user ID or password.

DTS keeps giving me error msg: File abc.dbf is not a database.

Why?

Now I tried it out.

In DTS, I choose DBASEIII and put in the dir of the abc.dbf file instead of VFP Driver. And it worked.

Thank you very much.