Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a csv file as below example . How do i read line after Detailed Data and load into the table
Accepted Solutions
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.
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.
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
