Date Format changing after reading CSV

Hello… I have a csv file with many columns one of which is a date. The date in CSV file is in dd-MM-yyyy format. After i use a Read CSV activity the date is changed to yyyy-MM-dd format in the DataTable. And I am writing it as a merged excel (.xlsx ) file.

The CSV file cell format is “Date” and after writing in Excel the cell format is “General”.

I want to retain the same format as the CSV in the Excel too. Please suggest a way. :slight_smile:

1 Like

Hi
inside the “For each row” loop where you will add an apostrophe (’) before each value which you want to retain as text with a simple assign activity like this
row(“yourcolumnname”) = “‘“ + row(“yourcolumnname”).ToString

and then use Write Range activity so that would retain that same format
But still the column in excel would be in text format
Because if we try to write a date in excel it will automatically gets converted to date format or even sometimes to number format

As we need to know the value visible in excel we need to have the same format from csv and this apostrophe can help us with that, unless we don’t use that date value
No worries even if we are using we need to replace the value with that apostrophe like this
row(“yourcolumnname”).ToString.Replace(“‘“,””)
@Arun_Prasad1

Hope this would help you
Cheers @Arun_Prasad1

1 Like

Thanks bro for the quick response. Let me try that at once and will let u know.

Hello @Arun_Prasad1,

I found the same. It has something to do with the native formatting of Excel…But the. easy way around it is to use the PARSE DATE activity (you may need to add a Package dependency for your workflow {Manage Packages > Workflow Manager Activities}
Activities

Then in the Parse Date activity change the CultureName to “en-GB” (English - Great Britain" or “en-US” (English - USA)…try both to se which gives best results. you will also need a DateTime variable as the RESULT and your output variable in the VALUE field.

dtime

Then use an ASSIGN: OUTPUTvariable = DTvariable.ToString(“dd-MM-yy”).

Hope this helps

MikeB

I did this. But i got an apostrophe (’) added in front of the date. The date format was not retained.

1 Like

so you are reading a cvs to a datatable and after you are writing to excel? if in the csv the date format is tnot the same as in your own system default format, when it goes to excel you just need to format as you wish, is not good to force a string conversion, better to leave as a DateTime always for storing the data…

Yes. I am reading a CSV to a datatable, Then writing it in a excel.

How to format the date when it goes to excel? Even the default date format in the Excel in my system is dd-MM-yyyy.

Thanks Mike. Let me try this…

Well are you going to use that date any where with the format we want
If so we can change Or we don’t need to because the excel will format it by default and even if we want we can change that format while fetching the same data from excel
Cheers @

i mean excel can open csv files too, why you are converting them using uipath? formatting columns in excel is very easy:
Capture

Aaha ok

To change the format while entering to excel
row(“yourcolumnname”) = “‘“ + DateTime.Parseexact(row(“yourcolumnname”).ToString.SubString(0,10),”yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture)

Yah to remove the apostrophe when we fetch same data from excel we can implement this step

This will retain your date format if needed @Arun_Prasad1

I am merging monthly reports (CSV Files) into a Yearly report (.xslx File). So I have to retain the format as in the monthly report.

And I tried the option you gave inside a for each row. But in the Assign activity I get an exception.

1 Like

Bro… I am merging monthly reports (CSV Files) into a Yearly report (.xslx File). So I have to retain the format as in the monthly report.

Your Date column is already of the type DateTime (as i think is best), so if you try to set as string it will give an error…

Aah
I missed mentioning .ToString atlast
That was issue so the expression be like this
row(“yourcolumnname”) = “‘“ + DateTime.Parseexact(row(“yourcolumnname”).ToString.SubString(0,10),”yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture).ToString

Cheers @Arun_Prasad1

Thanks bro… Now am getting a error “Assign: String was not recognized as a valid DateTime.”. Does it mean the DataTable is not having the values in DateTime format??

1 Like

Fine
Let’s do one thing
Use a writeline activity and mention the input row(“yourcolumnname”).ToString so that we will come to know the date format from excel and based on that we can format the expression so that we could come up with a expression
Kindly share that result we are almost done

Cheers @Arun_Prasad1

I got the below values.

image
{
“message”: “2017-10-10”,
“level”: “Information”,
“logType”: “User”,
“timeStamp”: “23:11:17”,
“processVersion”: “1.0.2”,
“jobId”: “3babb737-23e8-4176-9d8c-2efdfb69bf5d”,
“robotName”: “LearningBot”,
“machineId”: 196691
}

1 Like

seeing this im sure in datatable it is not a Date format, it is simple string…

Hmm this
Expression should work upon this
row(“yourcolumnname”) = “‘“ + DateTime.Parseexact(row(“yourcolumnname”).ToString.SubString(0,10),”yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture).ToString
Is there any row along that column with null value
That might throw this error
Cheers @Arun_Prasad1