Split Data Table based on unique values in column and Save each file with unique name

I have a Data Table with a column that contains values that can change and has some unique and some duplicate values. I want to split the data table based on that column. It needs to loop through and collect groups of the same values, save them to a Data Table, and then save the Data Table to a CSV with a unique name based on the column it is sorting by.

This is a sample of what the data looks like. Column R is what I want to split and save. Each CSV should have a unique name based on the value of column R.


Sample Data UI Path.xlsx (9.6 KB)

The problem I’m having is the for each set up to loop through, copy duplicate values of column R to a table, take the value of column R, and then save the CSV based on all of that. Can anyone assist?

@Freeman_Alex
find starter help here:
DT_Slice_GroupBy_1Col.xaml (8.4 KB)

in your case LINQ could look like this:
(From d In dtData.AsEnumerable
Group d By k=d(YourColNameOrIndex).toString.Trim Into grp=Group
Select grp.CopyToDataTable).toList

when iterating over the table list with:
YourCurrentLoopDataTableVar.Rows(0)(RColumnNameOrIndex).toString.Trim
will retrieve the the GroupKey Value and can be used for the CSV filename.

2 Likes

Thank you! This is almost working exactly as I need. The only problem is that in the For Each statement, my file is now saving with a new name, it’s overwriting the old one with the name of the last file it should write.

ui2

I’m assuming I have my assign in the wrong place? I want it to save the file, then create a new file with the name of the new value in column R of the sheet. It’s almost doing that, very close!

@Freeman_Alex
on a first look ot looks good:

  • linq split the tables
  • iterate over the tables:
    • fetch current R Col Value
    • use current R Col Value for the filename

sure we cannot inspect all from the screenshot but also do not know the variable values.

give a try on fetching the current name:

accnum = table. Rows(0)(…

Else Do one thing

  • put a breakpoint on write csv
    debug / after breakpoint go stepwise and inspect the flow/vaiables / results

I had the data table being referenced for the file name variable wrong! I was using the original data table and not the tables being split. It is now working exactly as intended! Thank you so much!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.