Hi,
I am new to Toad and have Toad for Oracle 12.6.0.53 xpert edition.
Use case 1: I have to import data from excel files on a daily basis into tables. for the first import, the table has to be created and in subsequent imports the data has to be appended. It has to be scheduled.
Use case 2: same as above, but here data has to be imported from sql server database.
Please help with the steps.
Toad cannot create tables automatically during an import. In some cases it would have to take a guess on column sizing and datatypes, so it is best that you do that yourself. You can use the "Load Columns from File" button in the create table window to get close, then make adjustments as needed before clicking OK.
once the table exists, use automation designer in Toad to import tables. You can schedule it from there.
The only way that Toad for Oracle can import from SQL Server is if you can connect to SQL Server via ODBC. in the data import wizard, there is an option to import from ODBC.
Thank you John for your quick response. For use case 1, I will create table and good with it. The challenge is I cannot find option to import excel files but only text files from Automation designer. Also, as I am new please help on how to import from from excel file into existing table on a daily basis with scheduled job. Thank you.
Import Wizard in automation designer is here.
Pick your table, click Next, and you'll see a choice for Excel file after that.
After you get it set up, you can right-click the action (where it says "import table data1" to schedule it.
Thanks again. I am able to get to it. But, I wanted to insert date as example today's date, when I am importing today along with the data from excel file. How can I do it? Appreciate your help.
Make the default for that column in the table as SYSDATE, and then don't import the column from Toad.
Thank you John.
On use case 2:
The only way that Toad for Oracle can import from SQL Server is if you can connect to SQL Server via ODBC. in the data import wizard, there is an option to import from ODBC. Please point me to correct place in Toad and let me know the steps.
There is an option for ODBC right next to the Excel file option.
As for setting connecting to SQL Server with ODBC, I don't have steps for that, but it should be easy to find with Google.
Thank you John. I will explore and get back to you for any clarifications.
You need to set up a Data Source in the ODBC Data Source Administrator in Windows, but I don't know the specifics of that.
I will try and let you know. Thank you John.
John, for UC1, I used automation designer and setup import table data to import data from excel into table. When I run it, it shows progress and also the number of records imported without any error. But, when I query table, I see no records. I also run explicit commit, still no result. Anything that I missed here.
I just tested and for me the records were there.
By "Explicit commit", you mean you used this option here? You should use that.
If you go to the editor and just type in "commit" and run it, that might not work because that could be running in a different session.
I got it now. what is Use Array DML?
Array DML is your friend. It makes the import much faster. It inserts multiple rows for a single insert statement.
The only reason to not use array DML is that if some invalid data causes the import to fail, you might not know which record is the bad one (but you could always turn array DML off and then try again to find out).
It helps, thank you John.
I also want to add a check to see whether there are records with today's date before I import todays' report. can I do it in Automation designer?
Do you mean that you want to look at the Excel file or somewhere in the database?
if the database, I think you could use the "actionable query" (on the DB Misc tab) to accomplish that.
If the excel file, there is no way to do that, but maybe what you could do is import it into a different table in the database, then run some pl/sql to see if the data that you want is there and if so, copy it to the "real" table.
I will explore DB Misc option. Thank you John.
Hi John, in Execute Script option, can I call Stored Procedure instead of script text/file? Please suggest.
