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

Highlighted
Six Stars

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.

 

 

 


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

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

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


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

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

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: 

Capture.png

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


TRF
Six Stars

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

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 

 

Capture3.JPG

 

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

 

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

 

 

 

 

 

Fifteen Stars TRF
Fifteen Stars

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

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


TRF
Six Stars

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

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 

 

Capture5.JPG

Setting as following

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

 

tFileList_1 -

 

This works Capture7.JPG

 

This Doesn't

Capture6.JPG

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

 

 

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download