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 Thampi,

Thanks a lot for this solution. It really solved my problem. It is the best solution.

Thanks,

Dhara

[Anonymous]

Hi,

     Happy to help 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

    What if State Name is State Name1(Column name mismatch) in file or there is no data in file or value is not found or format is mismatch or full file is blank or record not found or improper file. How can I handle such file and data? i.e. Error handling

Thanks,

Dhara

[Anonymous]

Hi,

     You will have to invoke the subsequent error flows based on the different business rules you are planning to create.

For example, you can check the number of records from incoming excel data file by checking the value of pseudo variable ((Integer)globalMap.get("tFileInputExcel_1_NB_LINE")). If the value of this variable is zero, then you can go for the error flow. Similarly you will have to define each error flow in your mind and catch the errors using Talend error handling components.

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi,

My file template is given below

Now, If I get some other file format like this below

then what condition should I check and which palette should I use to move this file in Error Folder.

And if my file is half run i.e from 5 rows, only 2 are inserted then rows should be rollback and file should be moved to Error Folder. I am dumping data from excel file to sql server.

Can anyone suggest me the solution as soon as possible?

Thanks,

Dhara

[Anonymous]

Hi,

What condition should I mention to move file to error folder if I don't get following all columns mentioned in the condition,

if (!Relational.ISNULL(input_row.A) && input_row.A.equals("State Name:"))
{
output_row.statename = input_row.B;
}
else if (!Relational.ISNULL(input_row.A) && input_row.A.equals("City Name:"))
{
output_row.cityname = input_row.B;
}
else if (!Relational.ISNULL(input_row.A) && input_row.A.equals("From Date:"))
{
output_row.fromdate = input_row.B;
}

if (!Relational.ISNULL(input_row.C) && input_row.C.equals("State Code:"))
{
output_row.statecode = input_row.D;
}
else if (!Relational.ISNULL(input_row.C) && input_row.C.equals("City Code:"))
{
output_row.citycode = input_row.D;
}
else if (!Relational.ISNULL(input_row.C) && input_row.C.equals("To Date:"))
{
output_row.todate = input_row.D;
}

[Anonymous]

Can anyone help me for this? I need the solution by today as I need to complete the requirement.

[Anonymous]

Hi Dhara,

   You can add a flag column to verify whether the file is good or bad.

context.file_flag="Y" (at starting point for each file read in a tjava)

if (!Relational.ISNULL(input_row.A) && input_row.A.equals("State Name:"))
{
output_row.statename = input_row.B;
}
else if (!Relational.ISNULL(input_row.A) && input_row.A.equals("City Name:"))
{
output_row.cityname = input_row.B;
}
else if (!Relational.ISNULL(input_row.A) && input_row.A.equals("From Date:"))
{
output_row.fromdate = input_row.B;
}

if ( Relational.ISNULL(output_row.statename ) && Relational.ISNULL(output_row.statename ) ...... add other columns according to your need )
{
context.file_flag="N";
}

Add a run if condition and verify whether the file_flag value is Y or N. Based on that value, you can either mark it as good file or bad file.

Warm Regards,

Nikhil Thampi

[Anonymous]

Thanks Nikhil Thampi for the solution

[Anonymous]

Hi Nikhil,

I am not able to dump this data  in my table. I am getting error as shown below