How to change the data format in a excel files

Help
, , ,
1

dear all,
i have a excel file, in some column, the data format is string , how could I change the data format string to number ? and in column F, H , the data is 16 December 2019, how could i change to 20191216 ?
anybody could help me ? I need some demo code to learn it . thanks a lot in advcance .

1
11064Ă—569 6.79 KB

2

Hi
hope these steps would help you resolve this
–use excel application scope and pass the file path of excel as input
—inside the scop use a READ RANGE activity and get the output with a variable of type datatable named dt
– now use a FOR EACH ROW activity and pass the above variable as input
–inside the loop use a ASSIGN activity like this
row(“TRADE_DATE”) = Datetime.ParseExact(row(“TRADE_DATE”).ToString,“dd MMMM yyyy”,system.globalization.cultureinfo.invariantculture).ToString(“yyyyMMdd”)

similarly for column H with its column name with another assign activity next to this

then write back this datatable to the excel with WRITE RANGE activity next to this FOR EACH ROW loop and enable ADD HEADERS property in the property panel

Cheers @Chris-Yiwei

4

i would like you to simulate the workflow with the above suggested activities
the reason is if we do from our studio we will get to know more insights than on seeing a template kind of workflow…
hope you dont mistake buddy
and i m sure you can do this
kindly try this and let know for any queries or clarification
Cheers @Chris-Yiwei

5

what is the data format of row(“TRADE_DATE”), string ? or datetime?

6

its of type string
where row is the variable name from FOR EACH ROW activity and inside that row(“yourcolumnname”)

Cheers @Chris-Yiwei

7

in fact , it generates errors.
like RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime. how could i deal with it

1 Like
8

fine
so we need to validate the date format that we get from the excel file
for that use writeline activity and mention as row(“yourcolumnname”).tostring
so that we can see the date format in the output panel
Cheers @Chris-Yiwei