[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1748
  • Last Modified:

How to import a DBF file into SqlServer?

I have a *.DBF file I'd like to make it into SqlServer.
How to do that? I am new to both SqlServer and DBF.

Thanks for any help.

Jennifer
0
jfz2004
Asked:
jfz2004
1 Solution
 
jhanceCommented:
While the DBF file could have been produced by any number of applications, the most common thing in recent years has been the Microsoft Visual FoxPro application.

The good news is that there is an ODBC driver for VFP DBF files and you can use that along with SQL Server's "Import and Export Data" utility to read it in.

VFP ODBC Driver:

http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

0
 
DeepDanCommented:
jhance's solution holds true, but if u want to update the SQL server through VFP code you will need to connect to the SQL server and insert the records
if you need that, i have the code
0
 
Chris MangusDatabase AdministratorCommented:
I have used code like this to simply import DBF files.  This can be run from Query Analyzer.

select * Into myData from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=\\myServer\F$\Temp\','select * from myData.dbf')

Some pertinent notes:

1.  My SQL table, myData, had a comparable structure to the myData.dbf file.
2.  You have to have the dBaseIII driver on your system.  I'm sure this was installed via MDAC but I could be wrong.
3.  Make sure you replace my path statement with your path statement.  This is the section after DATABASE=.  Don't forget to include the trailing \.
4.  Obviously, myData.dbf is the exact DBF filename you want to import.
5.  If you're not working with a dBase DBF file, replace that section of the code to use the VFP driver.

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
jfz2004Author Commented:
cmangus,

Thanks. I will try it tonight.

Is there any way to use SqlServer's export/import data functionality to do
it instead of using the sql statement?

Thanks,

Jennifer
0
 
Chris MangusDatabase AdministratorCommented:
I'm sure you could work it up using the Import/Export Wizard or in DTS.  My task was a one time task that was more suited to Query Analyzer.

If your DBF file is dBase, you already have the driver on your machine if you've installed MDAC.  If your DBF is FoxPro you will need to install that driver separately.

Above, jhance has given you a link for the driver.
0
 
jfz2004Author Commented:
Thanks.
My DBF is FoxPro.

I will try both methods tonight.
0
 
jfz2004Author Commented:
For now, I try to use DTS. All I need is to load a file abc.dbf
into SqlServer.

After starting DTS, it asks for

1. Data Source, I chose Microsoft VFP Driver (*.dbf)
2. User/System DSN, which I don't know what to type
3. User id, which I don't know what to type since all I have is
   a abc.dbf.
4. Password. which I also don't know what to do with.

Does anyone know the answers to 2,3,4?

Thanks a lot,

Jennifer
0
 
Chris MangusDatabase AdministratorCommented:
I would use a system DSN.  You will likely have to create a new DSN pointing to the .DBF file.  You shouldn't need a user ID or password.
0
 
jfz2004Author Commented:
DTS keeps giving me error msg: File abc.dbf is not a database.

Why?
0
 
jfz2004Author Commented:
Now I tried it out.

In DTS, I choose DBASEIII and put in the dir of the abc.dbf file instead of VFP Driver. And it worked.
0
 
csachdevaCommented:
The best way to do - without any problems (if You have to do it ONE time) is using MS Access. I had the same problem 6 months ago. Import/attach the dbf files to an access database, connect to the SQL Server thru ODBC and add the rows to the prepared table(s) in SQL Server. Then You can add validity check and/or indices...

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.  

Another way is to transfer data is that convert dbf 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.

The only way I found you could do this was to read the data from Foxpro into flat files and then BCP the data in.
To do this I used ADO connecting to the foxpro files using the foxpro provider and then looped though and created comma delimited files.

Hope this will answer your query.

Regards,
Chetan Sachdeva
0
 
jfz2004Author Commented:
Thank you very much.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now