topic Re: Unable to centralize Excel Metadata in Talend Studio

Unable to centralize Excel Metadata

Anonymous — Thu, 04 Jul 2019 16:11:05 GMT

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

Re: Unable to centralize Excel Metadata

Anonymous — Thu, 04 Jul 2019 19:36:53 GMT

Further investigations.

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

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?

Re: Unable to centralize Excel Metadata

Anonymous — Thu, 04 Jul 2019 20:30:31 GMT

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 🙂

Re: Unable to centralize Excel Metadata

Anonymous — Thu, 04 Jul 2019 22:19:26 GMT

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.

Re: Unable to centralize Excel Metadata

Anonymous — Thu, 04 Jul 2019 23:10:48 GMT

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 🙂

Re: Unable to centralize Excel Metadata

Anonymous — Wed, 10 Jul 2019 08:33:07 GMT

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