Importing multiple excel files with headers

[Korpelak]

I have various excel files where the first 27 rows are just garbage that I would prefer not importing. I know that I can use the sampling tool + dynamic rename combo to get those lines cleaned up. But how can I do that for a batch upload?

The batch upload by using wildcard reads all files at once and append them all into one big table so running the sampling tool + dynamic rename  after will only remove the first 27 rows for the first file in the final appended table. How can I clean up the rows as the files are imported?

Ideas?

Karina

[MarqueeCrew]

If there is a field that is reliably populated with values that are unmistakable, then you could use that knowledge and filter out header data.

Sent from my iPhone

[patrick_digan · Accepted solution]

@Korpelak You may be able to accomplish your task by outputting the file name on your input tool, grouping by the filename on the sample tool, and then grab the column names from the first row while eliminating the column headings from all your other files (assuming that all your files have the same column headings.) I've attached a quick sample. Let me know if that doesn't work for your particular situation! 

[Joe_Mako]

Does the Wildcard XLSX Input macro from http://www.chaosreignswithin.com/p/macros.html help with your situation?

[Korpelak]

Thanks Patrick! I did not need to use the other two sample tools you used in your example. The grouping by the filename in the first sampling tool did the trick.