Is it possible to populate new columns based on file names?
For example: when creating an import template, i set it up so that it adds a new column. Now depending on the file name, is it possible to set the data for that column?.
So if the file name contains "table_EXPO" - then populate the new column with data = "EXPO"
Short answer: The Import Wizard (since you mentioned "import template" which the wizard creates) can support query-based data migration which includes bind variables.
Also, I'm assuming that you want to do this in an automated way, and for more than one table at a time.
Loosely speaking, this can be accomplished by executing a Toad Automation script that includes setting of variables (either prompted at runtime, or derived) and the execution of your variablized import.
See screen snap below, where I've created a simple automation script that
- Sets a variable called "key"
- Executes the Import Wizard task.
The Import task uses an Import Template file that got created when using the Import Wizard previously. That template contains a query involving a bind variable called "key", whose value is set (actually I get prompted for the value in my flow) in the step previous. For my specific example, the query I used looks like:
SELECT
FROM ContosoRetailDW.dbo.DimCustomer
WHERE CustomerKey < :key
I'm assuming here you have a good way to strip off of the file name the string part you're interested in (a callable batch/edit utility, perhaps?) and this step would need to be the very first in your automation flow. Second step would be to set a Toad Automation variable value to that "file name" part value, and this value can be sent to the final Import step.
Hope this gives you some ideas.
Thank you for you help.
3 more questions:
1 - how do i make an import template include a variable
2- how do i set the toad automation variable value to the file name
3- is the process of running this import automation going to happen in the command prompt?