Export the data irrespective of file name standard but the sheet name will be same

[ShruthiVijayaKumar]

Hi Team,

There are scenario where the directory has multiple  excels with different file name and there are no standard but the sheet name to be written will be standard, wanted to know if we can write the data into a specific sheet irrespective of file name in alteryx.

will be very helpful for quick response.

Regards,

Shruthi.V

[davidskaife]

Hi @ShruthiVijayaKumar 

At a very basic level yes, irrespective of the file name you can input data into Alteryx from multiple excel files using a wildcard in place of the filename.

However, ALL of the files MUST have the same column names, data types, schema etc etc otherwise it will fail and you'll have to look at alternative options.

Have a look at this handy article that runs through the various options - https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

[davidskaife]

Hi @ShruthiVijayaKumar 

I've just re-read the original message and you're talking about outputting data into excel, not reading it in..

You can use a combination of a Directory tool and a Batch Macro to update multiple files in one go, where the filename will be different. I've attached my example to give you an understanding of how it works.

[ShruthiVijayaKumar]

Not able to understand the logic, can you please explain so that i will try to check the possibilities in my scenario.

[ShruthiVijayaKumar]

Hi David, Can you please explain briefly what the solution shared is being performed, which will be very useful for us?

Regards,

Shruthi.V

[davidskaife]

Hi @ShruthiVijayaKumar 

Apologies, must have missed the first message.

The workflow is doing the following:

Directory Tool - extracting from a location all Excel files, regardless of their name

Select Tool - restricting stream to just the FullPath of the files from the Directory Tool

Formula Tool - appending onto the FullPath the sheet name you want to define (using Sheet1 as the example here, replace this with the sheet name of your choice)

Macro

Using both your data and the list of excel files (including defined sheet name) as inputs this Batch Macro will cycle through all of the excel files and write the data (from the data input stream) to each excel file one by one)

So effectively what is happening is its compiling a list of all the existing excel files in the directory, appending a defined sheet name, then writing back to those same excel files the data, without needing to define the excel files themselves

Hope this helps