Create a table with column headers and their respective filled rate

Hi team

I have an input excel file, for example
sample input.xlsx (8.8 KB)

and I want the output to be like
sample output.xlsx (8.8 KB)

so how can I do it?


create an empty datatable with build datatable and configure the cols: colname, filledrow, percentage - dtResult

then do following:

option one:

  • for each (not for each row) and loop over the cols
  • within the body block calculate the info of the current looped row

option two:

  • A LINQ statement within an assign activity
    left: dtresult
(From c in dtData.Columns.Cast(of DataColumn)
let fr = dtData.AsEnumerable.Where(Function (x) Not (isNothing(x(c)) OrElse String.IsNullorEmpty(x(c).toString.Trim))).Count
Let p = (fr/dtData.Rows.Count*100).toString("F2")
Let ra = new Object(){c.ColumnName, fr,p}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

Find starter help here:
ColumnFillingsReport.xaml (10.4 KB)

1 Like

that worked
thankyou :slight_smile:

how can I add one more row at the top which shows the total no. of rows
for eg:
and can it be added to the same table
as I need to add other tables as well in the same sheet

we prefer to let a datatable within the tabular format. (does mean strict schema of rows and colimns.

For creating such an excel give a try on following:

dtData (sampleInput table) can be used for the first excel row e.g. by Write Cell

Write Cell
Range: A1 Value: TotalRowCount

Write Cell
Range: B1 Value: dtData.Rows.Count.toString

then with a write range (add Headers) and an offset to Range: A2 we can write out the Statistic Block

I thought of doing that but in the next step I am trying to append tables column-wise with a 2 column gap between 2 tables

so won’t it cause any trouble
and also how can I append tables column-wise with 2 column gape

just try and do it
it has chance that it will work

this error is coming

that worked
thankyou :slight_smile:

Just do your final testing and once it working mark the solving post as solitopn. So others can benefit from it. Thanks

how can i append different table column wise

using offset in write range e.g F1 for second table

Assign: Column ‘‘MTART’’ does not belong to table DataTable.

i am getting this error even when the column exists

I am using a variable to show mtart

check your input excel, maybe also the first 3 rows are over the data block acting like a headerline
ensure with offset or others that the first row is containing the column headers

the headers are there but still its not coming I tried output data table and for each and everything to cross check, I also hard coded the column name and checked but not working, I copied the data in separate excel sheet, still nothing

lets try to divide the the request, the requirements and new/different scenarios

  • Initial you was asking for a generated statistical report on datacolumn fillings

    • provided xaml was doing this at least the result was very close to the sample
  • then you was asking for an added rowcount summary info on top

    • an option with write cell for doing this was shared
  • then writing multiple table statistics side by side was asked

    • range offset was suggested

So just focus on the inital origin request and let us focus on this.

just share the excel with which you are testing / processing

debugging, inspections on watch / immediated panel are the recommended practices (feel free to chec out the course to this topic on UiPath Academy)

not recommended to do this

refering to your screenshot:

we should not do any quote surrondings on name

a string variable sapColumn = “MTART”

would be used like this:
From x in {sapColumn}

when changing to the variable without quotes
it’s giving the following error
Assign: Object reference not set to an instance of an object.

and when I add quotes following error
Assign: Column ‘‘MTART’’ does not belong to table DataTable.

But you agree on above quoted, right?

if possible share your XAML with us

Main.xaml (44.7 KB)
GetTransactionData.xaml (10.3 KB)
InitAllSettings.xaml (10.8 KB)
Process.xaml (14.3 KB)
MARA.xaml (9.6 KB)

these are the workflows

sample.xlsx (2.7 MB)

this is the sample input file