Parse date into a single format

Help Studio
, , , , ,
1

Hi All,

I have a excel file which has column “Refer Date”, the number of rows in the excel file could be 500k-800k~.

The format in the date column is very inconsistent

examples of date which are present in “Refer Date” column are mentioned below(it consists many formats like M/D/YYYY, MM-D-YYYY, OADate format etc. refer below)

3/2/2022, 44595, 28/FEB/2022, 02-3-2022, 01-03-202, 22-02-2022, 14/02/2022, N/A, 2022-01-06-11.59.23.370265, 2022-01-12T17:56:21Z.

I want to convert them into a single consistent format like MM/DD/YYYY

Any help is appreciated.

Thanks!!

2

we can make use of the following building blocks:

grafik

Parsing many formats in one go
ensure the following:
import_systemglobalization

arrFormats = {“dd/MMM/yyyy”, “dd-MM-yyyy”, … your other formats}

DateTime.ParseExact(YourString, arrFormats, cultureinfo.InvariantCulture, DateTimeStyles.None)

feel free to adapt the culture info

Finally we can check which case we do have to handle e.g. with regex
\d+ → OADate
NA → not parseable
…

Bringing all together we can parse the date and harmonize the format

3

@Dhruvi_Arumugam
Datetime.ParseExact(Refer Date).tostring(“MM/DD/YYYY”)

4

HI @Dhruvi_Arumugam

How about this expression?

DateTime.ParseExact(YourInput.ToString,{"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss","dd.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(“MM/dd/yyyy”)

You can simply add the any number of date formats inside the {“”,“”} and the formats should be separated by comma

The above expression will look into array of string and choose the apt format as per the input string and convert them and return you the output of desired format.

Regards
Gokul

5

Hi @ppr

Thanks for the reply, but specific to OADate you mentioned we can you use regex, can you please elaborate on that how can we use it in expression, for N/A we can leave it.

Also, how can be use it on all of the data as we have 500k~ + rows.

Thanks!

6

can you share some sample data along with the column names?

7

Hi @Gokul001

Thanks for your reply.

How can I handle OADate format? and as I have 500k~ + rows how can i incorporate that change in all.

Thanks.

8

HI @Dhruvi_Arumugam

In this case you can try with Format cell activity to get the desired format.

You can try to change the format of the column

  1. Excel process scope activity
  2. Use excel file activity
  3. Format cell activity

image

Check out this XAML file

FormatCellStudioX.xaml (8.5 KB)

Or

Try with BalaReva it will applicable only in Window Legacy

Check out the thread

Regards
Gokul

9

Hi @Gokul001

Thanks! I will try this.

Thanks

10

Hi @ppr

Apologies for late reply

Please find attached sample
Sample.xlsx (3.5 MB)

Below are the Columns in the file attached:

Closing date,Team,NCI Value,SA Name,Change Record (CR) #,Reference Date

The highlighted columns are the date columns.

Please let me know for any questions.

Thanks

11

@ppr Can you please suggest I provided the sample.

Thanks

12

Hi @ppr

I provided the sample data.

Can you please have a look here and provide some insights.

Thanks

13

Hello @Dhruvi_Arumugam

Here one option is to use the Format cells activity with a loop. But if the format is static for the excel, then better you can create an Excel template with the required formats to the columns and then write the datatable to that excel. It will help to preserve format for all the columns as per our requirement.

Thanks

1 Like