Initialize Array Of Array, Fill and Put in DataTable

Help
1

Hello,

I would like to fill a datatable in a random order. I cannot therefore use the classical Add Data Row/Add Data Column directly. I thought about creating an Array of Arrays, that I can fill in the order that I want, and then transform its components into Data Rows. The goal is to avoid passing through an Excel sheet in order to save time and memory. Practically :

I have 12*3000 String values that I would like to fill in a DataTable (3000 rows and 12 columns). My main question is :

How do I initialize the Array of Arrays ? I tried everything, a For each inside a for each as explained here Multidimensional arrays - #2 by Java , in this case I still get an error message that the array needs to be initialized, to all kinds of initializations in an Assign activity (Assign : myArray = new String[12][3000], = [12][3000], etc. nothing works…). I know that I can write by hand {{“”, “”, “”, …}, {}, {}, … {}} 12x3000 times but I’m not gonna do that :slight_smile:

Thanks in advance,

Remie

2

Can’t you use a List of (List of (String))?

3

Thanks badita for your quick reaction !
In that case, can I use the List as a Data Row ?

4

List of (DataRow). Sure.

2 Likes
5

I mean use the List of (List of (String)) in a Add Data Row activity ? Use the internal List(Of String) as a row input I mean ? Because in the Add Data Row activity, the input has to be either an Array or a DataRow. If I use DataRow I can’t fill it in a random order in the previous step…

6

Out of curiosity - what’s the purpose? I can’t clearly see what you’re trying to achieve (or to be more precise - why).

An alternative approach would be to fill the DataTable normally in order, but access it after randomizing.
System.Random rndm = new System.Random IEnumerable(Of DataRow) randomizedRows = yourDT.Rows.OfType(Of DataRow).OrderBy(Function(x) rndm.Next)

Note: usage of OfType is to convert DataRowCollection to IEnumerable(Of DataRow) without requiring yourDT.AsEnumerable from DataSetExtensions.

Or randomize your list of rows before adding it to DT. Note that you can always convert a List to Array with a ToArray call (note that it will reallocate memory).
If you want to also use it like this, you’ll need a 3000 by 12 jagged array/list, so that each item contains all column values (it’s also faster for access that way due to contingent memory instead of having one row values spread all over the place).

Regards,
Andrzej

1 Like
7

Hi Andrzej,

I think I was not clear in my explanation, sorry… It is not actually a “Random” order. The context is : I have 3000 employees, with 1 status per employee over 8 weeks (Available, Training, Paid Leave, etc.) and 4 other infos like name, business unit, etc. Thus the 12 columns.

The thing is I will be extracting data from another excel sheet, which is unsorted, so as soon as I calculate the status for Employee1 and Week4 for example I will fill the datatable in position (1,4). I might find the status for Employee3 Week5 meanwhile and thus I fill the position (3,5), and then come back to Employee1 if I find its status for Week6 and fill (1,6) etc.

The ultimate goal is to write a new excel sheet with one row per employee, and each row contains the name, business unit, statuses for 8 weeks and other details over 12 columns in total.

The challenge is that the datatable that I filled will have to be checked before writing into the final sheet as I need to only display employees who have at least one “available” status over 8 weeks, and therefore I cannot make this selection unless all the columns are filled.

What I am doing right now is that I fill a transitory sheet with all the statuses (this is easy, as I don’t have the problem of filling the datatable in specific positions, I only need to give the cell name eg. D7 for employee 7 week 4). Then I am extracting a datatable from this sheet, removing rows that don’t contain “available”, and then write this datatable into the final sheet.

I would like to do this without passing by the transitory sheet, is that possible ?

Please let me know if something is still unclear, and thanks a lot for all your help !

Regards,
Remie

8

It should definitely be possible, just a matter of how exactly to do it.

Could you provide some dummy input? Output I can imagine, but how the input looks is pretty important for how it can be done in an efficient way.

Regards,
Andrzej

9

Assuming your 2nd excel document contains 8 rows per employee (1 for each week status update) then can you not read the excel document into 1 big data table. Create a DataView of that table and select the distinct value of Employee Name / ID whatever and put these into a new DataTable. Loop over this new datatable and select all rows in the big datatable where employee name / ID equals the value in the foreach loop which should return 8 rows. You can then populate your DataRow variable with the details from the 8 rows

10

And how do you exactly fill the list of DataRow with string values?

I have a loop which will return a set of strings - and put it in a list of strings.
And right after I need that set to be a row of a Datatable.

How can it do it? Is it recommended using a List of DataRows? In that case, can I specify which values each datarow will have?