Strategy to load a set of files in Talend

Question

I want to know which is best strategy to aboard the following problem in Talend:

• I need to load data from a set of delimited files that are stored in a directory with names like (SAMPLE1.DAT, SAMPLE2.DAT, ... , SAMPLEX.DAT)
• The target will be a table in a MySQL database
• I have to load all data at once because after this task I need to work with all records in the same table

I'm a bit confused because I don't know if it possible in Talend. I was seeing the tFileInputDelimited component but I didn't find the way to solve it.

Thanks

Answer 1

To read several files from one directory, you would use the tFileList component. It allows you to specify a directory and a file name pattern. All files in the directory matching the pattern will be processed, one after the other.

You need to use an "Iterate" link from the tFileList component to those components that describe what you want to do with each file. In your case, you would start with a tFileInputDelimited component (read the file) and connect the main output of that to a tMysqlOutput component. The MySQL component will, by default, just append the data to an existing table, so that should get you the result you want.

In the tFileInputDelimited component, you would not use a fixed filename, but a variable filename which is set by the tFileList component for each iteration (your loop variable, so to speak of). The name of that loop variable can be seen in the "outline" view in the studio, usually in the bottom left corner.

Answer 2

You would use components tFileInputDelimited into tMap (optional) into tmysqlOutput

Step 1 : configure some components like this, except you will use the delimited file input:

Step 2 : configure the component settings for the delimited file, click the disk for the wizard :

Step 3 : configure your database by right clicking on Db Connection under metadata, then following wizard:

Step 4 : Right click on each component and choose Row > Main > drag to next step in flow.

Step 5 : Open your tMap and map the columns from the file schema to the database schema.

Step 6 : Run the job, it should work if you have followed all the wizard, if there are errors just hover over the red component and it usually describes errors pretty well. You will see as the job runs how many records it has transferred.

Step 7 : after you have made it that far, create a tfiledelimited output with the same schema as the input, right click on the input choose Row > Rejects and drag that to the new delimited output, this is where and records that are rejected by the tmap will be sent.