topic Re: Load Multiple CSV files with different Structure in Talend Studio

Load Multiple CSV files with different Structure

Anonymous — Sat, 16 Nov 2024 10:22:30 GMT

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.

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 22 Sep 2016 07:36:16 GMT

Hi,
Have you already checke talend dynamic schema feature which allows you to design schema with an unknown column structure (unknown name and number of columns).
Here is the article about:TalendHelpCenter:How to process changing data structure?
Best regards
Sabrina

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 22 Sep 2016 10:59:35 GMT

I am aware of it but this does not allow me to perform any data quality checks on the Data. This will be a simple load from file to the database or vice versa.
Thanks,
Sankalp

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 19 Jul 2018 16:58:40 GMT

Hello,

How can I import data from excel or csv file and dump data in two tables State Name, State Code, City Name, City Code in State_City table and Sr No, Name, Address, Department, Designation, Date of Joining in Emp table? Here is the attached file.

ExcelFile.xlsx

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 19 Jul 2018 18:46:30 GMT

Hi Dhara,

You will require two logical operations in this context.

After reading the file (while reading, specify limit value as 5 so that you will read only header part), you need to push the data to a tjavarow or tmap. Since State Name is in column A and value is in column B, you can do a comparison operation. ie, if input_row.columnA is equal to "State Name:" then output_row.statname=input_row.columnB. Similar operation can help you to parse other header details.

For reading the detailed content, read the data using texcelinput component again but this time specify the header as 5. In this way, you will be skipping the header components.

Hope this can resolve your issue. If it is working, please mark as accepted solution so that it will help Talend community.

Warm regards,

Nikhil Thampi

Re: Load Multiple CSV files with different Structure

Anonymous — Fri, 20 Jul 2018 09:01:55 GMT

How can I write if else condition in tJavaRow component?

if (input_row.A=='State Name:')
{
output_row.statename = input_row.B;
System.out.println(output_row.statename);
}
else if (input_row.A=='City Name:')
{
output_row.cityname = input_row.B;
System.out.println(output_row.cityname);
}

I wrote this code but getting error 'Invalid Character constant'.

See the screenshot

Re: Load Multiple CSV files with different Structure

Anonymous — Fri, 20 Jul 2018 09:14:50 GMT

Hi,

You have assigned the value as constant and that is the reason for error. You will have to try like below in tjavarow.

If you think the suggestion has worked, please mark the topic as acceptable solution.

Warm Regards,

Nikhil Thampi

Re: Load Multiple CSV files with different Structure

Anonymous — Fri, 20 Jul 2018 10:59:18 GMT

I am getting duplicate rows when I dump data into Oracle table and I am not getting state code value and city code value.

This is my code.

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.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;
}

Re: Load Multiple CSV files with different Structure

Anonymous — Sat, 21 Jul 2018 07:13:29 GMT

I am getting duplicate rows when I dump data into Oracle table and I am not getting state code value and city code value.

This is my code.

Can you help me to resolve this?

Output:

StateName CityName StateCode CityCode

Gujarat null 123 null

Gujarat abc 123 234

Thanks,

Re: Load Multiple CSV files with different Structure

Anonymous — Sat, 21 Jul 2018 20:00:44 GMT

Hi Dhara3010,

You will have to denormalize the dataset to get a single row of data. I have used the same excel sheet provided by you in your previous chat and I got the result set in single line. I have attached the sample job for your reference as zip file. Please make necessary changes according to your need.

And the result I got is as shown below (you may have to reformat the date column according to your choice).

The code used in tjavarow is as shown below.

//Code generated according to input schema and output schema
output_row.merge_data="Yes";
output_row.statename = "";
output_row.cityname = "";
output_row.statecode = "";
output_row.citycode = "";
output_row.fromdate = "";
output_row.todate = "";

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;
}

Warm Regards,

Nikhil Thampi

test2.zip

Re: Load Multiple CSV files with different Structure

Anonymous — Mon, 23 Jul 2018 07:48:21 GMT

Hi Nikhil Thampi,

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

Thanks,

Dhara

Re: Load Multiple CSV files with different Structure

Anonymous — Mon, 23 Jul 2018 08:06:48 GMT

Hi,

Happy to help 🙂

Warm Regards,

Nikhil Thampi

Re: Load Multiple CSV files with different Structure

Anonymous — Mon, 23 Jul 2018 13:07:03 GMT

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

Re: Load Multiple CSV files with different Structure

Anonymous — Mon, 23 Jul 2018 13:18:49 GMT

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

Re: Load Multiple CSV files with different Structure

Anonymous — Wed, 29 Aug 2018 14:45:55 GMT

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

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 30 Aug 2018 06:37:59 GMT

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;
}

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 30 Aug 2018 13:58:20 GMT

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

Re: Load Multiple CSV files with different Structure

Anonymous — Thu, 30 Aug 2018 19:33:11 GMT

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

Re: Load Multiple CSV files with different Structure

Anonymous — Sat, 01 Sep 2018 06:47:43 GMT

Thanks Nikhil Thampi for the solution

Re: Load Multiple CSV files with different Structure

Anonymous — Sun, 02 Sep 2018 15:03:41 GMT

Hi Nikhil,

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