Combine Excel and Rename Sheets

[KamenRider]

Hi and Good day,

I would like to ask for your knowledge and assistance regarding the following.

I have multiple excel files (.xlsx) and would like to merge/combine into one. Excel files has different sheet names and I would like to name it as Sheet1 with date today when combined.

Attaching two excel files for your reference.

Looking forward for your help. Thanks.

Kamen

[KamenRider]

HI @binuacs 

Thank you for your guidance. I was able to run the workflow. Just to confirm, the first macro is Get Sheet Macro wherein I will select "Full Path (V String) and second one is Get Sheets Details and will select "Full Path (V String) also.

The problem is that it read only the first date in my directory plus it produced two sheets in one file. See screenshots below.

 

 

Is there's something we could do to correct the workflow. The workflow should read the files in the directory under one sheet. No duplicates.

Hoping for your continued assistance.

Thanks

Kamen

[binuacs]

@KamenRider The macro is designed to read all the sheets from the given excel files and put in each sheet one excel file. Do you want to ready only from the first sheets of each excel file and put it all in one excel file with different sheets?

[KamenRider]

Hi @binuacs 

Thanks for the response. I would like to request for Sheet1 to combine all the contents of the excel files. I am not sure  why the workflow does not get all the files. It show only date 07-19 plus with duplicates. I have change the connection of the file pointing to the directory of my input files in the Get Sheets Macro because the workflow won't run because of the error stating the file does not exists.

Please advise.

Kamen

[grazitti_sapna]

@KamenRider, could you please share the workflow that you are using to implement my workflow, along with the files in the directory?

Thanks!

[binuacs]

@KamenRider I updated the workflow to combine all the data into one sheet with the name sheet1

[nitin_l]

Just putting this workflow here, if anyone wants to merge few excel files into one with a dynamic sheet name.

[KamenRider]

Hi @nitin_l 

Thank you for sharing your ideas however, my input files does not only limit to 2. It may have two or more reports to merge. Please feel free to still share your ideas.

[grazitti_sapna]

@KamenRider, updated the workflow. I ran it and I am not able to find out any dupes one of the reasons could be you might be storing the file in the same place as your files stored in the same directory. Could you please try storing the output file at a separate location?

I hope this helps!

Thanks!

[KamenRider]

Hi @binuacs 

The results in still the same except that it shows only one sheet which is correct but the content still looks like this. I've change the path or connection of the input file in Get_Sheets_Macro to ..//BSA Input/BSA 01.xls. The workflow does not still read the 4 files but read only the first file BSA 01.

Can you please use the 3 excels below. It shows the correct schema or columns. I'm not sure if this is the reason why the data is screwed. I've also attached the sample out. On this first column I've added a date of the report which I take from the sheet name.

Looking forwards. Thanks so much.

Kamen

 

[KamenRider]

HI @grazitti_sapna 

I don't have Alteryx at home but here are another files I would like you to use. This consist of the correct number of columns. I added a date on the first column which is taken from the sheet name. Please see attachments together with the output I would like to look at.

Thanks and hoping to hear from your assistance.

Kamen