All Forums Tools
kae1 6 posts Joined 08/11
08 Sep 2011
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:
Q 6 posts Joined 10/09
08 Sep 2011

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

 

 

 

mnylin 118 posts Joined 12/09
08 Sep 2011

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.

feinholz 1234 posts Joined 05/08
08 Sep 2011

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

kae1 6 posts Joined 08/11
09 Sep 2011

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.

nancyjean 5 posts Joined 02/13
14 Mar 2013

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

TonyL 20 posts Joined 12/09
14 Mar 2013

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

You must sign in to leave a comment.