Import .dbf files in sql server 2005

Question

Import .dbf files in sql server 2005

bladerunner148

SSCommitted

Points: 1824
More actions
November 17, 2008 at 1:55 pm

#383408

Hi there,
I have a .dbf file which i want to import in sql server 2005. When I right click on the database and click Import, I don't know what Data Source I should be using. Is it even possible to import .dbf files?
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Choose a data source based on what created the .dbf file (dbase, Foxpro,?). If you can't find one in the list, you might be able to find an ODBC driver for the source or you might have to save the data from the source dbms to a flat file that you can import to SQL Server.

Greg

bladerunner148 SSCommitted Points: 1824 More actions · Answer 2

I didn't get it when you said i can change the dbf to a flat file. Could you please specify what do you mean when you said change that to a flat file? What extension will that be?

Thanks!

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

If you do not know what created the DBF files, try using the built-in DBase driver.

If that does not work, try using FoxPro. As Greg said, you might have to download a driver for this.

Phil

BTW a flat file is just a text file - eg a CSV file.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dhans Ten Centuries Points: 1255 More actions · Answer 4

Instead of using Import/Export wizard, You can import DBF files using SSIS Packages.

Steps:

1. Create an SSIS Package using BIDS.

2. In the Connection Managers

Right Click --> Create OLE DB Connection-->New--> In the Provider column, Select Microsoft Jet 4.0 OLE DB Provider--> Type the DBF file location in Database file name column.

for eg: C:\SSIS\DBFFiles\ (DO NOT TYPE THE DBF FILE NAME)

3. Press ALL button in left side --> Advanced-->Extended Properties Type : dbase 5.0 Press OK.

4. Now the Connection is created.

5. Create Destination SQL Server connection for storing the dbf data.

5. Drag the Data Flow task

6. In Data flow task, Drag OLE DB Source and OLEDB Destination tasks

7. In Ole Db Source, select the connection manager as newly created Dbase Connection.

8. Select your DBF File in Name of the table or View.

9. Connect OLE DB Source and OLE DB Destination and Map the Source and Destination Columns.

10. Run the Package..... thats all..

amacwan SSC Rookie Points: 41 More actions · Answer 5

Hi,

Thanks for the posting. I am trying to import .DBF files into sql server 2005 using the same method. Can I also import .DBF files from access database where these .DBF files are linked?

I used the above method and using folders containing .DBF files. Connection is not set up, i m getting errors "...could not find installable ISAM"...please advise.

Thanks

Ashish

Jay_Noob SSCommitted Points: 1565 More actions · Answer 6

I just wanted to throw out an FYI that tripped me up with something similar to this. We are running 64 bit servers and there is no 64 bit FoxPro driver. To run the package on a 64 bit server from BIDS I had to go to Project (menu bar) -> "project name" Properties -> Debugging and select False for the "Run64BitRuntime" option.

info-658497 SSC Veteran Points: 252 More actions · Answer 7

Hi Dhans, Thanks for the helpful post.

Is it possible to link my SQL table to the DBF file? My data is captured at the backend by a 3rd party app. I need to link to the app's files, and build the frontend from there.

Thanks again

Dhans (11/17/2008)
Instead of using Import/Export wizard, You can import DBF files using SSIS Packages.
Steps:
1. Create an SSIS Package using BIDS.
2. In the Connection Managers
Right Click --> Create OLE DB Connection-->New--> In the Provider column, Select Microsoft Jet 4.0 OLE DB Provider--> Type the DBF file location in Database file name column.
for eg: C:\SSIS\DBFFiles\ (DO NOT TYPE THE DBF FILE NAME)
3. Press ALL button in left side --> Advanced-->Extended Properties Type : dbase 5.0 Press OK.
4. Now the Connection is created.
5. Create Destination SQL Server connection for storing the dbf data.
5. Drag the Data Flow task
6. In Data flow task, Drag OLE DB Source and OLEDB Destination tasks
7. In Ole Db Source, select the connection manager as newly created Dbase Connection.
8. Select your DBF File in Name of the table or View.
9. Connect OLE DB Source and OLE DB Destination and Map the Source and Destination Columns.
10. Run the Package..... thats all..

Dhans Ten Centuries Points: 1255 More actions · Answer 8

I am sorry, I have no Idea of linking SQL Table to DBF file.

Dhans:unsure:

Jay_Noob SSCommitted Points: 1565 More actions · Answer 9

I've never done this, but you might be able to use OPENROWSET() to select from your DBF file and stick that in a view.

Karen Roslund SSCommitted Points: 1724 More actions · Answer 10

Download & Install "Microsoft OLE DB Provider for Visual FoxPro 9.0" from...

http://www.microsoft.com/downloads/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

Katharine Mahon Grasshopper Points: 19 More actions · Answer 11

Hi Dhans,

Thank you for the post. It works like a cham......I imported a database III dbf file to a sql table. I was failed all the time until I read your post - number 3 - don't type the file name 🙂

Dhans Ten Centuries Points: 1255 More actions · Answer 12

I posted whopping 3 years ago.. Glad it helped you today..:-D

rocks SSC Eights! Points: 988 More actions · Answer 13

hi, what about foxpro 9.0, do i have to write foxpro 9.0 for foxpro connection.

rocks SSC Eights! Points: 988 More actions · Answer 14

hi what is your legacy system, it foxpro 9.0 or dbase.

as i have downloaded foxpro 9.0 oledb driver, but when i am using connection it's giving me code page error, even though it runs, but it dumps junk values. to table.

i'll appreciate your quick reply.