I am trying to transform a XLS to another CSV, from Columns to Rows. The source XLS has this structure.
Year1, Item1, Item2, Item3, Item4
Year2, Item1, Item2, Item3, Item4
etc..
I would like to transform the file into a normalized structure in this manner
Year1, Item1
Year1, Item2
Year1, Item3
Year1, Item4
etc..
I am using SpreadsheetReader -> Normalizer ->UniversalDataWriter
3 Questions
1) There are some Items with Null value. Is there a component to detect and fill null values with '0'?
2) I had looked at http://doc.cloveretl.com/documentation/ ... lizer.html but I am not sure how to use this example.
3) I would like the output to be CSV, so I chose the UDW component. The default delimiter seems to be '|'. Can this be changed?
Year1, Item1, Item2, Item3, Item4
Year2, Item1, Item2, Item3, Item4
etc..
I would like to transform the file into a normalized structure in this manner
Year1, Item1
Year1, Item2
Year1, Item3
Year1, Item4
etc..
I am using SpreadsheetReader -> Normalizer ->UniversalDataWriter
3 Questions
1) There are some Items with Null value. Is there a component to detect and fill null values with '0'?
2) I had looked at http://doc.cloveretl.com/documentation/ ... lizer.html but I am not sure how to use this example.
3) I would like the output to be CSV, so I chose the UDW component. The default delimiter seems to be '|'. Can this be changed?
-
Some updates. I have solved 1) and 3) using the Reformat Component.
For 1), it is solved by using a if/else to detect "0" and "-" (it is not null, otherwise i will use isnull()) and changing the value.
For 3), it is to edit the metadata and changing the delimitator type from | to ,
Still working on 2) -
Hi Trainman
I have attached an example graph, which loads some example data based on your inquiry. It contains the Normalizer component, which is set the way you want. The CTL code it contains is commented, for you to be able to easily understand what it does. Part of this code also replaces null values with 0 since there is no specialized component for this. Regarding the delimiter question: yes it can be easily changed via the metadata settings as depicted bellow.
http://s23.postimg.org/akbh3j5zb/delimiter_setting.jpg
Hope this helps.
Best regards -
Hi Cholasta,
I am unable to open your sample file. CloverETL says "Cannot open graph source editor" and details indicates that the Editor cannot initiate due to a null pointer exception in java. Had this issue before with other example files posted here in the forum.
My enviroment is a bog standard Windows 10 box.
cheers. -
Hi Trainman
I have couple of questions regarding your issue with opening the graph.
- 1. What version of the Designer are you using?
2. Do the graphs you have created yourself open normally?
3. Please provide me with the error log of the Designer, which is located in <your_workspace>/.metadata/.log
4. How did you import the file into the Designer (copy - paste method or standard import function of the Designer) ?
Thank you.
Best regards -
Hi Cholasta,
1) Designer 4.1, Build 018
2) Yes, graphs which I did on my own works fine.
3) See attached. (
4) I used File-Open to load the grf file into my current working project.
I have also attached a screenshot of my Designer setup running my own graph as well as the error message. -
Hi Trainman
You need to have the file imported in the project, opening it while it's not added to project will result into an error. However I admit the error message is not correct or understandable. I have created an issue in our bug tracker, for our developers to know about it. So please use some method to import the graph in order to be able to view it. You can right-click on the graph folder in the Navigator window and select import, or you can simply copy-paste the graph in.
Hope this helps.
Best regards -
Hi,
I am having a similar challenge. I'm looking to collapse data in various columns into a single column and multiple rows. I think the normalizer can do this? Do you have an example graph for how to do this?
Incoming record (1 row): First Name, Last Name, State, Field1, Field2, Field3, Field4
outgoing record (4 rows) where the first three fields repeat and then fields 1-4 get put on separate rows:
First Name, Last Name, State, Field1
First Name, Last Name, State, Field2
First Name, Last Name, State, Field3
First Name, Last Name, State, Field4
Can the normalizer handle this? Does anyone have any example code?
thanks! -
Hi Pintail,
yes, Normalizer is the component you are looking for. Below, you can review an example code (in Normalizer) that is based on your sample data://#CTL2
string[] fields;
function integer count() {
fields = [$in.0.Field1,$in.0.Field2,$in.0.Field3,$in.0.Field4];
return length(fields);
}
function integer transform(integer idx) {
$out.0.FirstName = $in.0.FirstName;
$out.0.LastName = $in.0.LastName;
$out.0.State = $in.0.State;
$out.0.Field = fields[idx];
return OK;
}
In the count function, I demonstrated a generic approach where you would simply put multiple fields into a single list field and return its index count to the transform function. If you knew that the number of fields being transformed to rows is always the same, you could return a hardcoded number by the count() function (for example: return 4;)
Consequently, the transform function runs 4 times and each time it writes a different field into the $out.0.Field based on the index number.
Best,
Please sign in to leave a comment.
Comments 9