How to convert text to columns into date format

Help Studio
, ,
1

Hi All,
I have 2 columns that contains date and I need to convert their date type from text to date using Data>Text to Column>Next>Next>Date.
I am copying these columns from a master sheet and pasting it into this particular columns.
Can it be done without UI interaction??

2

Hi @Kunal_Jain

Can you please share a sample input & expected output screenshots, so that we can analyze your requirements?

Best Regards.

3

image
image1080×431 21.8 KB

Here is the data
Column W and AD both contains date and the format for both of them is text .
we need t convert it in date format but without ui interaction.

4

@Kunal_Jain

Please use format cells actovity from excel activities and select the format you need as date

Cheers

5

Hi @Kunal_Jain

  1. Use the Read Range activity to read the data from the master sheet, storing it in a DataTable variable, let’s call it masterData.
  2. Create a new DataTable variable, let’s call it convertedData, to store the converted data.
  3. Add an Assign activity to copy the columns containing the date values from the masterData DataTable to the convertedData DataTable.

convertedData = masterData.DefaultView.ToTable(false, “DateColumn1”, “DateColumn2”)

  1. Add an Invoke Code activity to perform the conversion of text to date. In the Properties pane of the Invoke Code activity, provide the following code:-

For Each row As DataRow In convertedData.Rows
row(“DateColumn1”) = DateTime.ParseExact(row(“DateColumn1”).ToString(), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture)
row(“DateColumn2”) = DateTime.ParseExact(row(“DateColumn2”).ToString(), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture)
Next

Thanks!!

6

Invoke code: Exception has been thrown by the target of an invocation.
It is giving me this error

7

format cell activity from generic value right??

8

@Kunal_Jain

You want to change the format of cell for that you have format cells activity

Cheers

9

can you help me with example

10

Hi,

You can try the below code in invoke code activity:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(“File Path”)
excel.Visible=False
ws=CType(wb.Sheets(“SheetName”),Microsoft.Office.Interop.Excel.Worksheet)
ws.Range(“W1”).NumberFormat = “dd/mm/yyy”
wb.Save
wb.Close

Please pass the file name and sheet name as arguments.
Hope this helps!

11

@Kunal_Jain

https://docs.uipath.com/studiox/docs/tutorial-formatting-cells

Shows studiox…but works for studio the same way

Cheers

12

And what is the range W1??

13

Its for changing the format of W column.

14

If I need to change it for W and AD column both?
So what should I add to do it for both??

15

Invoke code: Exception has been thrown by the target of an invocation.
Getting this error

16

You can add another line for AD

17

I am getting the above mentioned error

18

Please check the file path and sheet name also make sure that Microsoft.Office.Interop.Excel is imported.
The above code is working for me.

19

image
image903×439 11 KB

20

Not in Manage package. There is an import tab after variables and argument tab… please check there