UPDATE 2008-11-05! A console version is available here
Since I had more and more requests for DBF migrations to Oracle and SQL Developer offers no support for DBF migrations, I decided to make a tool to ease my work. There used to be a command prompt tool (ImpDBF.exe) which generates the equivalent table for Oracle and SQL*Loader scripts for importing the data. This tool was for Oracle 7 and 8 and I don’t have the source code for this tool.
The workaround was to create a frontend for that tool that can also fix problems in generated files.
The result is the like this:
How to use it:
- Make sure that you have .Net Framework 2.0 installed
- Make sure that you have an available Oracle 10g or 11g database
- Download the DBF2Oracle from here
- Unzip it into a folder close to root folder and avoid spaces in path
- Copy the .DBF file to the above folder or put it into the same kind of folder
- Run the application, supply mandatory parameters and click “Load data”
- Check the log and statistics file
- Connect to Oracle to see your data (table name is DBF file name)
Download here: DBF2Oracle.zip
Known bugs:
- columns named as reserved words in Oracle will fail
- long or complicated paths for both application or DBF file will fail
- multiple Oracle homes on the same machine can confuse the user
In the end you will see such a screen:
If you will find it useful or if you have any kind of suggestions just leave a comment.
Good luck!
Read also:
Entries (RSS)
Hello,
This tools is just what I was looking for, load a dbf file into ORACLE 10g.
But, I have problems with numbers that are stored as 9.93100000000e+003 where I expect 00000009931
Do you have any work arround for that problem.
Thanks in advance,
Vincent
Hi Vincent, can you tell me what you get instead of 00000009931. You can also try to send me (andrei [at] webxpert [dot] ro) a simplified DBF file to test and correct the issue.
Andrei
Hi Andrei, I get the string : 9.93100000000e+003 instead of a string like 9931 with 0 or white space before.
I send you a reduce file (the one I try to load is 140000 rows).
Thanks for your quick answer,
Vincent
Hello,
sorry for my english, i’am french. Can i have the source of your file in .net. I have to use the code for import dbf file into oracle database but automatiquely. thank to send me your code source.
Nicolas
Nicolas,
I can’t give you the source code. You can either make your own graphical interface for impDBF, as I did, or tell me what you want and I can do this.
Andrei
Ok, thanks you for your response.
I need first to run your application in ms dos console. like this
start dbfimport.exe [link of source file] [user_db] [password] [TNS]
after to do this then close the application, i have need to do this because i have to run every night a batch for a lot of file dbf to save in oracle database.
Thanks
in the link i want manage the option “drop table if already exist” and “keep tempory file”
Thank
I remember that I did a “batch” version of the importer. Now you have a good idea by starting it from console, so I will also leave this option. One of the next days I will make a new post on this. Just check my RSS feed.
Sorry to disturb you. I have an other problem. When i have the colum specified number in my dbf file and the data in the dbf file like ‘30.45’, your program can’t insert data into oracle. You have to replace caracter ‘.’ by ‘,’ . Can you update you program then when you have a column define that number you have to replace ‘.’ by ‘,’ in the colums before to insert it.
thanks
This is not a general issue. It appears for you because you have France locales and this means that you have comma as decimal separator. You can easily overcome this by changing NLS_NUMERIC_CHARACTERS Oracle parameter.
Something like this:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'and of course you can read the manual for more information.
Andrei
when i want to insert number into oracle i write ‘6.45’ the same in the file dbf and i have a problem with your program ORA-01722: invalid number. But if i replace in my file ‘.’ by ‘,’ i don’t have problem. why and where is the file .sql generate?
Nicolas, files are generated in the same folder as the executable, but they are deleted after import for space saving is you don’t check the option “keep temporary files”.
In your software file are generated in the same folder of the source file. Can you update your software this week to run it on ms dos console. It’s important for me, for a project. Thank you andreiash.
yes, it will be ready tomorrow
[…] I had a request and I must agree it really makes sense I developed a console version of the DBF2Oracle tool. Functionality is the same, just that you need to give it parameters from command […]
[…] public links >> dbf DBase DBF import to Oracle freeware tool Saved by xzibrastripesx on Wed 05-11-2008 Writing Workshops Saved by deivideoliveira on Wed […]
[…] http://www.webxpert.ro/andrei/2008/05/30/dbase-dbf-import-to-oracle-freeware-tool/ […]
Thanks Andrei, this is what I was looking for.
is this running under w7 ? I get error even if I run it as administrator.
Va rog, ati putea imbunatati aplicatia, astfel incat sa poata rula si pe serverele Oracle v 8.0.5 (in loc de sqlldr.exe, ar trbui sa fie sqlldr80.exe).
Multumesc,
Gabi.