How can i retrieve datas (using sql) from Excel to a table in Oracle database .I am using dbsaint.
Insted of DBSAINT which developer tool shold i use for this purpose
Thanks
A:
The easiest way to do this is to export the data from Excel into a CSV file. Then use an external table to insert the data into your database table.
Exporting the CSV file can be as simple as "Save as ...". But watch out if your data contains commas. In that case you will need to ensure that the fields are delimited safely and/or that the separator is some other character which doesn't appear in your data: a set of characters like |~| (pipe tilde pipe) would work. Find out more.
External tables were introduced in Oracle 9i. They are just like normal heap tables except their data is held in external OS files rather than inside the database. They are created using DDL statements and we can run SELECTs against them (they are read only). Find out more.
Some additional DB infrastructure is required - the CSV files need to reside in an OS directory which is defined as an Oracle dictionary object. However, if this is a task you're going to be doing on a regular basis then the effort is very worthwhile. Find out more.
I don't know much about DbSaint; it's some kind of database IDE like TOAD or SQL Developer but focused at the cheap'n'cheerful end of the market. It probably doesn't support this exact activity, especially exporting to CSV from Excel.
APC
2010-08-04 10:21:27
Insted of DBSAINT which developer tool shold i use for this purpose
2010-08-06 01:41:38
@user410565 - all I'm saying is, it's a manual process. You can probably use DBSAINT to run the code.
APC
2010-08-06 05:12:16