Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: Inserting new Column with present Date

  1. #1
    Join Date
    Jan 2013
    Posts
    108

    Default Inserting new Column with present Date

    hi all

    I have some datas as text-files. i need to add a c new column with the current date-time to these datas in text-files, but not changing the name of files.


    thanks for your helps in advance

  2. #2
    Join Date
    Feb 2013
    Posts
    20

    Default

    1. Create a Job with a Variable called CurrentDatetime
    3. Create a transformation with Get SystemInfo and set the Variable CurrentDatetime in the Parent Job

    Now that you've got the system date as a variable, you can use Get Variable to load CurrentDatetime into a field and then output in the file. On the Text File Input, on the additional fields tab you can grab the Short Filename and then use that to create the output location in the Text File Output step

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

    Default

    The correct way to do this is in two sets of actions:

    1) Add the data:
    1.1) Read the File (Text File Input)
    1.2) Add Column C (Get System Info / system date (fixed))
    1.3) Write Temp File

    2) Rename Temp File

    Doing it in this pattern will allow you to deal with the scenario that someone else changes the schema of the source file (if you write back into it, your original file is GONE)

    So you'll need at least 1 job & 1 transformation. Depending on how dynamic you want this solution to be, you may need more transformations, or even more jobs.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  4. #4
    Join Date
    Jan 2013
    Posts
    108

    Default

    dear specialists,

    i got little confused

    1. step Get System Info is transformation, so i may not need a job, or?
    2. i added a new field DATE(manually written at the end of all fields) with a system date(variable or fixed) value from Get System Info in format like yyyymmddhhmmss.
    as a result i do have a new file with a new field DATE of system date but in format like:Fri Jun 28 11:13:54 CEST 2013

    and the best for me would be when the filename is not changed. in step Textfile Output i have to give a filename..


    thanks a lot in advance

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

    Default

    I don't see what's gained if a timestamp column is added to a file.

    The attached sample follows the procedure gutlez outlined.
    The named parameter INFILE is used to specify the filename.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Jan 2013
    Posts
    108

    Default

    thank you all so much, i now see the light.

    I need the system date to look like yyyymmddhhmmss - for ex. 20080115034550.
    But here it shows like a String Fri Jun 28 11:13:54 CEST 2013 mixed with words and numbers, and i don't need a weekday at all.

  7. #7
    Join Date
    Jan 2013
    Posts
    108

    Default

    oh no, i can't keep here the old filename unchanged. because, i am using it with a wildcard, i dunno the exact name..

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

    Default

    You can learn the exact filename from a job entry like "Add filenames to result".
    So long, and thanks for all the fish.

  9. #9
    Join Date
    Jan 2013
    Posts
    108

    Default

    i put this "Add filenames .." between Transformation and "Move Files" in Job, but.. i am retarding

  10. #10
    Join Date
    Jan 2013
    Posts
    108

    Default

    Hi marabu

    I found a step "Get files from result". i thought it would be the best solution for keeping the old filename unchanged while adding a new column with current date-time. i put all in transformation as follows: 1-Input txt-file, 2-Get files from result, 3- get system info, 4-text file output.
    But i am surprised getting absolute nothing after the 2nd step. it gives out an empty file.. where have i made wrong?


    thanks a lot in advance

  11. #11
    Join Date
    Jan 2013
    Posts
    108

    Default

    Or when i put "Get File Name" Step before "Text file Output", it gives an error, that it can't find the column coming from "Text file Input" step..

  12. #12
    Join Date
    Jan 2013
    Posts
    108

    Default

    and if i add columns with a new filename in TextFileOutput Step, some of columns are with a tab(space) in a new file. why is that?? how to avoid it?

  13. #13
    Join Date
    Jan 2013
    Posts
    108

    Default

    it inserts no tabs(spaces) to the columns of the new file, only if i check "Fast data dump(no formatting)" in Text file Output. But then it shows the newly inserted datestamp column not in yyyyMMddhhmmss, it inserts date like "Tue Jul 02 15:25:07 CEST 2013"... Plllsss help

  14. #14
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    only if i check "Fast data dump(no formatting)" in Text file Output
    Why do you want to do that?

    Why not use a text file output so that you cna format your text file as you need it?

    Mick

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

    Default

    Quote Originally Posted by verynew View Post
    Plllsss help
    This thread can go on for quite some time, if you don't provide a demo transformation or job we can correct.
    You should do the time consuming preparation of a job that runs when unzipped on a foreign system.
    We will do the surgery in next to no time, then.
    So long, and thanks for all the fish.

  16. #16
    Join Date
    Jan 2013
    Posts
    108

    Default

    hi

    here is variant, where i checked in "Text file Output>Content>Fast data dump(no formatting)", and does all perfect, only gives the date-time as a String, for ex. like this "Wed Jul 03 14:37:30 CEST 2013". I need it in Format yyyyMMddHHmmss.
    It gives it also in wished format but when i uncheck "Text file Output>Content>Fast data dump(no formatting)", but then it inserts tabs(spaces) in some columns.

    here is a zipped transformation:

    also example text-file:
    Attached Files Attached Files
    Last edited by verynew; 07-03-2013 at 08:47 AM.

  17. #17
    Join Date
    Jan 2013
    Posts
    108

    Default

    hi Mick

    maybe i did confuse you.. i do already use Text File Output, the problem is it outputs it with tabs(spaces) in some columns or gives out the date not like yyyyMMddHHmmss..

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

    Default

    I removed the Add sequence step, since it is not needed.
    Text File Input can very well provide a rownumber by its own.
    Option "Fast data dump" controls formatting of output.
    If you choose formatting, a string will be padded if necessary, to reach the specified length.
    That's why you see spaces to the right of col3.
    I corrected input and output metadata, so no padding will occur.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  19. #19
    Join Date
    Jan 2013
    Posts
    108

    Default

    Thanks a lot. i still am trying to pass it to my files, which have much more columns..

  20. #20
    Join Date
    Jan 2013
    Posts
    108

    Default

    hi there

    now it works well, except that it outputs a file without "|" in the end of lines. in Text File Input it has already this, but in output file. what should be checked or done, so that it doesn't remove it?..examples are as follows:

    input file:
    2012031210|0001|1111|
    2012072310|0006|2222|
    2012021010|0006|3333|

    after adding a date column, output text file looks like:

    2012031210|0001|1111
    2012072310|0006|2222
    2012021010|0006|3333

    ???

    thank you in advance

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

    Default

    Oops, a minor glitch.
    Add an additional String field to input and output.
    So long, and thanks for all the fish.

  22. #22
    Join Date
    Jan 2013
    Posts
    108

    Default

    Thanks a lot!

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.