Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: How to loop CSV file data columns

  1. #1

    Default How to loop CSV file data columns

    I am new in Pentaho Data Integration. I need help/guide on how to loop the csv file column.


    I need to insert data from CSV file in to a table having 3 fields (Customer Account, Date time, value)


    where D1, D2, D3...D6 contains 15 minute interval data for a day ( so there are 96 column values )

    CSV Data :
    Customer Account, Date, D1, D2, D3, D4, D5,D6,..D96
    123456, 11/28/2017, 1.25,1.26,1.27,12.8,...23.6
    234567, 11/29/2017, 2.35,3.45,4.56,6.79,...22.6


    I need to insert data into table like below


    CustomerAccount,Date,Value
    11-28-2017 00:00, D1 value
    11-28-2017 00:15 , D2 value
    11-28-2017 00:30 , D3 value
    11-28-2017 00:45, D4 value
    11-28-2017 01:00 , D5 value
    ....
    ...
    ....
    ....
    11-28-2017 23:45, D96 value
    11-29-2018 00:00,D1 value
    ....




    How can I achieve in pentaho. Please provide inputs

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    So you want to normalize the data?

    Have you tried the "Normalize" step?

  3. #3

    Default

    Thank You for prompt response and much appreciated. I didn’t use the normalize step.I’ll try normalize step

  4. #4

    Default

    Tried with row normalizer and getting 96 column values to corressponding date but not able to add 15 min interval:

    Current Result :



    CustomerAccount,Date,Value
    11-28-2017 , D1 value
    11-28-2017 , D2 value
    11-28-2017 , D3 value
    11-28-2017 , D4 value
    11-28-2017 , D5 value

    Expected Output :

    CustomerAccount,Date,Value
    11-28-2017 00:00, D1 value
    11-28-2017 00:15 , D2 value
    11-28-2017 00:30 , D3 value
    11-28-2017 00:45, D4 value
    11-28-2017 01:00 , D5 value

    Could you please provide input how to add 00:00, 00:15, 00:30, 15 minute interval.

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    So you tried Row-Normalizer.
    What did you do with the "Type field"?

    Name:  230669.png
Views: 147
Size:  25.5 KB
    So long, and thanks for all the fish.

  6. #6

    Default

    Hi Marabu,

    Thank you. In the type field I have mentioned 1,2,...96 and then used the java script and writtent 96 if condition if type = 1 then date = date.concat(" 00:00)... did for 96. But I felt it is not a good approach as JS decrease performance.

    after looking into the above Type field provided by you looks perfect to me and saved my time. Now only problem is how can I concate date and time (type) (space between data and time) to get below value

    11/28/2017 00:00:00, 11/28/2017 00:15:00....., etc.,

  7. #7

    Default

    Hi Marabu,

    I have used the calculator step and used the Add time B to date A.

    Is it right?

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Right it is

  9. #9

    Default

    Thank You

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.