Import dbf to sql server 2005

Introduction:

dbf file or dbase file can be imported direclty in SQL Server 2000 by selecting the option in the wizard. But SQL Server 2005 does not provide such an option. There are few methods with which this task can be accomplished. For example, using SSIS (SQL Server Integration Services). Below is another method of doing it which i found to be simpler than using SSIS.



Steps to import dbase file (.dbf) to SQL Server 2005:

1. Create a System DSN

2. Open SQL Server 2005 Import/Export Wizard

3. Select ".Net Framework Data Provider for ODBC" in Data Source.

4. Enter the DSN Name that you have created. Click Next

5. Choose Destination database. Click Next

6. You will given an option to write a query. Click Next. Here you should write the query to get the data from dbf file. For example: SELECT * FROM Test.dbf

7. Click Next. Select the destination table or create a new one.

8. Click Finish. The data is imported to the selected destination table.


How to create a system DSN?

1. Go to Control Panel
2. Go to Administrative Tools
3. Click Data Sources (ODBC)
4. Select System DSN Tab.
5. Click "Add"
6. Select "Microsoft dbase driver (*.dbf)"
7. Click Finish
8. Enter name for the new system dsn created. (This name is used during importing in sql server)
9. Select the directory where the .dbf file is located. (Deselect Current directory if the file is located else where and then browse the location)
10. Click Ok. System DSN is created.