Tutorials & Examples Web-help Blog

Best practice to consolidate x excel Sheet


#1

Hello,

I’ve got an Excel file with 36 same-formated sheet (1 for each month for 3 years)

I want to happend the 36 tables.
Should I import the 36 sheets + happend transformation ?
Is there an other possibility ?

Best Regards / Bien cordialement,
Michel


#2

Hi Michel,

In the “Import from Excel” transformation the sheet can be specified using a parameter. Create a project (A) that imports one particular sheet which name is defined by a parameter.

Then there is “Spreadsheet metadata” transformation (button “Insert table” -> category “File”) – it extracts all sheet names from a spreadsheet into a list. Create another project (B) with this transformation and get a list of 36 sheet names. Then use “Iterate” transformation on this list to run project A for each sheet name.

Here is an example that does it:

Book1.xlsx (9.6 KB)
Load All Sheets.morph (1.5 KB)
Load One Sheet.morph (1.3 KB)

UPDATE 7/13/2017
Files and screenshots were are updated with correct ones. As @michel.baldellon noticed, in the original ones there was a mistake – sheet name was hardcoded instead of using a parameter.


#3

i thought it is similar question. I have lists of 10 csv files with 50 rows each. I have created one csv file and transform it to show only the last row. I imported the 10 list of files. When I iterate with the transformed file expecting to show me only the last row of each file (10), instead I get all rows of 500. Where do I mess up or what do I miss? Eyasu


#4

Hi @eyasu,

please create new topics for your questions. To create a new topic just press “+New Topic” button in the upper right corner of the home page. Iterations is a big subject. Your question, while related to iterations, is not related to the subject of this topic which is consolidating sheets of a spreadsheet, not multiple csv files. I will answer it here, but next time please create a new topic, instead of posting into existing ones. This would also make it easier for other people to find relevant answers.

If you’re expecting to see only the last row in each file, then the iterated project should return only 1 last row for file, not entire file. For this, you can use the “Trim table” transformation to keep only 1 row at the bottom. If you have multiple tables in the iterated project, make sure that in the “Iterate” transformation you’re indicating the table with 1 last row as the returned result, not some other table.


#5

Hi Dmitry,
I tried this and I don’t understand.
Look, all the figures are the same (AAA is always 4 and BBB always 79 whatever the Sheet name.)
Don’t you think there is a problem

Best,
Michel


#6

Hi Michel,

you’re totally correct – I uploaded wrong version of the example, with sheet name hardcoded in the iterated subproject. It should use a parameter, of course. I’ve updated the files and screenshot in the original comment.


#7

Hi,

I’m not sure to fully understand what you have changed between the 2 versions…
If I use your new files, it’s OK.
But if I want do replicate that from scratch… I fail and I load each time the first sheet!
Like if the parameter was “harcoded”…

Regards,
Michel


#8

In the child project (Load One Sheet) the sheet name should be specified using the only parameter of that project (“Sheet name”). Because the parameter is assigned by the parent project (Load All Sheets) in iterations, it makes the child project to read every time another sheet.

My mistake was that the sheet name wasn’t defined using the parameter. It was always the same name, therefore in every iteration it read the same sheet.


#10

Thank’s Dmitry.
I didn’t realize that the import in the “Load one file” was with a parameter :

Because it’s a really great solution, if it could be packaged in a single transformation for a future release of EM…

Michel


Single transformation to import multiple Excel sheets
#11

I know it’s been a long time since this was active, but following last comment from Michael Baldellon, an option could be to add function to load info from list of files, like it is for importing excel spreadsheets.

image

– Would be great to have the same option here:
image