Four Stars

store the dynamic values in context variable

Hi team,

 

I have source oracle table and there is one column called txn_date. 

 

values are:

txn_date

02-06-2017

04-06-2017

07-06-2017

04-06-2017

 

Requirement: How to create folder based on date for examples:

folder 1: c;/Txn_dt_02-06-2017/

folder 2 :c:/Txn_dt_04-06-2017/

folder 3 :c:/Txn_dt_07-06-2017/

 

But no duplicate folder even if same records with same date arrive. 

 

and is possible to store single context variable  to store unique txn_dt values?

 

Thanks

Shree

 

  • Data Integration
39 REPLIES
Nine Stars TRF
Nine Stars

Re: store the dynamic values in context variable

Hi,

You can set a context variable value dynamically at runtime (context.yourVariable = "whatYouWant") but usaully for this king of usecase it's preferable to set a global variable. 

Your design process should look like:

tOracleInput -> tUniqRow (eliminates doublons on date values) -> tJavaRow (here set the desired global variable) ->  tSystem (to create directory)

 

To set the global variable, use the following:

globalMap.put("outputFolder", inuput_row.txn_date);

As there is not dedicated somponent to create an empty folder, use tSystem for this purpose with the following command:

"cmd /c mkdir " + ((String)globalMap.get("outputFolder"))

Hope this helps.


TRF
Community Manager

Re: store the dynamic values in context variable

Hi Shree
Do you want to store the source data to file under the folder or just create a folder without writing data?

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Ten Stars

Re: store the dynamic values in context variable

I see where @shong is going with the above. If you are intending to add data to the folder, there is no need to worry about this problem.

 

1) You cannot create a folder twice. Once it exists, it exists

2) When you want to write a file to a folder in Talend, you can set the file to create the folder if it does not already exist

 

Unless there is some other requirement here, you really do not need to worry about this.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi Shong.

 

Sound good, if we can store records as well based on dates. ex: 4th day records-> create folder(04-06-2017)/txn_dt_4th_data.csv. similar to other days as well.

 

Means time need to store the date values in context variable in single or mutiple context variable.

 

After that, i am going to use this context variable for some excel file creation based on some columns like gender='M' etc.

 

Thanks

Shree

 

 

 

 

 

Four Stars

Re: store the dynamic values in context variable

Hi,

 

As per suggestion whether it will create distinct folder based on date  unique values?

 

Thanks in advance.

 

Regards

Shree

 

Nine Stars TRF
Nine Stars

Re: store the dynamic values in context variable

I think I gave you the answer (or not?)

TRF
Four Stars

Re: store the dynamic values in context variable

Hi,

 

I have tried as your input. 

 

I have facing error while creating folder using tSystem component. I have attached screen shot for references.

 

One more point rather than using tUniquerow , we can override distinct in tOracleInput itself? 

 

Error message: 

A subdirectory or file null already exists.

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

The components that create output files have an option "Create directory if does not exist"

Better to use those so that the only directories created are ones with files in them. The component will only create a directory once.
Four Stars

Re: store the dynamic values in context variable

Hi,

 

Still i am facing same error.

 

I am attaching job for your reference. Could you please check it.

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

I'm afraid I cannot open your job since I am currently using an older version of Talend. But it is clear that you are trying to create a folder that already exists using the mkdir command. Instead of using tSystem to create directories why not use a tLogRow to print out the directories it is going to create to see how the duplication occurs.

 

Also note, you are trying to create a directory called "null". This happens in Java when you are concatenating Strings and one of those Strings is null. 

 

You need to refine your data collection and data validation.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

Still i am getting same error.i have tried with tLog_row, i getting null, How to track. i am attaching screen shot for references.

tJavaRow_1:

globalMap.put("C:/outputFolder", input_row.newColumn1);
output_row.newColumn2 = input_row.newColumn2;

 

Any inputs would be  helpful.

 

Thanks

Shree

 

 

Ten Stars

Re: store the dynamic values in context variable

You do not appear to be passing the value of input_row.newColumn1 to your output. You are setting your globalMap value (strangely to a key of "C:/outputFolder"....why?), but not passing the value to the output_row.newColumn1 column. Your tLogRow will be displaying this. We will need to see your tSystem configuration.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

Please find the attached screen shot.

 

Easy to trace where i am doing wrong.

 

Thanks

Shree

 

Ten Stars

Re: store the dynamic values in context variable

OK, I think I know what is going wrong. But it is difficult to tell for sure, so you will need to try this.

 

1) Disconnect your tJavaRow from your tUniqRow

2) Connect a tFlowToIterate to your tUniqRow

3) Use an iterate link from the tFlowToIterate to your tSystem component

4) In your tSystem component use the following code....

"cmd /c mkdir " + ((String)globalMap.get("row2.newColumn1"))

FYI the tFlowToIterate works by adding the row to the globalMap using the row name and column name as the key. Each iteration keeps those values for the whole of the flow of the iteration.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi ,

 

Thanks for suggestion.

 

Now, able to created directory based on unique txn_date.

Observation: if directory already exist it through error. 

error msg: A subdirectory or file 07-12-0012 already exists.

 

2) Year is not properly value. i will check this one.

 

Final Requirement: 

Source records:

Columns are:

txn_date, num 

12-06-2017,12

13-06-2017,24

12-06-2017,22

14-06-2017,24

12-06-2017,13

13-06-2017,24

 

 

tOracleInput_1 ==> tFileOutputExcel_1

 

1)  Create unique txn_dt directory. Ex:  12-06-2017, 13-06-2017 etc

2) Inside that directory create xlsx file. date_wise_load.xlsx ( like 12-06-2017)

3) Load that date records. 

Output File:date_wise_load.xlsx

txn_date,num

12-06-2017,12

12-06-2017,24

12-06-2017,13

 

4) If directory already exist though error and send notification either email. Keep job running.

 

Thanks once again for right direction

 

Regards

Shree

 

 

 

 

Ten Stars

Re: store the dynamic values in context variable

You do not need to do this. Below is a MUCH simpler way of achieving your goal.

 

1) Create the folder path String as before

2) Do not use a tSystem, use a tFileTouch component to create a file (you choose the name) in the folder you want to create. Select "Create direcory if does not exist".

3) Delete the file you have just created

 

There is no need to keep track of what folders you have create since this method will not error if you recreate the same folder.....because you cannot do that this way.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi ,

 

Sorry, i am missing some loops now. 

 

I have attached screen shots. 

 

Could you please check and tell me the flow.

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

At the end of your path you need to add a filename (maybe "del.me"). This will create a file inside the folder. At the moment it is assuming your folder is a file. Once the "del.me" file is there, you can delete the "del.me" file.

 

Not sure what you meant about loops?

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

I am using below flow.

 

tfiledelimited_1 => tUniqueRow_1 => tFlowToIterate_1 => tFileTouch_1

 

tFileTouch_1:

Properties:
File: "C:/Users/DELL PC/Desktop/reports/SOURCE FILE 3/" + ((String)globalMap.get("row2.newColumn2"))
   Checked: create direcotry if does't exist

 

I am able to create file with empty records. but folder are created.

 

Please let me know any changes required?

 

Thanks

Shree

 

Four Stars

Re: store the dynamic values in context variable

Hi,

 

I am using below flow.

 

tfiledelimited_1 => tUniqueRow_1 => tFlowToIterate_1 => tFileTouch_1

 

tFileTouch_1:

Properties:
File: "C:/Users/DELL PC/Desktop/reports/SOURCE FILE 3/" + ((String)globalMap.get("row2.newColumn2"))
   Checked: create direcotry if does't exist

 

I am able to create file with empty records. but even folder also not creating.

 

Please let me know any changes required?

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

You need to set up in the following way....

 

tFileTouch_1:

Properties:
File: "C:/Users/DELL PC/Desktop/reports/SOURCE FILE 3/" + ((String)globalMap.get("row2.newColumn2")) + "/DEL.ME"

 

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

I am getting below error.

 

[statistics] connecting to socket on port 3710
[statistics] connected
Exception in component tFileTouch_1
java.io.IOException: The system cannot find the path specified
at java.io.WinNTFileSystem.createFileExclusively(Native Method)
at java.io.File.createNewFile(Unknown Source)
at recon_test.mutiple_folder_creation_0_1.mutiple_folder_creation.tFileInputDelimited_1Process(mutiple_folder_creation.java:1535)
at recon_test.mutiple_folder_creation_0_1.mutiple_folder_creation.runJobInTOS(mutiple_folder_creation.java:1951)
at recon_test.mutiple_folder_creation_0_1.mutiple_folder_creation.main(mutiple_folder_creation.java:1795)
[statistics] disconnected
Job mutiple_folder_creation ended at 16:17 09/08/2017. [exit code=1]

 

Could you please check your local talend and share the job? would be very helpful.

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

I don't have a local version of your job. Like before, can you print out the path that is created by the Java you are using to create your touch file. 

 

I am confused as to why you are using a different piece of code for this. Previously you were using.....

((String)globalMap.get("row2.newColumn1"))

Now you are using....

"C:/Users/DELL PC/Desktop/reports/SOURCE FILE 3/" + ((String)globalMap.get("row2.newColumn2"))

First, why is row2.newColumn1 being used when you were creating the folder using a tSystem component? Also, why when you are using row2.newColumn2, are you prefixing it with "C:/Users/DELL PC/Desktop/reports/SOURCE FILE 3/"? 

 

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

Sorry for that, Even i am also missing points in between.

 

My requirement is below.

Final Requirement: 

Source records:

Columns are:

txn_date, num 

12-06-2017,12

13-06-2017,24

12-06-2017,22

14-06-2017,24

12-06-2017,13

13-06-2017,24

 

 

tOracleInput_1 ==> tFileOutputExcel_1

 

1)  Create unique txn_dt directory. Ex:  12-06-2017, 13-06-2017 etc

2) Inside that directory create xlsx file. date_wise_load.xlsx ( like 12-06-2017)

3) Load that date records. 

Output File:date_wise_load.xlsx

txn_date,num

12-06-2017,12

12-06-2017,24

12-06-2017,13

 

Could you please tell the flow.  There is no confusion.

 

Thanks

Shree

Ten Stars

Re: store the dynamic values in context variable

I think we are going around in circles a little. This was my first suggestion. I will explain.

 

1) Calculate your file path as a String (you were already doing this when you had the issue of trying to create the same path more than once).

2) Just create your xlsx file using a tFileOutputExcel component and the file path String from step 1.

 

That is it.

 

You do not need to care about creating the file path. Writing the file will take care of that.

You do not need to care about creating the file path more than once. That is not possible.

 

Simply query your data. Calculate your file path. Write to the Excel file.

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Hi,

 

Yes, Could you give me the job flow(components step by step).

 

Let me go through all before points discussed.

 

Thanks

Shree

 

Ten Stars

Re: store the dynamic values in context variable

OK, I have been through the complete thread again and I have some questions.

 

1) Is your data coming from Oracle? If so, why have you been reading from a file in some of the screenshots?

2) Are you expecting to create multiple Excel files per job or just one? For example, will the data being returned from your query require more than one output Excel file?

 

 

Rilhia Solutions
Four Stars

Re: store the dynamic values in context variable

Thanks once again.

 

Please find below my answers.

 

1) Is your data coming from Oracle? If so, why have you been reading from a file in some of the screenshots?

Ans: I tried both source as oracle and excel file. 

Reason: 1) I am trying create folder initial process only then i need source as excel file and no target i want store txn_dt  in context variable.(why means i will use this context variable create folder based on date anywhere in the job)

               2) After some sub jobs, i am going to use oracle as source and target is excel files. 

 

Note: You consider Oracle as source(Final point)

 

 2) Are you expecting to create multiple Excel files per job or just one? For example, will the data being returned from your query require more than one output Excel file?

Ans: Yes per job.

 

flow:  tOracleInput_1(Source) ==> tExcelOutputFile_1(Target)   --- (multiple folder creations inside records based on unique txn_dt)

 

Please let me know if need any information.

 

Thanks

Shree

 

 

Ten Stars

Re: store the dynamic values in context variable

OK. This is a bit more complex. I think this could be a good learning opportunity, so I will give you the components and the order.....but you will need to try it and debug it. This solution WILL work, but only once you have ironed out any bugs that are introduced while building it.

 

(1)tOracleInput -->(2)tJavaRow---> (3)tFlowToIterate ---iterate--> (4)tOracleInput -----> (5)tFileOutputExcel

 

1) Your first tOracleInput will return a distinct list of the data your files are grouped by. I believe that to be dates. Nothing else it required here.

2) Use the tJavaRow (or a tMap if your prefer) to create the file path from the group key. Output this to the tFlowToIterate.

3) Your tFlowToIterate will create a globalMap variable with that group key and the file path.

4) Connect your tFlowToIterate to another tOracleInput using an iterate link. This tOracleInput will return all of the data and be filtered by the group key derived in the first tOracleInput. You can use the globalMap variable in the WHERE clause of the tOracleInput. 

5) The tFileOutputExcel will need to use the globalMap value for the file path. 

 

The reason for doing it this way is that you will need to create a new file for each chunk of data requiring a new file. This cannot be done in one subjob. Therefore you need to iterate over that data. The tFlowToIterate will carry out the iteration and your second tOracleInput will supply the relevant data according to the key of the current iteration.

 

Rilhia Solutions