# import and combine of xlsx file

**URL:** <https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068>\
**Category:** KNIME Analytics Platform\
**Created:** [September 30, 2013, 12:29pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068 "2013-09-30T12:29:44Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Monika](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/f05b48/32.png) [@Monika](https://forum.knime.com/u/Monika)\
**Post date:** [September 30, 2013, 12:29pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/1 "2013-09-30T12:29:44Z")

</div>

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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value2  
1.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2  
2.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2

&nbsp;

file2:

date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value2  
1.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4  
2.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4

Result Table:

date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value2  
1.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2  
2.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2  
1.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4  
2.1.2013&nbsp;&nbsp;&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4

&nbsp;

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

---

<div class="post-metadata">

**Author:** ![aborg](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/c89c15/32.png) [@aborg](https://forum.knime.com/u/aborg)\
**Post date:** [September 30, 2013, 5:54pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/2 "2013-09-30T17:54:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Iris](https://forum-cdn.knime.com/user_avatar/forum.knime.com/iris/32/14_2.png) [@Iris](https://forum.knime.com/u/Iris)\
**Post date:** [September 30, 2013, 6:19pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/3 "2013-09-30T18:19:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Monika](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/f05b48/32.png) [@Monika](https://forum.knime.com/u/Monika)\
**Post date:** [October 1, 2013, 1:12pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/4 "2013-10-01T13:12:11Z")

</div>

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

Thanks,

---

<div class="post-metadata">

**Author:** ![Monika](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/f05b48/32.png) [@Monika](https://forum.knime.com/u/Monika)\
**Post date:** [October 1, 2013, 2:07pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/5 "2013-10-01T14:07:56Z")

</div>

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.

- [workflow.png](https://www.knime.com/sites/default/files/workflow_6.png)
- [error.png](https://www.knime.com/sites/default/files/error_0.png)

---

<div class="post-metadata">

**Author:** ![aborg](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/c89c15/32.png) [@aborg](https://forum.knime.com/u/aborg)\
**Post date:** [October 1, 2013, 3:07pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/6 "2013-10-01T15:07:06Z")

</div>

Hi Monika,

&nbsp;&nbsp; Have you tried [increasing the heap space](https://forum.knime.com/faq#q4_2) for KNIME? It usually helps in these situations. (Changing the permgen settings is not necessary though, at least not till you install more nodes. :) )

&nbsp;&nbsp; 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).

---

<div class="post-metadata">

**Author:** ![Monika](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/f05b48/32.png) [@Monika](https://forum.knime.com/u/Monika)\
**Post date:** [October 1, 2013, 6:06pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/7 "2013-10-01T18:06:13Z")

</div>

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 :)

&nbsp;

---

<div class="post-metadata">

**Author:** ![gverhey4](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/g/3ab097/32.png) [@gverhey4](https://forum.knime.com/u/gverhey4)\
**Post date:** [October 10, 2013, 9:06am UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/8 "2013-10-10T09:06:42Z")

</div>

sorry - did something wrong

---

<div class="post-metadata">

**Author:** ![Sabi\_Ba](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/s/48db29/32.png) [@Sabi\_Ba](https://forum.knime.com/u/Sabi_Ba)\
**Post date:** [October 5, 2016, 3:26pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/9 "2016-10-05T15:26:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [June 23, 2023, 8:18pm UTC](https://forum.knime.com/t/import-and-combine-of-xlsx-file/3068/10 "2023-06-23T20:18:43Z")

</div>


