how to load multiple files into one file using informatica

Question

I am new to informatics, I have created a mapping that using expression and sorter transformation to load multiple files into one single file which have 2 columns

1 data
2 seq number

All 10 files have random sequence numbers Like example: file1

erfef 3
abcdn 1

file 2

wewewr 4
wderfv 5

and so on till 10 files.

Expression transformation code is :

INTEGER(LTRIM(RTRIM(seq_num)),TRUE)

what I want is to load the file into one big file and sort it according to the seq number.

Got data in output file but number with incorrect seq number.

How to get data in the final table with a correct sequence number.

doing exactly what is mention in the below solution but still getting wrong output. getting output like:

where as it should be like below:

                                                                       where as it should be like 

abcdn 1                                                                                                                                
erfef  3                                                                                                                          
wewewr 4  
wderfv 5

Thanks in Advance !!!

score 2 · Accepted Answer · answered Mar 01 '22 at 14:22

2

Use indirect file load using a list of files to load all files together. Then use sorter on col2 to order the data. Finally use a target file to store data.

Whole mapping should be like this -

SQ --> EXP--> SRT(key = col2) --> Target

Few things to note -

In the session, use indirect file and use a list file name - mention filelist1.txt
Use ls -1 file* >filelist1.txt in pre session command task to create a file list with all required files.
Expression transformation- convert the col2 to INTEGER if its coming up as string in SQ.
Sorter transformation- use col2 as key column.

answered Mar 01 '22 at 14:22

Koushik Roy

6,868
2
12
33

doing exactly what is mention above but still getting wrong output. getting output like: – Amitesh Mar 01 '22 at 14:44
i have to put both column as key otherwise the seq_num value will be 0 for each row – Amitesh Mar 01 '22 at 15:27
did your solution worked for you? – Koushik Roy Mar 02 '22 at 03:38
nope it got truncate issue but not able to understand is it due to size or datatype – Amitesh Mar 03 '22 at 07:18
make data type to string in source qualifier and source and increase data length to 2000 - and then in EXP convert string to INTEGER. If this also fails pls check if the delimiter in file and informatica are same. – Koushik Roy Mar 03 '22 at 08:07
what about target source and target are same both are flat files – Amitesh Mar 03 '22 at 09:48
it doesnt matter - source and target can be files. – Koushik Roy Mar 03 '22 at 17:55
1

their is some issue in the length of the file . i updated and volla it work fine thanks for the help – Amitesh Mar 15 '22 at 06:23

score 0 · Answer 2 · answered Mar 03 '22 at 18:55

Using indirect file source is one way.

Another way is to use command as source and specify a command that will spit out data from all the files, like cat file*.csv.

Just change the Input Type to Command and provide the command - all this can be set by editing session -> mapping tab -> Source -> properties.

Here's an example session:

how to load multiple files into one file using informatica

2 Answers2