topic Import csv (in zip) file from FTP to Oracle DB in Talend Studio

Import csv (in zip) file from FTP to Oracle DB

Gourav_King_of_DataLand — Wed, 30 May 2018 14:32:58 GMT

Hi All,

I am quite new to Talendand need help in completing a simple task which I am not able to figure out myself.

Requirement: Three is a FTP location where 10 files are loaded everyday with the format file1_date, file2_date....etc. I want to get the latest version of each file from FTP and upload them into 10 different tables in Oracle DB. File1 goes to Table1, File2 goes to Table2.. and so on. So far this is what I am able to do

Get the latest file from FTP in a local directory using t_FTPGet. This copies latest version of all 10 files in a local directory. - this is ok

How do I then pick each file and then copy it in relevant tables in oracle db. I already have destination oracle DB connection established and retrieved schema/tables.

Re: Import csv (in zip) file from FTP to Oracle DB

TRF — Wed, 30 May 2018 15:31:47 GMT

You have made the harder, it remains the least funny.

You need a subjob per file to read the content and push to tOracleOutput to insert record into the corresponding table:

This suppose you don't need any transformation and the volume is not too high (so you don't need Oracle bulk component).

Re: Import csv (in zip) file from FTP to Oracle DB

Gourav_King_of_DataLand — Wed, 30 May 2018 15:45:51 GMT

Hi TRF,

Thanks for the reply, the process has to run everyday and the file name will change today file1_20180530, tomorrow file1_20180531 and so on.

I did something similar to what you suggested already

The thing that I am not able to figure out is how to get the file name dynamically in the "File name/Stream"

One way I thought of is to have some component before tFileInputDelimited that renames file to a fixed name always and once the data is successfully copied in database delete the file so that directory is ready to be used for next day. I am not able to figure out this either - how to change the file name in this flow.

Let me know your suggestions what can be the optimal way of doing this.

Re: Import csv (in zip) file from FTP to Oracle DB

TRF — Wed, 30 May 2018 16:06:13 GMT

1rst, if you get a zip file, you have to unzip it to get the text file.

2nd, you can use a tFileList to iterate over the file list and retrieve the current file name in a global variable such as "tFileList_1_CURRENT_FILE" or "tFileList_1_CURRENT_FILEPATH" (same with full pathname).

Re: Import csv (in zip) file from FTP to Oracle DB

Gourav_King_of_DataLand — Thu, 31 May 2018 08:55:28 GMT

Hi TRF,

Appreciate the help. I tried what you suggested and it worked, however need your help to figure out one thing

About the "1rst, if you get a zip file, you have to unzip it to get the text file" - there is an option in tFileInputDelimated on the basic setting at the end "Uncompress as zip file ". I checked that and it allowed me to upload data from zip files without needing them to unzip in advance.

The job looks like

Setting as following

tFTPGet_1 - FTP login details as required .. file Filemask as required

tFileList_1 -

This works

This Doesn't

How do I get the file name with path for the current file downloaded