Unable to centralize Excel Metadata

[Anonymous]

Hi all,

I have an excel file which is approx. 40MBs, however there is a lot of columns in it (up to the DM). When I try to add this excel file into the metadata, it just doesn't work. First time I had some problems with memory so I changed some things in my *.ini file to:

-Xms256m
-Xmx2048m
-XX:MaxPermSize=256m

So first problem was solved. Now when I try to add excel file I get this:

I have no idea how to avoid this, however when I manually create Excel input data in the job area and manully define the schema it works:

The problem is, right now I have no idea which columns I will need and to define the whole schema manually for all the columns is simply not feasible.

Could you please give me some hints on what I'm doing wrong?

Some additional Details:

W10 64bit

e:\>java -version
java version "1.8.0_212"
Java(TM) SE Runtime Environment (build 1.8.0_212-b10)
Java HotSpot(TM) 64-Bit Server VM (build 25.212-b10, mixed mode)

Open Sudio for Data Integration 7.21

[Anonymous]

Further investigations.

1)

I have tried to remove all non-necessary sheets from excel file and even removed 99% of rows from the sheet which I need. Idea behind this is that I just need the metadata for the schema. Once it is done I could just replace the file on the input with the big one.

Result: same error

2)

I converted file to version 2003 and now it works even when the file grow in size like 2 times.

So it seems like the issue is related to newer excel format. Any thoughs?

[Anonymous]

Hi,

     Could you please share the excel sheet after removing any confidential information?

     It will help the members to do detailed analysis.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

I need to check with the colleagues at work if I can share it (even anonymized). However this will be done on monday (soonest possible day for me). Don't want to get to any touble.

In the meantime what I did was to convert file to XLS and then back to XLSX. New file is slightly smaller. It works this way, so it might be something with the original file. 

[Anonymous]

Hi,

    Lets wait for the sample file and we will take it forward from there.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Unfortunatelly I did not get approval for uploading that excel file. Even anonymized. So I guees I will have to go with option xlsx => xls => xlsx