How to read first excel sheet?

Question

I need import data from excel every month. The excel file names and sheet names are different every time. But I only need import data from the first sheet of the file and their formats are consistent. Currently I use dynamic input to read the file, but in order to read the sheet contents, I need specifiy the sheet name. Is there a way to tell Alteryx to always read the first sheet of the file?

Peachyco · Answer

For this, I will need:

* Input Data Tool, configured to import only the list of sheet names, and to output the full path of the Excel file as a field. I will need the full path to pass to the Dynamic Input Tool later. This step will give me two columns: the Sheet Names already sorted how they appear in the file, and the Full Path of the Excel file.
* Select Records Tool, configured to take only the first record. Now I have only one record.
* Formula Tool, so I can generate a new full path of the Excel file that includes the sheet name. My output here would look like "\192.168.000.000\...\Excel File Name.xlsx|||First Sheet Name".
* Dynamic Input Tool, which will take the new path from the Formula Tool and bring the data from that sheet. I now have the data from the first worksheet of the Excel file.

My workflow would look like this:

AncientPandaman · Answer

you can do this way. if only one sheet or sheetname got any pattern.

1. input tool - load by list of sheetname, output fullpath (use wildcard in filename if possible)

2. filter - get specific sheet name (if required)

3. formula - Replace([FileName], "<List of Sheet Names>", [Sheet Names])

4. dynamic input - setup the how you input sheets (like 1st row is data, import from line x, etc), and replace fullpath in config

5. continue develop