Data Extraction / Filtering

KNIME Analytics Platform
#1

Hello all,

I am quite new to KNIME and I kindly ask for your help.
At work, we create invoices in excel (don’t even get me started about that). So there is a folder where there are hundreds of excel files, each being an invoice. The layout is the same and it has been always like that. I am looking for a way to capture the important information from those invoices in a nice, organized table that would allow me to do a bit some analysis. Attached is a template and also a picture of how the data is spread allover when I read it in KNIME. I have no idea how to organize it in order to have it in a nice way…

Any help is highly appreciated.

Thanks a lot!!
knime_invoices|690x177
KNIME_model.xlsx (13.4 KB)

2 Likes
#2

Hi @sfer5 and welcome to the KNIME forum,

Please take a look at the attached Excel file and let me know if this is the output format you desire. Then I will send you the workflow. You can modify the Excel file and send me the new one.

example_output.xlsx (3.7 KB)

:blush:

3 Likes
#3

Hi @armingrudd

That would be perfect !
Thank you very much in advance!

#4

25394.knar (30.2 KB)

This is a component. Import it and then drag and drop it in your workflow. Then in the configuration window of the component enter the location where the raw Excel files exist.

:blush:

4 Likes
#5

I think it is almost there. When I tried on the real data, I got this error

error
error1340×970 65.2 KB

Any idea how to overcome this?
Thanks a lot! :slight_smile:

#6

If the structure of the files are exactly the same, there shouldn’t be any problems. But it seems your files are not the same. Can you share a few files including the one which encountered the issue?

1 Like
#7

All the files look like the one attached. I just changed the names with dummy ones for data privacy etc. But the structure is exactly the same for each file…
invoice123.xlsx (80.3 KB)

#8

Did a bit of digging, I think there is an issue when there are 2 or more files in the folder. It works for just one, but for some reason it doesn’t work when I have more files. The data looks like this…

knime2
knime2493×592 13.7 KB

Any idea?

Thanks :slight_smile:

#9

This situation should never happen, as the workflow instructs the Excel Reader node to read each Excel file one at a time.

Did you make any changes to its configuration?

3 Likes
#10

Dear @sfer5,

I forgot to update the variable settings in the List Files node after a change and probably this issue has made you to modify the component.

I just updated and tested the component on multiple copies of your example file. It works fine now.

25394.knar (30.2 KB)

Drag and drop the component into your workflow, double click on it and select the folder which contains the Excel files.

:blush:

3 Likes
#11

Hello!

Excel Reader (XLS) node can read multiple files if they have same structure so loop is not necessary. Haven’t looked data and manipulation in details so not sure if it can be applied in this case…

@sfer5 If you want to avoid creating invoices with Excel create a workflow in KNIME which does it for you. Supply workflow with input parameters, build logic to fill Invoice template, use Continental Nodes for KNIME to format it and output your Invoice in Excel :wink: Welcome to KNIME Community!

Br,
Ivan

2 Likes
#12

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.