Load Multiple CSV files with different Structure

[Anonymous]

Hi all,
We have a project requirement where we are required to load 100+ CSV files into the oracle database. All 100 CSV files have different structure. Before loading the files into the database we need to perform basic validations like, 
- for Date column check if date is in correct format
- trim the white spaces for strings
- check if numeric data types columns actually consists of numbers
Now I have 2 queries:
- I am thinking of storing the name and schema of the CSV files in database. Read that meta data information of the file and then depending upon the meta data, load the data into the oracle tables. This way tomorrow is 100+ new CSV files are added for loading, I do not have to create a new job everytime.
Please suggest how it can be achieved. 

[Anonymous]

Hi Nikhil,

My requirement is to dump data from excel file to two table(the file which I sent you before again attaching the same file). The upper part

State Name:	Gujarat	State Code:	123	From Date:	7/19/2018
City Name:	hbhjbjh	City Code:	567	To Date:	20-07-2018

in one table 

Sr No

Name

Address

Department

Designation

Date of Joining

and this in other table. When my Job is executed the file should be moved to InProcess folder dump data in both table and if successful then move file in Process folder else in Error folder.
I even need to handle the datatype as in excel it is by default string. I even have an amount column. Even I need to handle all of the error conditions which may occur like out of 5 only 2 inserted or if the data is inserted in one table and not in other table. I do need to check all the validations part even for date column, Amount only number can be allowed otherwise move file in error folder. Even I need to maintain the log in third table like filename, status(processed or error) and reason for error like cause for error.

Can you suggest me the solution for this? as I really struggling for this task from 2 weeks maybe. Actually I am struggling with the error part to handle.

ExcelFile.xlsx

[Anonymous]

Hi Dhara,

     Apologies for the delay as I am currently traveling and didnt get chance to look my community messages. The first step we have to do is to parse the file and split the records (which I have already specified in my previous post). You can use a tjavarow to make this happen.

      You should do all the verification for null check in second tjavarow or tmap (after denomalization component) so that it will not become one big java custom code. You can use standard java if else conditions and data type check in this second tjavarow/tmap. Based on the condition validation, you can use run if to move the data to your target/reject area.

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

Can you give me solution for this date error?

[Anonymous]

Hi dhara,

      You will have to change/parse the date value from String to date using Talend/Java date conversion functions. I would suggest you to take Talend DI Basics and Advanced Training program as it will help you to overcome these hurdles.

Warm Regards,

Nikhil Thampi

[Anonymous]

Thanks I solved my problem of date parse

[Anonymous]

Hi,

How to get rejected records count of tconverttype?

For eg: There is reject count for FilterRow component "((Integer)globalMap.get("tFilterRow_1_NB_LINE_REJECT")).equals(0)".

Is there any such code for tconverttype reject rows count?

Thanks,

Dhara

[Anonymous]

Hi Dhara,

       Please start a new thread for each issue as it will help in understanding and replying to that specific context. Else the current thread will be so huge that it will become difficult to manage.

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

File format error is not handled by the solution you gave.

I tried this as shown in screenshot, but didn't work well

Can you suggest some other way to handle this file and print message "Invalid File Format"?

Thanks,

Dhara

[Anonymous]

Hello,

tConvertType provides a variable like this: ((Integer)globalMap.get("tConvertType_1_NB_LINE")).

It should be the number of input rows.

Best regards

Sabrina