FastLoad Loop Through Multiple Files for Single Table

Tools
N/A

FastLoad Loop Through Multiple Files for Single Table

I need to load numerous flat files using FastLoad.  Is it possible to loop through the file names in FastLoad so I don’t have to change the file name for each data file that needs to be loaded in the FastLoad script?

For example:

Let’s say I have flat files FileName1, FileName2, FileName3, etc.  They all need to be loaded to the same table TableName1.


.LOGON TDPID/USERNAME,PASSWORD


.DEFINE


    Field1 ...


  , Field2 ...


  , etc.


FILE=FileName1;


BEGIN LOADING TableName1 ERRORFILES TableName1_E1, TableName_E2;


INSERT INTO TableName1 VALUES ( . . . . );


END LOADING;


.QUIT

Do I have to change the "FILE=FileName1" for each file and run the script?  Or, is there a way to provide a list of file names for the FastLoad to loop through?

Tags (1)
6 REPLIES
Q_
N/A

Re: FastLoad Loop Through Multiple Files for Single Table

Kae1,

This is a sitting duck for TPT LOAD operation!!!  You can setup a dataconnector (it reads flat files) with a wildcard on the filename. 

eg Filename*

TPT will suck up the files in parallel vs. one at time if you looped fastload.  If the files are large, take the time to do TPT.

Q

N/A

Re: FastLoad Loop Through Multiple Files for Single Table

Q hit the nail on the head with his answer. TPT is perfect for this kind of thing.

Another option would be to use some kind of script wrapper (Windows Batch/*nix shell) that could accept the list of files and then iteratively kick off FastLoad scripts for each file. You'd want to look at some kind of variable replacement logic to dynamically drop a file name into a template FastLoad script. I don't have any examples of this, so I'd really recommend using TPT. So much easier.
Teradata Employee

Re: FastLoad Loop Through Multiple Files for Single Table

I think you can go one step further.

If you use the Easy Loader feature of TPT, you can do this from a command line without a script, using "tdload".

The syntax would be something like this:

$ tdload  -f "*.dat"  -t <tablename>  -h <tdpid>  -u <username>  -p <password>

There is a DevX article on how to use "tdload". We do not document that you can use the wildcard syntax but I believe it works.

"tdload" will generate the TPT script for you (and then delete it when it is done).

-- SteveF
N/A

Re: FastLoad Loop Through Multiple Files for Single Table

Thanks Q, mnylin, and feinholz for your responses!  TPT seems to be the answer.  I am very new to Teradata.  So I need to do some digging and learn more about TPT.

Also, I found the article on Easy Loader for TPT (http://developer.teradata.com/tools/articles/easy-loader-for-teradata-parallel-transporter) mentioned by feinholz.

Re: FastLoad Loop Through Multiple Files for Single Table

Hi,

How can I get TPT. I have TTU 13.10. I dont think i have TPT. so please help!

Teradata Employee

Re: FastLoad Loop Through Multiple Files for Single Table

TPT is available on the TTU 13.10 media titled "Teradata Parallel Transporter".