Output to one xls with variable sheet names

Question
1

i want to output to multiple new sheets in one excel file.

the sheets don’t exist. so i would be writing to a newly created sheet. i want the name of the sheet to be the value in a field.

for example, if i had [cities] with 50 unique values and each city had other related data, then i want one file that had 50 tabs where each tab was a city name and the contents of each tab would be the related data.

i understand that i can have variable file names. but can i have variable sheet names that come from field names? what would be the best way to do this?

2

See Split according to row values at:

Only, instead of writing to multiple files, you are writing to multiple sheets of 1 file. So the output column will have the file and sheet name, e.g.:

myspreadsheet.xlsx[Sheet1]
myspreadsheet.xlsx[Sheet1]
myspreadsheet.xlsx[Sheet2]
myspreadsheet.xlsx[Sheet3]

image
image1920×1116 162 KB

multi-sheet.transform (1.9 KB)

3

that was helpful. i did a successful quick test.

i think that the thing i wasn’t sure about was what would happen when you have an output column with the same value. and after your reply and a quick test, i understand that the “write mode” of “overwrite” won’t overwrite previous rows.

rather,

  • within a run, it appends (each row), and
  • between runs (when overwrite is chosen), it overwrites .

thanks!

1 Like
4

Yes.

Yes, unless you set Write mode to Append.