Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Gourav_King_of_DataLand
Contributor II
Contributor II

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

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

 

  1. 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.

 

 

 

Labels (2)
1 Solution

Accepted Solutions
TRF
Champion II
Champion II

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).

View solution in original post

4 Replies
TRF
Champion II
Champion II

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: 

0683p000009LykY.png

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

Gourav_King_of_DataLand
Contributor II
Contributor II
Author

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 

 

0683p000009LyjH.jpg

 

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

 

0683p000009Lykd.jpg

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.

 

 

 

 

 

TRF
Champion II
Champion II

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).

Gourav_King_of_DataLand
Contributor II
Contributor II
Author

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 

 

0683p000009Lylb.jpg

Setting as following

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

 

tFileList_1 -

 

This works 0683p000009LxMh.jpg

 

This Doesn't

0683p000009Lylg.jpg

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