Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Import .dbf files in sql server 2005 Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 1:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 01, 2011 1:41 PM
Points: 102, Visits: 303
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
Post #603932
Posted Monday, November 17, 2008 2:10 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 4,056, Visits: 5,180
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
Post #603942
Posted Monday, November 17, 2008 2:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 01, 2011 1:41 PM
Points: 102, Visits: 303
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!
Post #603959
Posted Monday, November 17, 2008 3:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 4,828, Visits: 11,177
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #603977
Posted Monday, November 17, 2008 4:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 135, Visits: 252
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..
Post #604012
Posted Monday, December 15, 2008 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 6:07 PM
Points: 1, Visits: 44
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
Post #619922
Posted Tuesday, February 03, 2009 9:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:54 AM
Points: 59, Visits: 236
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.
Post #648982
Posted Wednesday, February 11, 2009 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 6:10 PM
Points: 2, Visits: 50
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..
Post #655231
Posted Wednesday, February 11, 2009 2:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 135, Visits: 252
I am sorry, I have no Idea of linking SQL Table to DBF file.

Dhans
Post #655264
Posted Wednesday, February 11, 2009 2:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:54 AM
Points: 59, Visits: 236
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.
Post #655270
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse