Combining specific rows from a dozen xlsx files each containing multiple sheets

[aagha]

I've got 30 xlsx sheets, each around 30k.

I'm trying to follow the directions here to pull rows from these files into a single sheet so I can work with them:

https://jsvine.github.io/intro-to-visidata/intermediate/joining-sheets/

What's not clear to me is when I open the files w/ vd *.xlsx --skip 3 to remove the headers, I see the right number of files in Sheets (31):

But when I go into each one to go to the right tab:

Give them all headers and select the key column:

When I go bac k to Sheet, it creates a copy:

Should it do that for just taking a sheet, giving it headers and then designating a key column?? I'm fine if it does.

Where I'm getting stuck is that when I join a couple of sheets, a few things are making the data hard to work with:

1. All the data is combined in a way where I don't know which data came from which sheet. How do I mark each row so I know what sheet it ame from.

2. Is there a way to have marked the specific rows I wanted to match in the 1st sheet and in all the subsequent sheets I slect, have matches only for those rows in the key columns be a part of the join?

3. Is there a way--since all the files are uniform--to take the actions of giving them all headers (gz^) and selecting the first column (campaign) as the key so I don't have to go and do this in each of the 30 files?

[saulpw]

Should it do that for just taking a sheet, giving it headers and then designating a key column?? I'm fine if it does.

It created a "new" sheet when you opened it. Note that the first 30 sheets you're seeing are all XlsxIndexSheet (the list of tabs in your xlsx file), whereas the new one is an XlsxSheet (the actual Table).

1. All the data is combined in a way where I don't know which data came from which sheet. How do I mark each row so I know what sheet it came from.

On joined sheets, there's a hidden column that contains the sheet. Try using gv to unhide all columns.

2. Is there a way to have marked the specific rows I wanted to match in the 1st sheet and in all the subsequent sheets I slect, have matches only for those rows in the key columns be a part of the join?

What kind of join are you doing? An inner or extend join should work this way.

3. Is there a way--since all the files are uniform--to take the actions of giving them all headers (gz^) and selecting the first column (campaign) as the key so I don't have to go and do this in each of the 30 files?

You could make a macro. There's no way to do this completely automatically though.

[aagha]

All the data is combined in a way where I don't know which data came from which sheet. How do I mark each row so I know what sheet it came from.

On joined sheets, there's a hidden column that contains the sheet. Try using gv to unhide all columns.

I tried that. No luck. I used g_ to expand all the columns to make sure I wasn't overlooking them.

Is there a way to have marked the specific rows I wanted to match in the 1st sheet and in all the subsequent sheets I slect, have matches only for those rows in the key columns be a part of the join?

What kind of join are you doing? An inner or extend join should work this way.

inner.

Confirmed that is working.

Is there a way--since all the files are uniform--to take the actions of giving them all headers (gz^) and selecting the first column (campaign) as the key so I don't have to go and do this in each of the 30 files?

You could make a macro. There's no way to do this completely automatically though.

Thanks, I'll give that a shot.

[saulpw]

All the data is combined in a way where I don't know which data came from which sheet. How do I mark each row so I know what sheet it came from.

If you're doing an inner join, then the rows are necessarily coming from all sheets. (If the keys aren't present on all sheets, they wouldn't be included!) So I'm not sure what you want to 'mark each row to know what sheet it came from'.