Merge all excel sheets in a file to one

Excel sheet merging in to one

Hi @sujesh

I’m assuming your post got cut-off half way but if not, can you please amend your post in accordance with the guidelines on how to write a post?

That will increase the chances of you getting some support.

3 Likes

Thank you @ArjenEX for your reply to how to post, It will not solve the issue faced by me, I searched and it also there in sample work flows , it generating error, I am a Accountant, When I used knime for report generation it easy and user friendly , So I posted ,Hopes some one will understand and help me.

Hi @sujesh , what @ArjenEX is saying is that if you are going to post what we assume is supposed to be a question, then please tell us what it is you want to know. The above link is not designed to answer your question (how can it…? You still haven’t actually asked one!). It is designed to help you tell us what you need.

So please help us to help you.

If this is a request for assistance, it would be better to formulate a question, and if you are having specific problems to give an idea of what you have tried so far. It is best if you can post an example workflow exhibiting the problem.

You now say it is generating errors… Well? What errors?

If I google:
knime merge sheets
or
knime read multiple excel sheets

I get quite a number of hits including forum threads and example video links, so we would assume that you have done the same. Therefore please tell us what problem you are facing, and we will gladly help but none of us is going to write an entire tutorial on the off chance that it might cover the one thing you are possibly doing wrong.

3 Likes

Thank you @takbb explaining it, the error is attached with this reply,


now this message is showed, where as earlier it will merge but the generated sheet contains only single sheet appears several time

Hi @sujesh,

I feel that you don’t like to give too much away in any one go :wink:

So from this screenshot I can tell you what your problem - you have misconfigured it.

I can take a guess, but that will take me time to think through all the possible ways you might have misconfigured it. So if you give more info*, I will probably be able to tell you how… :slight_smile:

* e.g. wider screenshot with the whole loop + screenshots of the node configurations or upload a demo workflow

btw, For what I am guessing you are trying to do (read all the sheets from an Excel spreadsheet and combine them into a single sheet, assuming that all sheets have the same columns ? ), I wouldn’t generally put the Excel Writer inside the loop. I’d be more likely to adopt this pattern:

Of course that maybe isn’t exactly what you are trying to do because… you still haven’t told us.

Thank you very much @takbb for the quick reply and pointing out the error. unfortunately same error occurring now also. please find the



screen shot attached

Hi @sujesh -

Instead of screenshots, please export your workflow and upload the workflow to the forum directly, along with your sample data (assuming it’s not confidential).

Workflows are 10x more diagnosable than screenshots :slight_smile:

3 Likes

Hi @sujesh,

As @scottF rightly says, uploading your workflow would greatly assist in helping you because then we can actually see what the issue with the configuration is.

BUT the much bigger problem that I have is that I am now writing the 9th post on this thread and you still haven’t told us what you are wanting to do!

We know you get an error. The error is caused because you haven’t got a SheetName flow variable defined anywhere, and your Excel Writer has been configured to use a variable by that name.

Your screenshots reference @ipazin’s workflow on the hub, but the workflow you have in your screenshots is not as it is on the hub. I know you have moved the Excel Writer because of what I said, but what I said was based on a different assumption of what you were trying to do.

The original on the hub was this:

Original from Hub

I can see exactly why the Excel Writer was inside the loop for this job. It is right for the specific job it was designed to perform. i.e. Read several XLSX files and consolidate them into a single workbook with each sheet being named from the source filename.

So the hub workflow works. but you’ve modified it and now it doesn’t work, because you removed the node (String Manipulation) that creates the “SheetName” flow variable.

If you modified the hub workflow, I can only presume it doesn’t do exactly what you want, so again, please tell us what that is.

@sujesh what you could do beides explaining what you want to do with your KNIME and Excel workflow would be to check out some examples an resources about KNIME and Excel.

I have a large collection with some typical scenarios at the bottom you might want to try them:

Also there is a book (From Excel to KNIME | KNIME) and a video (First Steps into KNIME Analytics Platforms for Excel Users - YouTube) you might want to explore.

Another thing I often use is the DeepL translation website (DeepL Translate: The world's most accurate translator). The translation often is much better than google so it is easier to just write in one’s language and then press translate.

1 Like

Thank you @mlauber71 , I able to do basic things. I found difficulty in generating the output. definitely I shall go through videos for enhancing and to get affirmation on Knime knowledge.

Thank you @ScottF , my belated reply due to non availability of speedy network, I will upload workflow, Data c
sim.xlsx (28.1 KB)
Consolidate_Multiple_Excel_Files_into_One 1.knwf (55.7 KB)
onfidentiality is there but will upload it.

Thank you very much @takbb for spending time for this, I made work flow through a reference from web only, How to Combine Multiple Excel Sheets Using Knime - Analytic Iron, I followed this.

1 Like

Hi all. @ScottF could you able to find error occurs which node.

@takbb that work flow I mention generates only one sheet multiple times

@sujesh you could take a look at this workflow. It takes the data fro the line 9 of each sheet since they seem to contain the data (if not please change). You might delete lines that are unwanted:

kn_forum_51793_consolidate_multiple_excel_files_into_one.knwf (55.3 KB)

If you want to do more complicated imports of blocks of data you might have to take a look here:

1 Like

Thank you @mlauber71 this worked , Looking at this I under stood that you worked on it to get the result, Since data is confidential one I tried it on the actual one unfortunately it showed error as seen in the attached form

@sujesh it means your data structure is different between the sheets you want to import. You might have to adapt that or allow a change of data structures in the Loop End and deal with the problems later maybe try the: Column Auto Type Cast – KNIME Community Hub

image

1 Like