Importing multiple excel files with headers

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

Input

Dynamic Processing

Preparation

Accepted answers

patrick_digan

@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!

Skip first N rows.yxzp

All comments

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

Skip first N rows.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703