Read files with multiple sheets from same directory

[srk0609]

I have multiple files in directory but I just want to read sheets present in File 1, File 2 and skip other files. Can someone help me build a workflow that would help me extract data from sheets having data in them. I want to exclude sheets with no columns, no data and so on below is the description of sheets present in the files. Attached below are three files of the directory.

Sheets in File 1 and 2 having perfect schema - Jan 24, Feb 24, Apr 24, Jun 24, Jul 24, Aug 24, Sep 24

Sheets in File 1 and 2 having spaces before perfect schema - May 24, Extra

Sheets in File 1 and 2 having column names but no data(need a way to automatically exclude sheets with no data) - Mar 24, Oct 24, Nov 24

Sheets in File 1 and 2 having no column names and data(need a way to automatically exclude sheets with no data) - Dec 24

Sheets in File 1 and 2 having space before only column names(need a way to automatically exclude sheets with no data) - Extra 2

Sheets in File 1 and 2 having having no data and not needed to be included in data pull - Not to be included

Sheets in File 1 and 2 having having data but not needed to be included in data pull - Not to be included 2.

I connected directory tool but it doesn't give option to read sheet names within the files.

[Bren_Spill]

Hi @srk0609 - One way to do this is with a nested batch macro. The steps are as follows:

• Main workflow
  • Directory tool to read in file paths
  • Formula tool to add "|||<List of Sheet Names>" to the Full Path
    • This will be the control parameter for the next macro
  • Data flows into Macro 1
  • Add Browse or Output tool after Macro 1

• Macro 1 - List of Sheet Names macro
  • Input tool with Table or Query set to List of Sheet Names, and Output File Name as Field set to Full Path
  • Control parameter and Action tool connected to Input tool
  • Control parameter set to FullPath
  • Action tool set to Update Value (Default), updating the File - value =...... line
  • Within Interface Designer - Properties, set Output Mode to Auto Configure by Name
  • Formula tool to replace <List of Sheet Names> in FileName with actual sheet names
    • This will be the control parameter for the next macro
  • Within this macro, you can filter out any of the files or sheet names you don't want to include before reading in the data in the next macro
  • Add Macro 2
  • Add Macro Output tool

• Macro 2 - Read Data macro
  • Input tool set to read in one of the sheet names in the connected file
  • Control parameter and Action tool connected to Input tool
  • Control parameter set to FileName
  • Action tool set to Update Value (Default), updating the File - value =...... line
  • Within Interface Designer - Properties, set Output Mode to Auto Configure by Name
  • In this macro you can cleanse individual sheets as they are read in
    • Use Dynamic Rename to cleanse field names to remove spaces etc..
    • Do any other required cleansing
  • After cleansing is complete, add Macro Output tool

Give this a shot and let me know if something isn't working for you.

[Qiu]

@Bren_Spill 
Very detailed. 👍

@srk0609 
You may want to check this post.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Input-multiple-Excel-files-wit...

[srk0609]

could you please explain with an example workflow because it is very difficult to follow this. I haven't created Macro before so if you could explain me with example it would be great. Thank you.