automatically read the files from the folder & populate the data for the respective header

Dear frnds,
I am new to alteryx , much help will be appreciated.
Problem : I am attaching sample of my input files .. I have 100 Plus such files ..
format remain same for each file but headers differs. We can skip first 5 rows and make the 6 th line as header .
Desired output : I am attaching the sample output .
Since the header in all the files remains between jan to Dec , I would like to combine all the files into one by population the data for the respective header name (months)
I have 100 Plus files so I would like to automatically read the files from the folder .
Please help me to provide the solution for this .
Thank a lot

input(1)xlsx.xlsx

input(2).xlsx

input(3).xlsx

input(4).xlsx

Output(1).xlsx

Common Use Cases

Accepted answers

Joe_Mako

Those icons mean the workflow could not find the macro file, the .yxmc file, did you unzip both files into the same folder?

Does this package open for you?

Parse Input.yxzp

Joe_Mako

How about the attached?

Parse Input.yxzp

All comments

kat

Hi @Ekta

I built a little batch macro for you that can read in all the files for you. It was a bit of a pain getting all the headers to align as the dates came up in all sorts of formats. It might give some issues with your other files - then you just need to adjust the workflow for these scenarios.

Hope this helps!

Katrin

read files.yxzp

kat

I was having issues uploading the file - let me know if it doesn't work

read files.yxzp

estherb47

@Ekta, do you need the totals rows? You could always add a Summarize tool to give you those (having them right in the data isn't database format)

Cheers!

Esther

Joe_Mako

Attached is a workflow with supporting macro to read in a directory of XLSX files, with a known and consistent data structure, parse and reshape as requested.

Tool that will need to be adjusted:
Directory tool, point it to your directory

If the message tool errors because you have a new month format:
Formula tool with the caption "Fix date text format", you would need to change this if there was a new month format in the month header, currently it will parse "2017-0-01" and "Jan-2017" style formats. The message tool will Error if there is a new data format.

If your data structure changes:
Two filter tools with expressions like "[RowID]=7","[RowID]>7", you would change this your header structure changed, 7 is the final header row with the month headers
Text Input with known columns, if you had new columns or wanted different field names, also if you had more columns you would adjust the RecordID tool as well. 5 is the start of the month columns.

I noticed that one of your files was a duplicate, would you like duplicate data to be removed?

Load and Parse XLSX.zip

kat

Hi @Ekta!

Looks like something went super wrong when I packaged the workflow. Does the attached work?

You need to change 2 things:

1. In the batch macro - point the input tool at any one of your excel files

2. In the actual workflow - change to the folder containing your files

Remember to save the changed macro and to run the workflow.

macro macroworkflow

read files.yxzp

Ekta

Hi,

on running the work flow , i got this error, Why it i s so?

Am i doing anything wrong?

I am new to alteryx.

Please help

Thanks
On running the workflow

Joe_Mako

Those icons mean the workflow could not find the macro file, the .yxmc file, did you unzip both files into the same folder?

Does this package open for you?

Parse Input.yxzp

Ekta

Hi, Thanks a lot for the amazing workflow, i am facing an issue now in the macro - when i am running the workflow macro is giving me the error that- The 2017-10-10 windows security patch prevents the jet driver from reading .xls files; install the 2010 32 bit access driver.

what does this means?what i need to do ?

Please help

Best Regards

Ekta

@Joe_Mako

what does this means?what i need to do ?

Please help

Best Regards

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703