import and combine of xlsx file

KNIME Analytics Platform
#1

Hello,

Is somebody able to help me with an problem I face:

I want to import a list of .xlsx files and combine them to one table wich should be exported (as a csv file).

When I do this with importin csv files I'm using List of files -> Iterate List of files (wich includes TableRow To Variable Loop start -> Variable based file reader -> Loop End) -> CSV Writer

My fist idea was to simple change the import filetyp to xlsx in the Variable based file filter. This was not working. So my first question ist: Can I use Variable based file filter with xlsx files?

Since this was not working I tried to work with the XLS Reader. But there no Imput parameter is possible for importing a list of files. Is there any possibility to do so?

Last but not leaste if I import with the xlsReader how can I combine 2 files with the SAME colum names as the following example:

file1:

date        Value1            Value2
1.1.2013     1                    2
2.1.2013     1                    2

 

file2:

date        Value1            Value2
1.1.2013     3                    4
2.1.2013     3                    4

Result Table:

date        Value1            Value2
1.1.2013     1                    2
2.1.2013     1                    2
1.1.2013     3                    4
2.1.2013     3                    4

 

Is there any possibility to do so?

Since I'M trying KNIME first time in my life: Is tere a possibility to change the "code" of nodes like adding In- or output Ports?

Thanks,
Monika

#2

Hi Monika,

I think you are on the right track. The file names for the XLS Reader can be set in the Flow Variables tab of the configuration (there the XLS_LOCATION is what you need). (But also have to set an xls file name for the configuration.)

You do not need to concatenate the tables if you are using the Loop End node, else the Concatenate node is what you need.

You might need to check the developer section of this site to change the code. It describes how to get the sources, altough it is not recommended to modify the KNIME sources.

Cheers, gabor

#3

Take a look in the wiki: http://tech.knime.org/wiki/flow-variables

#4

Thank you for your help, I will try flow variables.

Thanks,

#5

Ok I now got it with the flow variables. Works fine if you know :)

I tried the following workflow (with flow variables as you suggested) and its working with smale files. (->workflow.png)

When I'm using big xlsx files (around 50MB per file) I'm facing the error below (error.png)

Do you know what are the boundaries of the XLSRead node?

Thanks again,

Monika

p.s. Gabor, where are you from? A friend of mine is calles Gabor and he's from hungaria.

#6

Hi Monika,

   Have you tried increasing the heap space for KNIME? It usually helps in these situations. (Changing the permgen settings is not necessary though, at least not till you install more nodes. :) )

   I think the boundaries are the same as the applied POI library. Usually it is only bound by the available memory.

Cheers, gabor

PS.: Yeah, I am from Hungary. Your name is similarly spelled in Hungarian too (Mónika).

#7

Thanks for you help again, I tried to increase the speed, but the files are too large. I will seperate my inputfiles.

Have a nice eve Gabor from Hungary :)

 

#8

sorry - did something wrong

#9

Hey!

I know the topic is a bit old, but I had the same problem and tried this solution. I was able to import all my data, but somehow there are no data types in the columns. Instead of Integer, Double or String there is a questionmark.

If I try column rename I can only change the types to string, so I would need another node to define the actual type. Also, I can only change the columns one by one. As the columns might change in the future, it would be helpfull to have the types changed automatically.

I also tried the column auto type cast node, but it doesn't find any of the columns I imported.