Ingest All .csv Files found in Multiple Folders - Can't Use Directory Tool

[cravena]

All,

We have a massive, messy folder structure that contains csv files.  The files we want are inside any folder path containing folder name "XYZ".  Example:

• Top Folder
  • XYZ Folder
    • File1.csv
    • File2.csv
  • ABC Folder
    • File3.csv
    • XYZ Folder
      • File4.csv
      • EFG Folder
        • File5.csv

I want File1, File2, File4, and File5; do not want File3.  I can't use the Directory Tool to Ingest Top Folder and all its subfolders because it contains a gazillion folders and files and would take forever to load, and I don't need 95% of them.  So, I boosted a RunCommand tool I found on another community post, which returns all the folder path names inside Top Folder.  Then I use a Filter with Contains "XYZ"; now I know the folder paths I'll need.  I thought I could feed the Dynamic Input tool these paths, but it fails because Dynamic Input tool needs a FullPath including file name, which i don't have.  I only have the folder paths.  How to dynamically ingest:

\\Top Folder\XYZ Folder\*.csv

\\Top Folder\ABC Folder\XYZ Folder\*.csv

\\Top Folder\ABC Folder\XYZ Folder\EFG Folder\*.csv

A macro, probably?  Or maybe I'm missing something simple in the Dynamic Input tool?  

[MarqueeCrew]

@cravena ,

Square 1:  Go to the highest mountain (top directory).

Square 2:  Jump (directory of all CSV files in all sub-direcotries).

Square 3: Bounce (filter out any of the records that you know that you don't want to read).

Square 4: Use a dynamic input tool to read the files (assumes that the formats are the same).

The FULLPATH should be present.

Cheers,

Mark

[IraWatt]

Hi @cravena,

You could use this approach:

Put a directory tool on your top directory then filter to only include directorys containing XYZ.

Any questions or issues please ask :)
HTH!
Ira

[AmalinaH]

Hello @cravena, I agree with @IraWatt 's suggestion to go with a Directory tool. 

This generates a FullPath field of all the files under the parent folder and its subdirectories and you can then filter to those that contain "XYZ" and connect to a dynamic input.

[cravena]

All,

dir

\\Top Folder\*.csv , + Include Subdirectories takes a long time to load--it's a big folder, lots of subfolders, over a hundred thousand files (and growing, rapidly), 95% of them I already know I do not want.  I was hoping for something a bit more targeted.

Thanks

[MarqueeCrew]

Do you have a list of files or directories already?   

[cravena]

I've got a list of directories already. Example:

C:\Users\cravena\Desktop\Alteryx\XYZ
C:\Users\cravena\Desktop\Alteryx\data\XYZ
C:\Users\cravena\Desktop\Alteryx\data\holder\XYZ
C:\Users\cravena\Desktop\Alteryx\data\Maintenx\XYZ\SUBFOLD
C:\Users\cravena\Desktop\Alteryx\data\processed\SUBFOLD\XYZ

The filenames are more or less unpredictable.

[MarqueeCrew]

I can jump on a zoom and show you techniques.  Essentially if your list is fixed, you can have multiple directory tools with a Union leading into a dynamic input.  Alternatively, you can create a batch macro to handle the searching.  

there isn't a magic figure out what files to read easy button. 

cheers,

 mark

[cravena]

Thanks, Mark.  I'll investigate the batch macro.