Multiple different text files with multiple different schemas

I am quite new to Alteryx so still learning. I have a folder of text files with a corresponding flat file definition. I ultimately want to import them all and create one dataset as some of them can be stacked and matched with primary keys but initially I want to just import them to get the data parsed to export to an excel sheet.

I can setup the process to do one file at a time but I am just getting lost when trying to convert that into a batch macro to run through the full folder of files.

Does anyone have any good examples or steps to follow?

Input

Macros

Batch Macro

Accepted answers

All comments

aatalai

@Laura_M can you please upload some files (dummy are fine) and I will try and mock something up

JosephSerpis

Hi @Laura_M this article may be of some help.

Laura_M

Here's an example of the workflow I have created.
We have multiple text files and multiple flat file definitions so without a macro or iteration, we having to set them all up individually.

So we start with a folder with 90 text files and 90 flat files to correspond to provide the definition.

We can move / rename the text and flat files so they are all in one folder or provide a list and have them relational. eg Text file 1, flat file 1, text file 2, flat file 2.

I am just stuck on how we change the text file location / name and the flat file location / name to run through them all in one go rather than having to do them manually.

Output - we have put it in a csv so ideally we would like one csv per text file input or all sheets collated in one workbook.

Example Workflow.yxmd

Gaurav_Dhama_

If you are just importing the files, instead of jumping to batch macro, did you try with dynamic input tool?

Check out below snip where i am importing multiple flat files using dynamic input tool.

Dynamic Input tool configuration:

Laura_M

Hi Gaurav,

I did look at the dynamic input tool but the example says you need several files with identical schemas.
My text files have different schemas. So I need something where I specify both the text file path and the schema file path. Then run it multiple times

Gaurav_Dhama_

So for flat files, if you notice i am importing everything i to a single column, so the schema becomes same for all the files.
Once you have imported them along with file names, you can separate them as per your requirement and process.

Laura_M

Hi Gaurav,

I've followed this and managed to create a csv file with all my input text files with the dynamic input tool.
But i'm not understanding how the schema becomes the same for all files?
I still need to apply different flat file definitions to each line to split them into correct columns. And the data we are working with is not stackable. We will want to union the files eventually.

Thank you for your help on this.

Gaurav_Dhama_

Use this workflow, I built it to read the data from flat file dynamically, so it should be able to easily parse your data (may or may not require some modification).

Regarding the schema, now that is a part that remains same as what you would have done inside a batch macro. Because once you read the file in batch macro, you would essentially need same schema, or output them based on position, which can still be done at this point.

Dynamic Delimiter v2.yxzp

Gaurav_Dhama_

This was built for same schema, but can be modified for different schema files. Or you can use it as a macro to parse all the same schema files at once.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235