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.
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
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}
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.
Then use an ASSIGN: OUTPUTvariable = DTvariable.ToString(“dd-MM-yy”).
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…
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 @
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
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
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??
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
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