Robotic Process Automation (RPA)

 View Only
  • 1.  How to read excel columns with custom format

    Posted Thu May 05, 2022 10:40 PM
    Hi Team,

    I have a column with a custom format ( date: yyyy/mm/dd ) as shown in the screenshot, when I try to read this column data using datatable and map row functions I see a numeric value, something like this 44215. I would need the exact value from excel ( 2022/05/23), please suggest



    ------------------------------
    KARTHIK GUTTAPUDI
    ------------------------------


  • 2.  RE: How to read excel columns with custom format

    Posted Fri May 06, 2022 04:18 AM
    Edited by Yon García Nistal Fri May 06, 2022 04:18 AM
    Hi Karthik,

    You just need to use the Convert Date Time to Text command:


    dateTimeToText --date "${my_date}" --usecustomformat  --customformat "yyyy/MM/dd" my_date_custom_format=value

    ${my_date} is the variable where you are storing the value read from Excel and selecting Use custom format, you can obtain a text variable with the desired format.

    Hope it helps.

    ------------------------------
    Yon García Nistal
    Skechers Iberia
    ------------------------------



  • 3.  RE: How to read excel columns with custom format

    Posted Fri May 06, 2022 05:34 AM
    Hi Yon García Nistal

    Thank you for looking into it, in my case the variable "my_date" is of String type, so I cannot use the Convert Date Time to Text command.
    If I change the my_date type to Date then the Get Excel Value command won't work.


    ------------------------------
    KARTHIK GUTTAPUDI
    ------------------------------



  • 4.  RE: How to read excel columns with custom format
    Best Answer

    IBM Champion
    Posted Fri May 06, 2022 08:31 AM
    Hi Karthik,

    This number is how Excel treats dates internally and IBM RPA just gets this raw value. Each number represents the number of days passed since a base date (1899-12-30), so what you have to do is add the number you read from the cell to the base date, as in the example below:
    setVar --name "${dataBaseExcel}" --value "1899-12-30 00:00:00"
    convertStringToNumber --text "${strData}" intData=value
    addToDateTime --date "${dataBaseExcel}" --value ${intData} --type "Days" data=value​

    Hope this helps.

    Regards,



    ------------------------------
    Vinicius Marques
    ------------------------------



  • 5.  RE: How to read excel columns with custom format

    Posted Fri May 06, 2022 11:34 PM
    Hi Vinicius Marques

    Thank you for taking time and explaining it, appreciate your help, tried the way your suggested and it works.


    ------------------------------
    KARTHIK GUTTAPUDI
    ------------------------------



  • 6.  RE: How to read excel columns with custom format

    Posted Fri May 06, 2022 09:37 AM
    Hi KARTHIK GUTTAPUDI,

    I had a similar issue with this also, my solution was to change the excel custom format to a text format, so when the bot retrieves the value it will get exact what you see in the excel.

    But I am curious if someone have a solution for it that we dont need to change the excel format. I will keep track of this post. =)

    ------------------------------
    Bruno Raphael Marques de Santana
    ------------------------------



  • 7.  RE: How to read excel columns with custom format

    Posted Fri May 06, 2022 11:36 PM

    Hi Bruno Raphael Marques de Santana

    Thank you for looking into it, in my case I cannot change the cell format manually.

    The solution provided by Vinicius Marques is working fine.



    ------------------------------
    KARTHIK GUTTAPUDI
    ------------------------------