topic Re: change data format for multiples files in Alteryx Designer Desktop Discussions

change data format for multiples files

yc151 — Tue, 22 Feb 2022 18:54:13 GMT

hi, i have a quick question about change data format for multiples files. Now I have many excel files that have same data format(say one file for each car brand, the only difference is different files have different number of rows). I just need to do some transpose/cross/subtotal functions. I already built a workflow to do it for one file. Is there any way to have alteryx do it for all the files I have? Thanks.

Re: change data format for multiples files

AngelosPachis — Tue, 22 Feb 2022 19:44:19 GMT

Hi @yc151 ,

Yes, you can create a batch macro to read each one of those files, do the transpose/crosstab/summarize functions (that should be consistent across all files) and then write the output to the same filepath

Re: change data format for multiples files

yc151 — Tue, 22 Feb 2022 19:58:07 GMT

Thank you @AngelosPachis , can you share how to write a such macro? or similar macro?

Re: change data format for multiples files

AngelosPachis — Tue, 22 Feb 2022 20:15:31 GMT

Hi @yc151 ,

Here's an example. I would suggest you also spend some time to go through the macros Interactive lessons if you haven't done that already.

Let me know if you have any further questions.

Cheers,

Angelos

Re: change data format for multiples files

yc151 — Tue, 22 Feb 2022 21:16:31 GMT

hi, @AngelosPachis

Thank you for the sample. I opened your macro and replaced my series of tools to your transpose tool. I linked to the file path has my many input files. In the macro, I relinked to one of my input files and relinked to output files, and run into a error message, can you look?

Re: change data format for multiples files

AngelosPachis — Tue, 22 Feb 2022 21:24:54 GMT

@yc151 can you try reconfiguring your output tool so you select a sheet to write to? It seems this is causing the issue

Re: change data format for multiples files

yc151 — Tue, 22 Feb 2022 22:47:19 GMT

hello, @AngelosPachis I have finally be able to finish these! thank you, it's helpful! i will learn more macro when i have time.

Re: change data format for multiples files

ArtApa — Tue, 22 Feb 2022 22:26:58 GMT

Hi @yc151 - A batch macro can be a good solution for you. You may learn about types of macros in Alteryx and how to design a macro here: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

However, there is a chance that a Dynamic Input will also do a job for you. You can potentially (must be the same schema) read all your files process them and then Output to one or many files.

a) Please review Dynamic Input sample:

b) Please review Output Data sample: