To change the format of the data in excel to another format

[sushmitha.e]

Hi Team,

I have the data in this format,

But need to get this as below format,

Can anyone help me out to get this format.

Thanks in advance

[pavankumar267 (pk)]

Hi Sushmitha, i believe the direct conversion is not available. however in your case, you can perform below steps:

1. Create a Excel File as per your requirement (As per second image)
2. Extract the data from First excel file and fill accordingly in second excel file

[SenzoD (Senzo Dlomo)]

Hi

Can you share the input file?

[sushmitha.e]

Hi, Sorry it has the client data, so couldn’t send the input file. So I have provided the images above with sample data.

[SenzoD (Senzo Dlomo)]

You can replace sensitive data with dummy values

[noufalahammed]

Please try the following steps

1. Read the excel using Read range
2. Convert the datatable to text using Output Data Table activity
3. Convert it to the text datatable using Generate Datatable from Text activity, use : as the delimitter
4. Write it back to an excel

Check the whether the key and values are in separate columns and change it according to your desired excel format

[sushmitha.e]

OK.

SampleInput.xlsx (10.1 KB)

[SenzoD (Senzo Dlomo)]

Thank you,

Will there ever be a case where there are more line items than you provided on the sample file or will it always be the same number of row/line items?

[SenzoD (Senzo Dlomo)]

Give this a try and let me know:

Practice01.zip (13.1 KB)

[sushmitha.e]

Thanks for the solution, but when I take input file with more than 100 rows , getting only the last row value in the output sheet.

[SenzoD (Senzo Dlomo)]

To get the assistance you need, please make sure you provide a realistic input sample and also as many details as possible.

I built the solution based on the input sample you provided.

below is the updated code:

Practice01.zip (13.9 KB)

[sushmitha.e]

Thanks @SenzoD , its working now.

[SenzoD (Senzo Dlomo)]

@sushmitha.e

great, happy I could help, please make sure to mark it as a solution

[sushmitha.e]

Hello @SenzoD ,

For date column, for some rows we have 2 values for same column 2 and it is extracting the below row value, not exactly the date value as shown below

This is how getting date value in the highlighted row

And the logic used is ,

Could you please check into this once.

Thanks.

[SenzoD (Senzo Dlomo)]

Hi

You need to skip or exclude text that isn’t a date, eg below will exclude text that does not contain a “/”, you can make adjustments as you need.

If(String.IsNullOrWhiteSpace(CurrentRow("Column2").ToString), strDate, If(Not CurrentRow("Column2").ToString.Contains("/"), strDate, CurrentRow("Column2").ToString))

[sushmitha.e]

Hello

For one input value, Deduction is coming as empty to the output. While I debug came to know like it is getting cleared for some reason as shown below,

DataRow { HasErrors=false, ItemArray=object[21] { “Desc: “This item was originally shipped cross-border and will be returned to Wayfair. Deduction reflects the cost of return originally invoiced on #518597068 and the Wayfair Managed Return Rate [(WSC - Allowances) x WMR rate (0.8)].””, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “” }, RowError=“”, RowState=Added, Table=[DataTable] }

For this deduction value mentioned in the excel

Sample.xlsx (9.5 KB)

Thanks.