Dynamic Sample Tool

[gmerce]

Hi,

I have a directory whith hundreds of Excel files. I wanted to use the dyncamic input tool to import my data in Alteryx.

Unfortunately my table headers are on the row #15 so I wanted to use a Sample Tool (with the Skip 1st N records option).

I can't do the Dynamic Input Tool and the Sample Tool at the same time... I'm stuck on this problem and I don't identify any solution to do this properly.

Does anyone has encoutered the same problem ?

Thanks a lot.

Gilles

[michael_treadwell]

Could you use the Dynamic Input tool to read the files and under 'Input Data Source Template' choose 'Output File Name as Field'

Then use a Sample tool to skip first 15 records with 'Grouping Fields' as [FileName]?

[scottj · Accepted solution]

I would suggest using a Batch macro to read and merge each file.  Set up a Text input with a sample of the location of the file (from the directory tool).  Then use the Dynamic input to read in the file followed by a sample tool to remove the first N rows, and finally a Dynamic Rename to take the field name from the first row of data.  That stream goes into a Macro output.

Now us a Control parameter and attach to the Text input.  Use the default action to update the file name.

I have attached an example of what I mean.  It looks to my directory, so it needs editing, but you should get the idea.  Run the directory tool into this macro and all files are merged to work with in Alteryx.

[michael_treadwell]

Here is an example of grouping by file path with Dynamic Input on a directory of .xls files.

[s_pichaipillai]

What about this option without Dynamic Input Tool

1. Use Regular Input Tool and Provide Dir Listing with *.xlsx

2.Use Named range Query Ex."SELECT * FROM `Sheet1$A15:B`" . here the actual rows starts from Row Number 15 

see the below image 

[gmerce]

Thank you all for your answers.

@s_pichaipillai : that works fine, thank you.

My problem was a bit more complex and I had to process each file with dynamic rename and dynamic select so I've chosen ScottJ solution with batch macros which are more powerful.

Thanks