Working with xls Files

, ,
1

Has anyone has success working with Excel document (native format, non-csv)

I have downloaded the tool (in downloads sections) but have been fairly unsuccessful.

Any advice would be appreciated!

2

Yes. I wasn’t able to get the downloaded package from Igor working, though in all fairness to Igor I didn’t try to figure out what the issue was. Is there something specific that you’re experiencing that someone here (perhaps Igor) could help resolve?

I used the same POI/HSSF library as Igor used but I ended up implementing things using the event model API. I was toying around with uploading it soon and will do so as soon as I can.

3

Yes, I got it working, but it had problems with blank cells. As I recall it was the getPhysicalNumber of cells call returning only the count of non-empty cells. I only tried cells with strings.

4

The EXCEL package I uploaded does work on most Excel file however I discovered that there are some Excel files that are not possible to read with Apache POI inetrface. In many cases it was a matter of open the file in Office 2000 Execl adn resave it then POI was able to read it. I did some research into this matter with Apache and debuging it myself I found that there some Excel file created that do not confirm to latest Microsoft specifications No surprise from MS. If you experiance a problem with my package EXCELContentHandler that can be one of the reasons. The blank cell comment is well placedit is an issue with getPhysicalNumber of cells method. However I have made several update to this package and was able to avoid most of the problems using different POI APIs. Still the format problem there is no way around this POI fails on some Excel files but Most of the time resaving or removing first header text row can correct original file format and POI works fine.

5

Hello,

Is there any way to create a Excel file from the data that i query form TN Database?

Any Help will be greatly appreciated

thanks
Jay

6

After the query just go to the Transactions/Export Query Results (on the top) and save it as " *.csv " file. This csv (comma separated value) file could then open in Excel.

7

Hi ,

Itemfield’s ContentMaster is fully equipped to parse/serialize any binary/textual format (one of which is MS Excel) into any other format. We have vast experience working with all office documents. ContentMaster can easily read and create a REAL native format excel document.

Itemfield is a new webMethods partner. Its unique “Example-Based Parsing” technology offers an intuitive graphical development environment for easy creation of parser scripts.

ContentMaster is easily integrated into WM. See [url=“http://www.itemfield.com/solutions/sol_webmeth.shtml”]http://www.itemfield.com/solutions/sol_webmeth.shtml[/url] for more details.

Please feel free to contact me with any questions.

Meitav Harpaz

8

Derek,

Which version of webMethods IS are you using?

If you are using IS6.01,have you tried adding the xls extension in
IntegrationServer\lib\mime.types file.Syntax the following
(application/x-wmflatfile dat csv xls)

HTH,

9

Did you bounce the server after adding the zip? You should see this entry in the classpath when you go to the IS admin and click the ‘About’ link.

Will

10

Hello RMG,

We are running IS 6.1. I tried adding that entry to the mime.types file and restarted the IS, and I am still having a problem. I should probably have expanded on what was occurring before.

I receive the file in with a contentStream object defined as input (also tried ffdata object), and then proceed to do a streamToBytes, then a bytesToString, and then a savePipelineToFile. When restoring the pipeline, I see the bytes array exists, and the string contains data. In the middle of the string I see my fields that existed in the Excel input file, however it is surrounded with extraneous data and symbols, which I am guessing is cell formatting, etc. I would imagine the amount of this “garbage” around my data would change with every input file, so I don’t feel it is reasonable to do a substring to a certain point.

I may be completely on the wrong track as far as taking in this file, so please feel free to point me in the right direction.

Hello Will,

I did recycle the IS after adding that .zip, and do see the entry in the IS\lib\jars classpath.

Thanks for the help from the both of you. Please let me know if you hav any other suggestions, or if I am missing something.

Thanks,
Derek

11

The excel spreadsheet file (.xls) is a binary file. You can’t convert it to a string and expect to see normal characters. It looks like from previous posts that the special excel package converts the xls file to an IData record for you to use.

I haven’t used that custom wm package mentioned in previous posts, but it looks like you would convert the file to the IData record using their flow services, then map from the IData record to get the values you want.

12

I suspected that was the case, which is why I had tried loading the Excel_contentHandler package. Thanks Will for getting me off of that dead-end path.

Unfortunately, as explained in my original post, I am still receiving those errors when trying to load this package.

Has anyone seen those errors when loading this package from Igor, or know more specifics about the .jar file and path required for using the package?

Thanks

13

Iandrosov,

THe POI jar files, where did you place the file? Is it /IntegrationServer/lib/jars path? Did you need to do anything after that in order for IS to find the library files?

14

Can you upload the utility with the blank cell issue fixed. Thanks in advance.

15

Dears,
please i have a problem to get the suitable inputs for the service “ExcelToXML” in the contentHandler package. i cant figure what inputs to use for the following fields:

file_stream
file_data

please advice :o