TDLoad to load data from multiple files into a single table

General
Enthusiast

TDLoad to load data from multiple files into a single table

Hello
I need to a load of set of files from one location to a target table. In the folder /x/home/revathi/, I have several files which have a naming convetion as

part_1_file1.txt

part_2_file1.txt

part_3_file1.txt

....

part_100_file1.txt

 

And I have tried to load the data using easy loader.
tdload -f '/x/home/revathi/'* -t tdload_generic_out -h hostname -u revathi -p ***** --TargetWorkingDatabase database1 --SourceTextDelimiter '~'

 

Even though file names are getting resolved in place of the wildcard character, I'm getting the below error

TDLOAD: TPT05502: Error: Invalid syntax. Hyphen is expected before '/x/home/revathi/part_1_file1.txt'. TPT version is: 15.10


I understand it is possible to achieve this using TPT as well. But I would like to create a generic script, with no table schema, that I can use in multiple places.

Could anyone help with this?

7 REPLIES 7
Teradata Employee

Re: TDLoad to load data from multiple files into a single table

Hi ChRevathi,

 

I understand it is possible to achieve this using TPT as well. But I would like to create a generic script, with no table schema, that I can use in multiple places.


TPT can infer on his own a schema from a table with this syntax :

 

define schema sch_generic from table delimited @TargetDatabaseName || '.' || @TargetTableName

Extremely useful !

You have to pass or set those target variables of course.

 

 

On your command line you put the star outside the quotes of the path, it may just be a typo for the forum but check that.

 

Enthusiast

Re: TDLoad to load data from multiple files into a single table

Hi Waldar

Thank you indeed for the suggestion. I have tried it and it is working.

 

I would also like to know why I'm not able to achieve the same with tdload. An user has commented in the below link that processing multiple files with tdload is working using wildcard *

https://community.teradata.com/t5/Tools/Easy-Loader-for-Teradata-Parallel-Transporter/td-p/39768/pag...

Reg * outside the quotes, yes I had put it there deliberately. If I put it inside the quotes, it is not being considered as wildcard character. Instead it is considered as literal and I'm getting the error File "x/home/revathi/*" does not exist.

Teradata Employee

Re: TDLoad to load data from multiple files into a single table

Try with *.txt - inside the quotes.

Enthusiast

Re: TDLoad to load data from multiple files into a single table

Hi Waldar

Still the same error

$FILE_READER[1]: DataConnector Producer operator Instances: 1
$FILE_READER[1]: ECI operator ID: '$FILE_READER-29430'
$FILE_READER[1]: TPT19231 file '/x/home/revathi/*.txt' not found.
$FILE_READER[1]: TPT19015 TPT Exit code set to 12.
$FILE_READER[1]: Total files processed: 0.

 

But I do have many files available in /x/home/revathi/

Teradata Employee

Re: TDLoad to load data from multiple files into a single table

And without quotes ?

tdload -f /x/home/revathi/*.txt ...

As I don't use tdload, I'm doing guesses.

Teradata Employee

Re: TDLoad to load data from multiple files into a single table

It appears that you can't use wild cards with a fully-qualified pathname.

Make /x/home/revathi your working directory and use an unqualified filename pattern. 

And the wild card definitely goes inside the quotes - you need to pass it to tdload and not have the shell expand it first.

 

Enthusiast

Re: TDLoad to load data from multiple files into a single table

This is working.

First I have set up my working directory as /x/home/revathi/.
Then I executed the below command:
tdload -f '*.txt' -t tdload_generic_out -h hostname -u revathi -p ***** --TargetWorkingDatabase database1 --SourceTextDelimiter '~'

And, Bingo, all files in the location have been processed.
Thank you very much Fred and Waldar for your help.