Solved

how to import data from .dbf file to MS SQL server?

Posted on 2004-09-13
15
492 Views
Last Modified: 2010-05-18
I have a file extended with .dbf and I want to transfer data from the file to MS SQL server 2000. Can you give me some idea how to do it? Thanks.
0
Comment
Question by:gyuan
  • 4
  • 4
  • 2
  • +1
15 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 12048605
1. Run dtswiz.exe and click next
2. Select dBase IV from combo, fill file name
3. Click Next
4. Fill SQLServer logon information
5. Select database
6. And so on
0
 

Author Comment

by:gyuan
ID: 12049289
Where can I get dtswiz.exe ? Thanks.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 12049446
It is in MSSQL tools installation (Query Analyzer,...)
In Programs it is named "Import and Export Data".

Try Start - Run - type "dtswiz.exe" - click on OK
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:CarlWarner
ID: 12049947
Be very careful in thinking the dBASE IV choice will work.  Depending on where that .dbf came from, it could easily be a table (.dbf) from the MS Visual FoxPro data environment that might just be different enough where a dBASE IV interface fails to load it as you would expect.  Just a note to let you know why it may not do what you want on the first try...
0
 

Author Comment

by:gyuan
ID: 12055912
I got the following error:

Error Source: Microsoft JET Database Engine
Error Description: External table is not in the expected format.
Context: Error calling OpenRowset on the provider.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 12059625
I am convinced you just found out it isn't a dBASE IV .dbf file.

Not being an avid MS SQL Server afficionado, I'm not sure what mechanism Data Transformation Services via the dtswiz.exe uses to grab that data.  Does it use available connectivity drivers in the way of ODBC drivers?  If so, you may in fact need the MS VFP ODBC driver to get to that .dbf file.  Since that driver hasn't been part of the MDAC package for quite a while now, you'll need to d/l it and install it separately, if it is in fact what DTS needs.

Visual FoxPro ODBC Driver
http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx
0
 

Author Comment

by:gyuan
ID: 12126444
Thank your for your information. I visited that page. Since the file VFPODBC.DLL exists in my system, I think the ODBC driver is there. The hard thing is that I only have the database files extended with .dbf and I was told that those files are dBase files. I even do not know what the version is. I really can not find the way to retrive data from the files.
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 125 total points
ID: 12126615
If you want to actually check the version of the VFP ODBC driver or to see whether it's installed (or not) even though you see the file, go into your ODBC Data Source Administrator [under Control Panel...Administrative Tools...Data Sources (ODBC)] and go to the Drivers tab.  On mine, when I scroll down the list of installed drivers, I see "Microsoft Visual FoxPro Driver" and to the right of it is the version number that is installed.  

My VFP ODBC driver is current and showing 6.01.8629.01.  If yours doesn't, that may be the problem.
0
 

Author Comment

by:gyuan
ID: 12400730
Sorry, based on the information submitted by the users, I still can not solve the problem. Any further help will be apprreciated.
0
 

Expert Comment

by:Frylock
ID: 14355066
Export the table to a CSV.

From within Foxpro, select the table and type

"copy to filename csv"

Then from the enterprise manager, import there and select the last option under source - text file. And you should be set.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 14355179
And if there are any memo fields involved, the CSV export will lose all of that.
0
 

Expert Comment

by:Frylock
ID: 14355315
Ok, fine. You can also create an actual database in foxpro, attach the loose table to it, and then import it using hte dtwiz. I have something like that running righ tnow.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question