I think you mean .xlsx, although it’s close to the same as a .csv except .csv only let’s you have 1 sheet so it’s slightly different.
There’s different approaches you can take, like you could make a dictionary of datatables or a list of datatables, then just use each datatable. Or you can create a 3-dimensional dictionary so you can reference each sheet (like a group) and each column. Or you can create a 2-dimensional dictionary where all sheets get put in to the same group and the second dimension is the columns. You can also go further and use Arrays in the cells and convert them to an array as you place it into the dictionary ( like “[ ‘a’,‘b’,‘c’ ]” then using json parsing ), since each type would be an Object.
What I would say you need to do is before each loop create a new dictionary with the needed key. And, using an Excel scope with a wb variable you can loop through each sheet and apply it to the dictionary. Then within that loop, loop through each column and apply the column into the dictionary as one of the dimensions.
Example pseudocode would be like this:
out_d = new dictionary( of String, Dictionary( of String, Object ) )
Excel Scope with wb variable
For each sheet In wb.GetSheets(0)
Read Range of sheet to dt variable
For each row In dt.AsEnumerable.Where(Function(r) r(0).ToString.Trim<>"" ).CopyToDataTable
out_d(row(0).ToString.Trim) = new dictionary( of String, Object )
For each col In dt.Columns.Skip(1) // skipping first column (syntax might be off)
out_d(row(0).ToString.Trim)(col.Name) = row(col.Name).ToString
The above example is if you want to combine all keys to the same group for each sheet for a 2-dimensional dictionary. EDIT: if you want to group them by sheet just adjust the initialization of the dictionary to be 3-dimensional and add an assign in the “For each sheet”
If you would like to reference my version of an InitSettings workflow which will do .xlsx/.xls, .csv., .json, and other text-based extensions with a delimiter… feel free, here it is: InitSettings.xaml (56.2 KB)