Import Disparate Files from a Directory and Union them Based on Column Position

Hello,

I am in need of creating a workflow that would be able to go to a file folder or directory and import all of the different file types (primarily Excel files and CSVs). Once imported, I need to be able to join all of these files into a single file. These files have the same content, however, the column names differ in nomenclature and position. I would need to be able to map them correctly based on position before creating a union file.

Your assistance is greatly appreciated!

Thank you!

I have attached four small sample data files for this exercise.

Area Sales Report.xlsx

Monthly Sales Tracker.csv

Regional Sales Register.xlsx

Monthly Sales Tracker 2.csv

Input

Macros

Preparation

Accepted answers

Yoshiro_Fujimori

I assume you want to keep the name and position of the first file.

Workflow

You need to cleanse each file to be aligned with the first file.

The column name and data type should be aligned, but you do not need to change the column positions, as Union Tool will do the work.

I hope it helps. Good luck.

@mmunozjr
If we want to be dynamic, I think we better use this macro.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Import-Multiple-sheets-in-from-one-Excel-file/td-p/840836

And since your Field Names and positions can be different, some manual adjust would be inevitable I think.

Multi Sheet Import Example 2-r1.yxzp

All comments

Yoshiro_Fujimori

I assume you want to keep the name and position of the first file.

Workflow

You need to cleanse each file to be aligned with the first file.

The column name and data type should be aligned, but you do not need to change the column positions, as Union Tool will do the work.

I hope it helps. Good luck.

@mmunozjr
If we want to be dynamic, I think we better use this macro.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Import-Multiple-sheets-in-from-one-Excel-file/td-p/840836

And since your Field Names and positions can be different, some manual adjust would be inevitable I think.

Multi Sheet Import Example 2-r1.yxzp

Thank you, Ace Qiu for your time and effort in assisting me with this solution.

I greatly appreciate it.

Regards,

Manuel

Thank you, Yoshiro for your time and effort in providing this solution.

I greatly appreciate it!

Regards,

Manuel

@mmunozjr
Thank you for the feedback and hope it helps.

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15867

MarqueeCrew 13708

apathetichell 13703