Hello,
How to convert the date format in csv file to (dd/MM/yyyy).
The values below invoice date are dynamic.
Hi @Abhi4
=> Use Read CSV to read the file and store the output in a variable say dt_data
=> Use For Each Row in Data Table to iterate through the data table.
=> Use the Below Syntax in Assign Activity
CurrentRow("your column Name")= DateTime.ParseExact(CurrentRow("your column Name").ToString,"dd/MM/yyyy hh:mm",System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")
=> Use Write CSV to write it back to the file.
Hope it helps!!
Thank you for the reply.
I want to convert all the values under Invoice Date excluding the first line
HI @Abhi4
Can you share the input csv file with dummy data so that I can get back to you with a solution.
Regards,
I am not able to upload CSV file.
Can you provide me with solution file.
I will help you with that @Abhi4
Regards,
Will there be data in the first cell every time . Please specify @Abhi4
Regards,
after reading the csv file
assign activity
dt=dt.asenumerable.skip(2).copytodatatble
for each row in datatable activity
inside use this expression
or read the excel file range as from “A2:”
so that you will skip that first line
cheers
Yes there will always data in first cell.
I want to convert the values under invoice date to (dd/MM/yyyy).
Whatever may be the date format,I have to convert to (dd/MM/yyyy).
Row number 6&7 are in different format.
Use modern excel activities
use excel file activity
inside use format cells activity
pass your new date format which you want to get
cheers
I used format cell activity to convert the date , it worked and output file stored as excel file .xlsx.
I converted excel file to csv to upload csv format file in portal.
After converting the to csv file, date formats are changed in CSV file. I used Format cell activity for CSV file but not all the dates are changing to dd/MM/yyyy.
Can you help me with any other workaround?
Hi @Abhi4
Try this
DateTime.ParseExact(CurrentRow("Invoice Date").Tostring, {"dd/MM/yyyy hh:mm", "d/MM/yyyy h:mm"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None)
The given solution works but also i want to keep value first cell above Invoice Date.
First Cell A1,is header date and I want to keep that value above Invoice date. or is it possible we can convert that value to in dd/MM/yyyy
check this its working fine
dateformat.zip (9.8 KB)
in place of read rangeactivity you can place read csv file activity and follow all the steps
if any issue let me know
cheers
Use a invoke code activity with dt as in/out datatable argument and add the read csv output datatable here
In the invoke code use this
Dt.AsEnumerable.Where(function(x,i) i>1).ToList.ForEach(Sub(r) r(0) = DateTime.ParseExact(r(0).ToString,"dd/MM/yyyy hh:mm",System.Globalization.CultureInfo.InvariantCulture)).ToString("MM/dd/yyyy")
Then write the data back to csv…
And while reading give no headers options
Cheers
only for the 3rd cell including header value is not getting changed.
