How to go from DBF files to SQL Server 2008?

Question

I have an application that uses DBF files and I need to import them to SQL Server 2008. However, I also need to modify some of the data along the way and some columns will be added to tables while others will be deprecated.

So far I'm using DBF -> Access -> MS Migration Assistant -> SQL Server 2008. But I think that there has to be a better way to handle it. I'd like to get away from the Migration Assistant because it doesn't let you modify the data when you import it.

Enzyme

Answer 1

It seems you're out of luck :-(

See this note:

Note: In SQL Server 2005, the SQL Server Import and Export Wizard does not support importing from or exporting to dBASE or other DBF files. To import DBF files, first use Microsoft Access or Microsoft Excel to import the data from DBF files into an Access database or Excel spreadsheets. Then, use the SQL Server Import and Export Wizard to import the Access database or Excel spreadsheets that contain the data from the DBF files.

Microsoft SQL Server 2005 Books Online, Choose a Data Source (Import and Export Wizard)

But maybe this other Stackoverflow question can be of help??

How to import a DBF file in SQL Server

Or check out some commercial and/or shareware tools for the job

• EMS Data Import for SQL Server
• DBF to SQL Converter seems to convert DBF file content into SQL scripts
• Full Convert Enterprise

Malayoindonesian

Answer 2

I was able to import DBF into SQL Server 2008 by using the information at this link.

In particular I:

1. Used Import/Export wizard, selecting the "Microsoft Office 12.0 Access Database Engine OLE DB Pro..." as the Data source in the dropdown.
2. Opened the Properties dialog for the Access OLE DB connection.
3. Specified the folder containg the DBFs as the Data Source.
4. Set Extended Properties to "dBASE 5.0"

I then set destination as my local sql server and upon hitting next was able to see each DBF file in the folder as a possible table/view to import from. Clicking on them created the similarly named table in the specified database.

Weatherwise

I'm getting issue with the destination table ... how do you know what column name to have in your table ? – Nidia

This worked perfectly for me. I had kept Access and old 97-2003 format MDB files around just to work around this import problem in SQL Server an now I can finally dump them. – Augustineaugustinian

Answer 3

Since you apparently just want to import the data into SQL Server, I'd just use SQL Server's Import and Export Data, which will work with any data source for which you have an ODBC driver.

Recognizee

Answer 4

From Books Online it appears that while the import/export wizard won't let you use a .dbf file, SSIS can be configured to use one:

To configure a connection manager to connect to a dBASE or other DBF file Add a new OLE DB connection manager to the package. For more information, see How to: Add or Delete a Connection Manager in a Package.

On the Connection page of the Connection Manager dialog box, select Native OLE DB\Microsoft Jet 4.0 OLE DB Provider as the Provider.

When working with DBF files, the folder represents the database, and the individual DBF files represent tables. Therefore the Database file name text box must contain the path of the folder where the DBF file resides, and must not include the file name itself. You can type or paste in a folder path, or you can use the Browse button to select your DBF file, and then remove the file name from the end of the folder path.

On the All page of the Connection Manager dialog box, enter dBASE III, dBASE IV, or dBASE 5.0, as appropriate, as the value of Extended Properties.

Click Test Connection to validate the values that you have entered. You should see the message, "Test connection succeeded." Click OK to close the message box.

Click OK to save the configuration for the connection manager.

To use your connection manager in the data flow of the package, select an OLE DB source or destination and configure it to use the connection manager that you created by using the preceding steps.

Signesignet