topic Re: csv file read in Talend Studio

csv file read

Karuetl — Sat, 16 Nov 2024 06:38:00 GMT

I have a csv file as below example . How do i read line after Detailed Data and load into the table

Re: csv file read

akumar2301 — Fri, 08 Feb 2019 20:21:11 GMT

Put header as 15 in tfileinputdelimited, it will start reading from line 16

Re: csv file read

Anonymous — Fri, 08 Feb 2019 20:47:11 GMT

There are a couple of ways of doing this using a tFileInputDelimited. The first way is the easiest way, but requires that the number of rows before the header remains the same. In your example, the header row is row 15. So you would simply configure the tFileInputDelimited to have the header at row 15, and it would only select rows after the header. For example, your tFileInputDelimited might look something like this....

Alternatively (and probably more likely) you may want to do it more dynamically. You probably do not know how many rows will be used before the header of the set of data you want to import. If that is the case, a relatively easy way of solving this (for your example above) is to configure your tFileInputDelimited to have 5 columns which are all Strings. Then read from row 1 of the file. Now you know that the header row's column A will have "Email Address" in it. So what you do is set up a tMap to look for the first occurrence of this value and only return rows from when this happens. For example, I have knocked up a quick example of a tMap doing this.....

I use a tMap variable I call "GoodData". It is set to a Boolean and has the Nullable box ticked (it must be allowed to be null). This works because tMap variables store their values between rows. So what I am doing is using the below code in the tMap variable....

Var.GoodData==null || Var.GoodData==false ? row1.A.compareToIgnoreCase("Email Address")==0 : Var.GoodData

....to say, "If the value of GoodData is null OR is false, test the value of Column A for 'Email Address'. If it is that, set the value of GoodData to true. Otherwise set it to false". If GoodData is ever found to be true, it will subsequently always be true.

In the output table of the tMap I simply have an output filter that checks the following ....

Var.GoodData!=null && Var.GoodData

....which says "If GoodData is not null and true".

So only rows from "Email Address" row onwards are returned.

Of course, you may want to remove the Header Row as well and only return the data. This requires a small change, but makes use of a cool piece of functionality that most people don't consider. As I said, tMap variables keep their values between rows. They also are strictly processed from top to bottom. So due to that, you can act upon what happened in the previous data row. Take a look at the layout of my tMap now.....

I have a new tMap variable called "DataRow". The other tMap variable is exactly the same as the last time. However it is below the "DataRow" variable. This means the "DataRow" variable is checked first for every row. The code in the "DataRow" variable is below....

Var.GoodData!=null && Var.GoodData ?  true : false

It checks to see if the "GoodData" variable is not null and true. If so, it sets its value to true. Otherwise it is false. Now we know that as soon as column A holds "Email Address", the "GoodData" variable will be set to true and will remain true. But the first time "GoodData" is set to true, "DataRow" has already been set to false. So the output table (which is now filtered using "DataRow) will not release the header row, but it will release the next row and every row after that.

Have a play around with this as it is REALLY useful functionality to know about.

Re: csv file read

Anonymous — Fri, 08 Feb 2019 20:48:47 GMT

Sorry @uganesh, I didn't see your post before I started typing mine. Didn't mean to re-provide your response as part of my answer

Re: csv file read

akumar2301 — Fri, 08 Feb 2019 21:11:24 GMT

@rhall No worries.I could just admire the details you have put in your response.

Re: csv file read

Karuetl — Fri, 08 Feb 2019 21:21:18 GMT

I got below error

Execution failed : Job compile errors

Error Line: 1489
Detail Message: The operator != is undefined for the argument type(s) boolean, null
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

i gave as

Var.GoodData!=null && Var.GoodData

Re: csv file read

Karuetl — Fri, 08 Feb 2019 21:36:31 GMT

Its working fine but I dont want header to be loaded
Email Address;Date Completed;Assigned To;Subject;Email Status

Re: csv file read

Anonymous — Sat, 09 Feb 2019 12:56:36 GMT

Look at the example I gave at the end. That will remove the header row

Re: csv file read

Karuetl — Wed, 06 Mar 2019 16:06:09 GMT

When the file is .csv it was working fine but now received the file as *.csv.xlsx , it read the rows but did not load anything

Re: csv file read

Anonymous — Wed, 06 Mar 2019 16:15:42 GMT

There is a big difference between a CSV file and a XLSX file. A CSV file is a flat file. It can be read with any text editor. A XLSX file is an Excel file and must be read with Excel. I suspect this has happened because you have opened a CSV file with Excel and saved it as XLSX. The tFileInputDelimited component will only read flat files NOT XLSX files.

Re: csv file read

Karuetl — Wed, 06 Mar 2019 16:16:38 GMT

ok thanks