Create a record ID

Question

I have a file that includes transactions split up by type in different columns. The rows are defined by the date these transactions occurred.  The entire sheet relates to 1 entity, the name of the entity is presented in the middle of the entire sheet, row 1, column g. How can I create a column right before the first column, and copy the name of the entity, that is presented in the center of the sheet, down to every row to now serve as a record ID. The entity name is also the name of tab.  So I can pull it from the tab or the cell it is located on the sheet. Whichever is easier

Claje · Accepted Answer

Hi,

When you bring in an Excel file, one of the options in the configuration is "Output File Name as Field", and you can choose "Full Path".  If you do this, you will get a column with the full file name, including each of your sheet names.  You can then use a Select tool to move this wherever you want, and you can use a Formula like RIGHT(Filename,FINDSTRING(Filename,'|')) to get to the sheet name.  This should get you what you need.