How to avoid Text format (ex: 2-13) converting to Date format (02/13/2019) when writing in Excel

Help
1
  1. an Web browser URL has an data in this format 2-13, 4-6 and using Data Scraping building an Data Table.
  2. the Write line for the Data table output (Scrapped Data) shows the output (in UiPath) in same format 2-13, 4-6 as in URL.
  3. But, when writing the Data table output in to an excel sheet using Write Range, it is getting converted into Date format as 02/13/2019 , 04/06/2019.

How to avoid converting into Data Format?

Please advise.

1 Like
2

Hi @Gopi_R

in for each row

assign row(“Column Name”)=CStr(row(“ColumnName”).ToString

Try this and check it out

Thanks
Ashwin S

3

Hi @Gopi_R

Welcome to uipath community
—hope you have saved the variable obtained after Data scrapping named outdt (for example)
— the. Use a for each row loop and pass the above variable outdt as input and within which use a assign activity like this
row(“yourdatecolumnname”) = row(“yourdatecolumnname ”).ToString
Now use a write range and pass the datatable variable and try to write into a excel

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @Gopi_R

4

Hi @AshwinS2 ,

It didn’t work. same result. Thanks for your reply.

5

Hi @Palaniyappan

Thank you for your Welcome :slight_smile:

Using the default variable : ExtractData Table in Data Scrapping.
I tried using For each and used assign activity with below one

row(“yourdatecolumnname”) = row(“yourdatecolumnname ”).ToString

and even the below one didn’t work.

row(“Column Name”)=CStr(row(“ColumnName”).ToString

in both of the value I had used the Column Name in my Table.

Thanks for your kind help.

6

Hmmm … fine
—let’s do one thing try to write that to a cvs file and then let’s try to convert that to excel followed by a datatable
Like
—once the datatable is ready with the data use a write csv activity and pass the datatable as input and mention the file path of a cvs file, mention the delimiter as comma and enable the add headers property so that a csv file will be written
— now use a read csv file and pass the same file path as input and get the output with a variable of type datatable named finaldt
—now use this finaldt as input in write range

Hope this would help you
Kindly correct me If I m wrong
Cheers @Gopi_R

7

@Palaniyappan

I had tried csv format before, it didn’t work.

Even now I tried to do the same. But it is getting converted to Date format both in csv and excel file.

8

fine
In for each row loop that we used earlier to convert that to a string use like this
row(“yourcolumnname”) = “=TEXT(“+row(“yourcolumnname”).ToString+“, “+”dd-MM”+”)”
And then pass this datatable as input to write range activity
Cheers @Gopi_R

9

With this Assign Value “=TEXT(“+row(“yourcolumnname”).ToString+“, “+”dd-MM”+”)”

I am getting below one :

#NAME?

inside cell value of #NAME? in excel shows as =TEXT(2, dd-MM)

But I had modified your assign value as row(“EQPSC UNIT”).ToString+“,"

and I am able to get required output but with comma separator. If I do remove the comma separator it gives me in Date format.

Output :
2,
2-22,

I had tired two options earlier but manually.

  1. after writing the data to an excel, I can custom the cell value for a particular column to m-d in excel then it changes to 2-22 but inside cell value it will be in Date format.

  2. After writing the extracted output to an excel, I had copied the particular column value to an Notepad and again pasted it back to excel file. This time it didn’t show the cell value in Date format. But as 2-22.

To avoid these manual work, I am trying to get help from you guys. Since I have multiple data in multiple excel files.

Thanks