# Normalizer Cols to Rows **URL:** \ **Category:** CloverDX Platform\ **Created:** [September 29, 2015, 12:00am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676 "2015-09-29T00:00:00Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![trainman50](https://avatars.discourse-cdn.com/v4/letter/t/b3f665/32.png) [@trainman50](https://forum.cloverdx.com/u/trainman50)\ **Post date:** [September 29, 2015, 12:00am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/1 "2015-09-29T00:00:00Z")
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](http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/normalizer.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? ---
**Author:** ![trainman50](https://avatars.discourse-cdn.com/v4/letter/t/b3f665/32.png) [@trainman50](https://forum.cloverdx.com/u/trainman50)\ **Post date:** [September 30, 2015, 10:21am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/2 "2015-09-30T10:21:24Z")
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) ---
**Author:** ![Lukas\_Cholasta](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/lukas_cholasta/32/166_2.png) [@Lukas\_Cholasta](https://forum.cloverdx.com/u/Lukas_Cholasta)\ **Post date:** [September 30, 2015, 12:47pm UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/3 "2015-09-30T12:47:16Z")
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](http://postimg.org/image/akbh3j5zb/) Hope this helps. Best regards - [normalizer\_example.rar](https://cdn.cloverdx.com/forum/18108%5F94eeb8c3ede61bb17742e6784335572d/normalizer%5Fexample.rar) ---
**Author:** ![trainman50](https://avatars.discourse-cdn.com/v4/letter/t/b3f665/32.png) [@trainman50](https://forum.cloverdx.com/u/trainman50)\ **Post date:** [October 1, 2015, 2:24am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/4 "2015-10-01T02:24:46Z")
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. ---
**Author:** ![Lukas\_Cholasta](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/lukas_cholasta/32/166_2.png) [@Lukas\_Cholasta](https://forum.cloverdx.com/u/Lukas_Cholasta)\ **Post date:** [October 1, 2015, 9:39am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/5 "2015-10-01T09:39:49Z")
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 \/.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 ---
**Author:** ![trainman50](https://avatars.discourse-cdn.com/v4/letter/t/b3f665/32.png) [@trainman50](https://forum.cloverdx.com/u/trainman50)\ **Post date:** [October 2, 2015, 5:51am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/6 "2015-10-02T05:51:38Z")
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. - [errlog.log](https://cdn.cloverdx.com/forum/18128%5F9c0abcaa5238ca0a058db6b1fd0a9203/errlog.log) - [cloveretl\_normal.jpg](https://cdn.cloverdx.com/forum/18128%5Ff16c8c7aefad26414e4c5acc631cb62e/cloveretl%5Fnormal.jpg) ---
**Author:** ![Lukas\_Cholasta](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/lukas_cholasta/32/166_2.png) [@Lukas\_Cholasta](https://forum.cloverdx.com/u/Lukas_Cholasta)\ **Post date:** [October 2, 2015, 9:25am UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/7 "2015-10-02T09:25:15Z")
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 ---
**Author:** ![pintail](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@pintail](https://forum.cloverdx.com/u/pintail)\ **Post date:** [October 19, 2017, 10:05pm UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/8 "2017-10-19T22:05:40Z")
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! ---
**Author:** ![Vladimir\_Barton](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/vladimir_barton/32/167_2.png) [@Vladimir\_Barton](https://forum.cloverdx.com/u/Vladimir_Barton)\ **Post date:** [October 20, 2017, 12:46pm UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/9 "2017-10-20T12:46:51Z")
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: ```auto //#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, ---
**Author:** ![pintail](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@pintail](https://forum.cloverdx.com/u/pintail)\ **Post date:** [October 20, 2017, 3:58pm UTC](https://forum.cloverdx.com/t/normalizer-cols-to-rows/1676/10 "2017-10-20T15:58:00Z")
Thanks! That worked prefect for what I need and doubt I would have figured out the right code on my own…thanks for the help.