Iterate and load multiple files into database using tFlelist and tMysqlout

Talend Training Course details

Below tutorial will explain you the  steps to load files into database 

Step 1: Open Talend

·         Create new project or open already existing project

Step2: create sample csv files  

·         File1,File2,File3,File4

·         They are same format or schema

·         Create one new folder name as’ fileprocessfolder’ and  put all files in it. I can access the files from this folder.

Step3: Create new a new job in Talend

·         Right click on the job designs in the repository window and select the ‘create job ‘option.

·         Name of the job is’ filelist_fullload’.

·         Click on finish button

Step4:  create metadata for one sample file

·         Go to repository window, click on arrow next to ‘metadata’ and right click on File delimited select the ‘create file delimited’.

·         Enter example name like’file_process’ click on next button.

·         Add metadata file to repository

·         Click on the browse button, select the sample file’file1’, Click on next button

·         In this screen  select field separator  field   click on corresponding combo box  select  ‘comma’ option instead of semicolon

and select check box for set heading row as column, click on refresh for preview, click on next button.

·         In this screen click on the finish button, automatically window will be close.

Step5:  creation of database connection using Mysql database

·         If we have   already database connections  for loading data into target table  no need to create   one more database

Connection, just use existed database connection and by the given credentials.

·         If we don’t have data base connection, just follow the below steps to create new database connection.

·         Step-1

·         Go to Mysql database --->enter valid password ---> after Mysql prompt open create database using commands like

·          Mysql>  create database targetdb; here targetdb is new database name

·         After creation of new database we can grant all permissions to that data base.

·         Using this command we can grant   all permissions to the DB.

·         Mysql> grant all on database name.* to username@’%’ identified by ‘password’.

·         Step- 2

·          Go to Talend repository window --->click on arrow next to ‘metadata’ --->right click on DB Connections-->select

Create connection option.

·         After click on that new database connection window opened ,in that step one we can give the name of the database click on next button.

·         In the second step we can select desire database type and version of the database ,fill the  all options with valid credentials

After check that for DB connection success or failure, click on check button. If creation is successful just click finish button.

·         Here I use this database only to load the result data. Like ‘Target database’. 

Step6: design sample job

·         In step3 already I created one job with ‘filelist_fullload’.

·         Go to repository window --->click on arrow next to ‘job design’--->right click on’ filelist_fullload’ job  select  ‘edit job’ option.

·         in step4  I already created one sample file for metadata ,we can use this like input file  and also it  can process the same  format or schema  files  using  tFlelist component.

·         Go to metadata---> click on the file delimited   select   which file  we can use  as a input file  ,drag and drop  it on  job design console

·         Click on ok button.

·         Go to right side panel  palette  ---> in that  search mode option  just type  tMap  and press enter key , we can get tMap component, drag and drop it on job design window.

·         Right click on the  ‘file process’ component ,select  row----->main connect a row to the tMap component

·         Again go to right side panel palette---> in that search mode option type  tMysqloutput ,press enter key, we can get  that component, select that component drag and drop it on job design window.

·         Now we can arrange the all components in proper order, why because we design  somewhat easily  and better way  to give  the connections etc.,

·         Go to tMap component right click  on that ,select  row--> new output and  connect to tMysqloutput component on that time  that will display one window  for new output name  ,we can give one name relatively to output file  it don’t have no spaces. After that click on ok button.

Step7:  component settings for sample job

·         For easily understanding purpose I run this sample job, mainly we can identify difference between the normal job and iterate to load multiple files in single job.

·          First  I can set the three  component properties one by one

·         double click on ‘File process’  component  we can get  basic-settings in the bottom of the job design window

·         Here we don’t need to change settings, why because already we gave at the time of creating metadata.

·         Now I go to tMap settings, double click on that we can get a new window.

·         I can select all the columns from the row1 (here row1haveing input file or source file) ,drag and drop it over on ‘loadallfiles’ (here it is output ).

·         Here it is optional select columns based on our requirement we can select columns.

·         And also here we have more options ,to change the data types ,length  size of the columns, if  add more columns, or remove existed columns  on both sides  (input, output), etc.,

·         Click on ok button close the tamp settings window.

·         tMysqloutput settings

·         Double click on the tMysqloutput component we can get basic settings in built-in mode.

·         Now we change the property type into repository, automatically   all options filled with valid credentials except table name, here i can enter manually, like”fileprocess”.

·         And also I change the option action on table “create table if not exist”. It creates the table in target database if it doesn’t have previously.

Step8: Run the sample job

·         click on run button or press F6 button from keyboard, it can run the job automatically , it can display the  job execution starting  time and ending  time ,status of the job.

step9:  design job using tFlelist component

·         Here I can continue with  previous job ‘filelist_fullload’

·         Go to palette--> type manually tFlelist in search box ---> select that component drag and drop it over on left side  above  corner of job design window .

·         Right click on tFilelist select row--->iterate, connect that row to input metadata file component “file_process”.

·         tFilelist settings

·         Double click on tFilelist component, we can get basic settings under the job design window screen.

·         In basic settings i can change directory, like this "D:/5.3output/fileprocessfolder.csv", input processing files are located in this directory.

·         Metadata input file component settings, here I used ‘file_process’ for that.

·         Double click on that component, change the property type repository ---->>built in, and change filename/stream like this ((String) globalMap.get ("tFileList_1_CURRENT_FILEPATH").

·         Tamp settings

·         No need to any changes old settings

·         tMysqloutput component settings.

·         Double click on component --->go to basic settings ---> change table name, why because that name I already used in previous job and move to --->action on table select one action based on our requirement.

·         Run the job

·         Click on run button or press F6 from keyboard.

·         Job executed successfully.

Load OracleDatabase Table data From Filedata

 Load Database table  from CSV files that contains the following fields:

•          Employeid 
•          Employename
•        Dateofjoin
•         Salary

                                  

The file would looks like this:

 

We want to load this file into a OracleDatabaseTable  with a schema as follows:

 Here are the steps we will take to build our Talend Open Studio solution:

Step 1:  Open Talend

Open Talend and create or open an existing project

Step 2: Create a new job

Right click on Job Designs in the Repository window and select “Create job”

Name the job “item_load”

    Enter the name of JOb

Step 3: Create a File Delimited repository element

Now we need to create a repository item for our  Oracle databaseTable. To do this click the arrow next to “Metadata”  in the Repository window and right click on “File delimited” and select “Create file delimited”

Enter a name for your example file schema and click next:

.

 

Select your example file that we saw earlier (or use your own) by clicking the “Browse” button.

Click Next

 

 

On the next screen select field separator as comma (as per your file data) and select the checkbox for “Set heading row as column names”, then click “Refresh Preview”. Click Next.

Talend has now generated an estimated schema, review this schema and make any changes as you would like, then click Finish.

Step 5:  Design your job

In this step we are going to design our job to connect our CSV file to our OracleDatabaseTable.

Open the job we created in Step 2 by double clicking the name of the job under Job Designs in the Repository window.

In the palette window on the right hand side type “tFileinputDelimited” into the search box, then drag and drop the component “tFileInputDelimited” into the job window in the center of the screen

  Select the tFileInputDelimited Component in the job window and then select the “Component” tab near the bottom middle of the screen.

Click the drop down box next to “Property type” and select “Repository”

Then click the button with three dots that appears and select the delimited file from the repository that we created in Step 3.

Select a tMap component from the palette on the right hand side of the screen and drag it into the job design window

 Right click on the tFileinputDelimited component, select Row->Main and connect a row to the tMap component.

 

 

Select a tOracleoutput  component from the Palette and drag it over to the job design window.

                                         

Click on the tOracleoutput  component to select it, then navigate to the Component tab in the lower middle of the screen. Fill all blocks

Connection Type:

DB Version:             

Host:

Port:

Database:

Oracle schema:

Username:

Password:

Action on Table: 

Next right click the tMap component, Select “Row” then “New Output”.

Connect this new output to the tOracleoutput component

Name the output row- “output1″

Now your tMap is connected to the tOracleoutput component, double click the tMap component to open up the tMap editor.

Click and drag the data fields from the “row1″ panel to the left of the screen to the corresponding “output1″ data fields. This tMap editor enables you to map to fields from the CSV input to the correct output fields in tOracleoutput data fields.

Click “Apply” then “OK” to save the changes and return to the Job Design window

Now your job design is complete and we just need to run the job to load the file into OracleDatabaseTable.

  

Step 6: Run your Job

In the group of tabs in the lower middle of the screen, select the “Run” tab

 

Under Execution, click the “Run” button

Your job will load and then run and you will see how many rows were processed in the job design window

Congratulations! You have now loaded your one file into your OracledatabaseTable.