jfz2004
asked on
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
How to do that? I am new to both SqlServer and DBF.
Thanks for any help.
Jennifer
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
if you need that, i have the code
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 =\\myServe r\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.
select * Into myData from OPENROWSET('MICROSOFT.JET.
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.
ASKER
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
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
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.
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.
ASKER
Thanks.
My DBF is FoxPro.
I will try both methods tonight.
My DBF is FoxPro.
I will try both methods tonight.
ASKER
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
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
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.
ASKER
DTS keeps giving me error msg: File abc.dbf is not a database.
Why?
Why?
ASKER
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.
In DTS, I choose DBASEIII and put in the dir of the abc.dbf file instead of VFP Driver. And it worked.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.
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