Toad World® Forums

Automation Designer Folder Iteration Import File loses its column definition

I wrote an Automation Designer app to import all Excel files in a folder into one Oracle table. I am trying to import from the second tab in the Excel file(s), but the Import table definition loses it's column definition on the second pass and can't import the remainder files. Here is the order of operations:

File Iterator to Directory with extension .xlsm.

Copy file to a location with a static name.

Import the static name into table and direct the import to the second tab. The definition works well when the file is available to read setting up.

First loop or file works well, then the second file fails. I open the import table definition and the tab selected is checked, but the definition of columns (mapping) are gone.

It is possible that I can only import from the first tab? I moved the tab from the second position to the first position in all files and it works.

Appreciate any help and thank you in advance.

This is working OK for me using Toad version 13.1. What is your version #?

I set up my action like this:

image

When I created the Import Table Data action, I set it up with the first file in the folder, set it to import the 2nd sheet and tested it. Worked as expected. Don't forget to set the commit option on the last step.

Once that was done, I just changed the filename on it to %Folder% to match my folder iterator action, hit Apply and close. Then, when I right-click on the Folder Iterator action and choose "Run", it imported the 2nd sheet from each XLSX file.

If you need to look at the Data Import action after setting it up, you'll have to temporarily change the filename back to one of your existing files, make your changes, then apply and close, then re-open and only change the filename to %Folder% or whatever the name of your folder iterator is.

Not changing filename before looking at the action again might be why the column definition gets lost (because it can't find a file as you hit "next" and go through the wizard).

Just got back to this today. I followed your instructions and works like a charm. Thank you John.

1 Like