Create a table with column headers and their respective filled rate

[Aishwarya_Bhargava]

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?

[ppr (Peter Preuss)]

@Aishwarya_Bhargava

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
  Right:

(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)

[Aishwarya_Bhargava]

that worked
thankyou

[Aishwarya_Bhargava]

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

[ppr (Peter Preuss)]

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

[Aishwarya_Bhargava]

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
eg:

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

[ppr (Peter Preuss)]

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

[Aishwarya_Bhargava]

this error is coming

[ppr (Peter Preuss)]

AsEnumerable is not a member of 'System.Data.Datatable'

Hi @lucas.mcgonagle EDIT 20.10 fixed this issue for newly created projects. You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup.

[Aishwarya_Bhargava]

that worked
thankyou

[ppr (Peter Preuss)]

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

[Aishwarya_Bhargava]

how can i append different table column wise

[ppr (Peter Preuss)]

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

[Aishwarya_Bhargava]

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

[ppr (Peter Preuss)]

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

[Aishwarya_Bhargava]

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

[ppr (Peter Preuss)]

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.

@Aishwarya_Bhargava

the headers are there but still its not coming

just share the excel with which you are testing / processing

@Aishwarya_Bhargava

I tried output data table and for each and everything to cross check

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

@Aishwarya_Bhargava

I also hard coded the column name

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}

[Aishwarya_Bhargava]

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.

[ppr (Peter Preuss)]

@ppr

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

But you agree on above quoted, right?

@Aishwarya_Bhargava

when changing to the variable without quotes

if possible share your XAML with us

[Aishwarya_Bhargava]

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