Import Data from a Huge .dbf (FoxPro Database) File

Hi Folks,

I recently had a task where I had to import data from a huge .dbf (FoxPro Database) file with 1 table having more than 10 lakh records into a MS-SQL table for processing. After lot of research and trying several solutions, I finally figured out a simple way. Just follow the below listed steps.

Before you go ahead, you will need the following:

1. A location to save the .dbf file which is accessible by your MS-SQL server.
   (It may be a folder on the server where your MS-SQL server in installed or may be a shared folder that can be accessed by server on which MS-SQL server is installed.)
   
   
2. The user you are using to login into MS-SQL server must have sufficient rights to enable and disable SQL configuration settings. The user should preferably be of a sysadmin role.

Steps

1. Save your file at a location which is accessible by the server where MS-SQL server is installed.
   (You can save the file via web upload for dynamically importing data each time a new file is uploaded or for a one time activity, you can save the file manually.)
   
   
2. Run the attached SQL script on your MS-SQL database and create the stored procedure required for importing the data
   
   
3. Pass the directory path (where the Foxpro database file resides) and file name (with file extension i.e. FileName.dbf) to the procedure and execute it. The output should be data in your .dbf (foxpro database) file.

Explanation

1. The stored procedure (uspImportDataFromDbf) takes two parameters i.e. ‘FolderPath’ and ‘FileName’ in sequence.
   
   
2. The first parameter i.e. FolderPath is the folder where your foxpro database file resides.
   
   
3. The second parameter i.e. FileName is the full file name of your foxpro database file including extension. Example: MyFoxProDbFile.dbf
   
   
4. The procedure first enables ‘show advance options’ and then enables ‘Ad Hoc Distributed Queries’ that are required for querying the database file, in sequence.
   
   
5. Finally, using the JET.OLEDB driver, we read the data from the foxpro database file.
   
   
6. After reading the data, we disable the settings in reverse sequence i.e. first disable ‘Ad Hoc Distributed Queries’ and then disable ‘show advance options’.

Now, you can use this data as to your like. Feel free to provide your comments.

Note: In my case, the foxpro database file had only one table, so the stored procedure worked straight away. If you have more than one table, you may need to customize the stored procedure.