Forum Discussion
Import Excel files from Sharepoint and need the worksheet name as a field
Managed to solve this using the Web connection method on each spreadsheet in the sharepoint folder and going back some steps in the applied steps on each to get the worksheet name as advised in the steps above.
Tried to do it using the sharepoint folder connection method and inserting step to change the type from contents to data but was unable to get it to work.
Super UserHello dmccardle,
This step is automatically added when you choose to get data from an Excel worksheet. This is essentially a drill-down into the object with the data. It does not retain any of the previous rows/columns but replaces it.
If you wanted to keep the worksheet name (or the name of whichever object you got the data from), you can do it by selecting the previous step and expanding the column containing the data, by clicking the icon which looks like diverging arrows in the header of the column containing the object. This will retain the object kind (table, sheet, defined name, etc) and the object name.
I filtered the table in the snip above to only show the sheet named Sheet1, then expanded and the result is shown below. I have the data rows from Sheet1 as well as the columns previously displayed with the object kind, name, etc.
I connected to an Excel file locally in this example, however the method is exactly the same regardless of if the files are on your computer or in SharePoint.