Linq / Method to convert string into date format for a large dataset

Help Activities
, ,
1

Hi,

I need to convert the attached CSV data file into Excel.

Challenge:
I need to retain the date format (in dd-MM-yyyy format) while writing the file to excel.

Sample Data.zip (19.3 KB)

2

@Ayush_Purohit_IN

I don’t see any issue
Check as below

image
image1928Ă—640 56.5 KB

Hope this may help you

Thanks

1 Like
3

Is the Exp_Date is in Date Format?
As for me, the cell format is coming as General.
Also, If you expand the column width, if it’s date, by default it’ll be right aligned.
Can you check pls.

4

give a try on
read ccv - write excel
Use below Activity and reset / force the cell format
grafik

5

@Ayush_Purohit_IN

I didn’t format any cell, I check the settings

image
image1233Ă—819 51.4 KB

And it’s General only

Thanks

6

I have already tried that. I used the format as : “dd-mm-yyyy”
Should I have to use any other format?

7

Yes… So the issue is, I need the dataset in Date Format and not General.
There are certain valuations, which are dependent on the same. (Don’t need alternate solutions for this).
Now if I use for Loop, It’ll take min. 30 mins.
So I am expecting if the dataset can be read from csv, then converted to date format → then write data to excel.

8

format string depends on your local. Just explore the the advanced tab and offered suggestions / formats. Doi it 1 manually till you have found the right format. Then incorporate it into your bot implementation

9

Can you help me with xaml?
I want the values to be explicitly in “dd-mm-yyyy”

Note that - I did tried the activity ; and although it is reflecting the cell as Date, but the value inside the cell still remains Text.
Once I F2 and Enter the cell manually, then the date is getting converted to Date format

10

we already got it. Please show us your implementation and show us a screenshot from format cell advanced tab with some date sample formats. Format string is different and locals dependedn as already mentioned. Maybe DD-MM-YYYY is needed. We have to find out together with you.
Thanks for support

11

Pls find attached herewith.

unnamed
unnamed1920Ă—1166 420 KB

12

@Ayush_Purohit_IN - when convert a string to date format it will always gives with / as shown below…

image

If you would like custom date format as shown in your excel , you might have do it manually.

13

Yes. This is true. But here the problem is different.

Ques is why half of the columns as shown in above screenshot are coming as Date format correctly but the rest in Text format.
I also used a different sequence which is working all good. But this solution takes over 30 mins to process 10k rows.

  1. Build newDT (with Date column Format)

  2. For Each row in originalDT
    Add DataRow in newDT → {DateTime.ParseExact(row(“rowName”).ToString, “{dd-MM-yyyy,dd/MM/yyyy}”, Sys.Glo.Cul.InvariantCulture)}

  3. Write To Excel

14

We requested for following (See it for german locals)

grafik
grafik716Ă—744 54.1 KB

So prototype your custom local format string

15

unnamed
unnamed1920Ă—1440 593 KB

Screenshot taken post bot has completed processing

16

see and noticed that we have marked advanced, was also marked in the screenshot. Please recheck the screenshot from above. In your screenshot called Custom

grafik

17

Update:
I had to switch to loop mechanism as I did not see much difference in performance using different methods.

For Reference:

Using Invoke Code was working for me - Check the solution from Wendy, but we need a bit tweaks there.

Convert String Column To DateTime In DataTable (microsoft.com)

Also, using For each was giving similar results:

Steps:

1. Read CSV in dt.
2. If your data is present in "dd/mm/yyyy" or "dd-mm-yyyy" singular format/s just use replace function to streamline the datatable -> Store it in newDt.

LINQ query to update(replace)datatable in uipath - Help - UiPath Community Forum

3. Create a new TestDt with column where date needs to be formatted - with DateTime Format.
4. For Each row in newDt{
Try:
   Add DataRow in TestDt -> Array Field : {DateTime.ParseExact(row("ColName"), Format, Sys.Glo.Cul... }
Catch:
   Continue / Exception -> As per your flow.

If anyone can optimize it further pls update the forum. To process 10k rows it took 2.1 secs using above flow.

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.