was trying to work with the excel to csv module and am getting a null pointer exception on two of the three files im trying to parse. they are all xls format. not sure the issue.
I created an assembly with the 3 file names in a note
excel error test - f3ca4f81db20408f8d64f08b6d50c11a
If you could take a look and let me knwo what is failing here. thanks
Hi Brent,
I see this error logged on the server:
Initializing WorkBookHandle failed.: WorkBook initialization failed: ‘Smallblock based workbooks are unsupported in ExtenXLS: see http://www.extentech.com/knowledgebase/KBDetail.jsp?article_id=195’
The module uses a 3rd-party library to read XLS files. Apparently 2 of the files are not supported by that library.
Hmm, looks like the company that made the library is no longer around, www.extentech.com is no longer working.
so im guessing that module isnt going to work anymore? i REALLY hope that isnt true. I was planning on using that this weekend on something for a client
If you can help direct me to another way to parse excel to xml I would be forever in your debt. I really need that like asap
The module will work, but can only read compatible XLS files. Otherwise your best option is to perhaps find an API service that can convert XLS files to XML or JSON. Or you can try to find an alternative Java or PHP library and I can install it on your server.
do we know why the two are not compatible? all 3 are from the same system so i would think they are all of the similar.
despite that, we know they wont work as is. Do you know of any api service that does that or library that would work? I just really dont know and am looking for as much help as i can get here. appreciate it
No idea regarding why that library can read one file but not the others. The error message says it all. That library is many years old.
Here is the first API service at the top of a google search: https://www.convertapi.com/
A couple of Java libraries are mentioned here: https://stackoverflow.com/questions/21351284/java-library-to-read-microsoft-excel-files#21351301
Don’t underestimate how hard it can be to use a java library to read xls files. Plus, java libraries may have dependencies that may not be compatible with the hundreds of other java libraries installed on your server.
Your best and easiest solution is to try an API service to do the conversion like the one I found above. Looks like you can try it out and see if it will work with your files. But if it doesn’t, then understand that whatever trouble is happening with reading the various files is due to their formatting and it is unlikely that a magical library is going to just read all formats effortlessly. Working with Microsoft files is notoriously difficult. Ideally try to have the data come from a CSV instead of native XLS…e.g. export from excel into a format suitable for processing.
oh i have tried already. they fought me tooth and nail that they couldnt get csv so this is what im stuck with. just frustrating that i tested original versions of each file a while back and now with new ones it doesnt work.
and yes. from all my googling it seems everyone uses apache poi but i had no idea how to get that into the system so i could use it.
If I wanted to use it, what would we need to do?
I’m not 100% sure, but I think I tried to use Apache POI and couldn’t get it to work many years ago when I stumbled upon the library currently being used.
Can try and see if I can install POI again tomorrow, but it may be one that has lots of dependencies and may not be compatible with all the other libraries the system uses. Apache libraries usually have lots of dependencies.
Why not take the easier path and try that API service? Looks like you can upload files in test mode from their website to check for compatibility.
mostly because of the cost and the client wont pay for it so we would have to eat it. i could write a novel on what the overall project has been like.
Will see if I can get POI installed tomorrow, but I’m pretty sure I have tried before without luck. Has been many years, so maybe things have changed. If not, will try to install docx4j. Will let you know tomorrow what happens.
really appreciate the help. let me know if there is anything i can do to help test or anything.
I remembered correctly, Apache POI cannot be installed b/c it has Java library conflicts.
I was able to install this library however: https://www.docx4java.org/trac/docx4j
Your server booted ok after installation. Hopefully it won’t have any silent conflicts. You may want to check high-priority automations to make sure they are still working ok.
You should now be able to use JSP code to access that library.
In case this will help, here is the code for the old library to convert XLS to XML:
<jsp:useBean id="appJSP" class="appServer.AppInterfaceModuleJSP" scope="page"/><%@page contentType="text/xml"%><%@page pageEncoding="UTF-8" import="java.io.*,java.util.*,java.net.*,com.extentech.formats.XLS.*,com.extentech.ExtenXLS.*"%><%
/**
* Copyright 2020 APIANT, Inc. All Rights Reserved.
*/
try
{
appJSP.registerJSP(Thread.currentThread());
if (request.getParameter("url") != null)
{
WorkBookHandle book = new WorkBookHandle(new URL(request.getParameter("url")).openConnection().getInputStream());
ExtenXLS xls = new ExtenXLS();
String xml = xls.getXML(book);
int pos = xml.indexOf("<?");
if (pos != -1)
{
pos = xml.indexOf("?>", pos);
xml = xml.substring(pos+2);
pos = xml.indexOf("<?");
}
out.println(xml);
}
else
{
out.println("<root><SystemException>Missing 'url' parameter.</SystemException></root>");
}
}
catch (Exception e)
{
out.println("<root><SystemException><![CDATA["+e.toString()+"]]></SystemException></root>");
}
finally
{
appJSP.unregisterJSP(Thread.currentThread());
}
%>Thanks for getting that in. I am looking through the docs and not seeing anything about xls, only xlsx. did you see it somewhere and im just missing it or do you know how to handle that?
Sorry, I don’t know.
Thanks for the help. I ended up using a service to convert but it was free if under 500 calls a month so i can fit in there.
Can you share which service you used, in case other customers hit the same problem?
cloudmersive.com. they have a lot of different conversion apis for different file types. very simple call and works well.