[Solved] Weird .XLS file that is treated as text

rah · Post by **rah** » Mon Nov 18, 2013 10:22 pm

We're getting an .XLS file from a customer that Calc v4.0.1 can't handle. When I try to open it in Calc, it's treated as a text file for import. If you accept import, Calc errors out.

I've tried opening the file in Excel 2003 and 2010 and it opens successfully but if I try to open it in the latest version of MS Excel Viewer, it errors out saying it can't open files of this type.

Below is the top portion of this file and it opens as readable code in a text editor. A normal .XLS file is binary and displays as gobble-de-gook in a text editor.

I'm guessing this is some kind of MS scripting language that isn't really an .XLS file but creates one when it loaded into Excel.

Is there any way to get Calc to handle it?

Thanks.

Code: Select all

MIME-Version: 1.0
X-Document-Type: Worksheet
Content-Type: multipart/related; boundary="----=_NextPart_01C5084F.FEF9A7A0"

------=_NextPart_01C5084F.FEF9A7A0
Content-Location: file:///C:/Mappe1.htm
Content-Transfer-Encoding: text/html
Content-Type: text/html; charset="utf-8"

<html xmlns="http://www.w3.org/TR/REC-html40" xmlns:alv="http://www.sap.com/ALV/2.6" xmlns:asx="http://www.sap.com/abapxml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:x="urn:schemas-microsoft-com:office:excel"><head><meta http-equiv="Content-Type" content="text/html;&#xA;        charset=utf-8"/><meta name="ProgId" content="Excel.Sheet"/><meta name="Generator" content="Microsoft Excel 9"/><link rel="File-List" href="./WbGui_XML_files/filelist.xml"/><link rel="Edit-Time-Data" href="./WbGui_XML_files/editdata.mso"/><link rel="OLE-Object-Data" href="./WbGui_XML_files/oledata.mso"/><!--[if gte mso 9]><xml><o:DocumentProperties/><o:OfficeDocumentSettings><o:DownloadComponents/><o:LocationOfComponents HRef="file:msowc.cab"/></o:OfficeDocumentSettings></xml><![endif]--><style><!--
        table
               {mso-displayed-decimal-separator:"\.";
               mso-displayed-thousand-separator:"\.";}
        @page
               {margin:1.0in .75in 1.0in .75in;
               mso-header-margin:.5in;
               mso-footer-margin:.5in;}
        tr
               {mso-height-source:auto;}
        col
               {mso-width-source:auto;}
        br
               {mso-data-placement:same-cell;}
        .style0
               {mso-number-format:General;
               text-align:general;
               vertical-align:bottom;
               white-space:nowrap;
               mso-rotate:0;
               mso-background-source:auto;
               mso-pattern:auto;
               color:windowtext;
               font-size:10.0pt;
               font-weight:400;
               font-style:normal;
               text-decoration:none;
               font-family:Arial;
               mso-generic-font-family:auto;
               mso-font-charset:0;
               border:none;
               mso-protection:locked visible;
               mso-style-name:Normal;
               mso-style-id:0;}
        td
               {mso-style-parent:style0;
               padding-top:1px;
               padding-right:1px;
               padding-left:1px;
               mso-ignore:padding;
               color:windowtext;
               font-size:10.0pt;
               font-weight:400;
               font-style:normal;
               text-decoration:none;
               font-family:Arial;
               mso-generic-font-family:auto;
               mso-font-charset:0;
               mso-number-format:General;
               text-align:general;
               vertical-align:bottom;
               border:none;
               mso-background-source:auto;
               mso-pattern:auto;
               mso-protection:locked visible;
               white-space:nowrap;
               mso-rotate:0;}

        .x124
                {mso-style-parent:style0;}
        .x125
                {mso-style-parent:style0;
                mso-number-format:"Long Time";
                text-align:right;}
        .x126
                {mso-style-parent:style0;
                mso-number-format:"Short Date";
                text-align:right;}
        .x127
                {mso-style-parent:style0;
                mso-number-format:0;
                text-align:right;}
        .x128
                {mso-style-parent:style0;
                mso-number-format:"0\.0";
                text-align:right;}
        .x129
                {mso-style-parent:style0;
                mso-number-format:"0\.00";
                text-align:right;}
        .x130
                {mso-style-parent:style0;
                mso-number-format:"0\.000";
                text-align:right;}
        .x131
                {mso-style-parent:style0;
                mso-number-format:"0\.0000";
                text-align:right;}
        .x132
                {mso-style-parent:style0;
                mso-number-format:"0\.00000";
                text-align:right;}
        .x133
                {mso-style-parent:style0;
                mso-number-format:"0\.000000";
                text-align:right;}
        .x134
                {mso-style-parent:style0;
                mso-number-format:"0\.0000000";
                text-align:right;}
        .x135
                {mso-style-parent:style0;
                mso-number-format:"0\.00000000";
                text-align:right;}
        .x136
                {mso-style-parent:style0;
                mso-number-format:"0\.000000000";
                text-align:right;}
        .x137
                {mso-style-parent:style0;
                mso-number-format:"0";
                text-align:right;}
        .x138
                {mso-style-parent:style0;}
        .x139
                {mso-style-parent:style0;}
        .x140
                {mso-style-parent:style0;
                mso-number-format:"0%";
                text-align:right;}
        .x141
                {mso-style-parent:style0;
                mso-number-format:"0\.0%";
                text-align:right;}
        .x142
                {mso-style-parent:style0;
                mso-number-format:"0\.00%";
                text-align:right;}
        .x143
                {mso-style-parent:style0;
                mso-number-format:"0\.000%";
                text-align:right;}
        .x144
                {mso-style-parent:style0;
                mso-number-format:"0\.0000%";
                text-align:right;}
        .x145
                {mso-style-parent:style0;
                mso-number-format:"0\.00000%";
                text-align:right;}
        .x146
                {mso-style-parent:style0;
                mso-number-format:"0\.000000%";
                text-align:right;}
        .x147
                {mso-style-parent:style0;
                mso-number-format:"0\.0000000%";
                text-align:right;}
        .x148
                {mso-style-parent:style0;
                mso-number-format:"0\.00000000%";
                text-align:right;}
        .x149
                {mso-style-parent:style0;
                mso-number-format:"0\.000000000%";
                text-align:right;}
        .x150
                {mso-style-parent:style0;
                mso-number-format:"\[$-F400\]h\:mm\:ss\\ AM\/PM";}
        
        .xl51
                {mso-style-parent:style0;
                mso-number-format:"d\/m\/yy\\ h\:mm\;\@";}
    --></style><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>Sheet1</x:Name><x:WorksheetOptions><x:DefaultColWidth>10</x:DefaultColWidth><x:Print><x:ValidPrinterInfo/><x:PaperSizeIndex>9</x:PaperSizeIndex><x:HorizontalResolution>600</x:HorizontalResolution><x:VerticalResolution>0</x:VerticalResolution></x:Print><x:Selected/><x:Panes><x:Pane><x:Number>3</x:Number><x:ActiveRow>1</x:ActiveRow><x:ActiveCol>0</x:ActiveCol></x:Pane></x:Panes><x:ProtectContents>False</x:ProtectContents><x:ProtectO

edit this topic's initial post · Post by **Villeroy** » Mon Nov 18, 2013 10:43 pm

All types of files are distributed with an .xls suffix assuming that "everybody" has MS Excel installed. This one seems to be a special Excel flavour of HTML. Your browser should be able to give a human readable representation of it.

rah · Post by **rah** » Mon Nov 18, 2013 11:14 pm

I can open this file in a text editor and I can read it. The problem is, there's a spreadsheet in there somewhere and I want to extract it as a spreadsheet.

I was hoping if something as old as Excel 2003 can open this file, Calc would be able to as well.

Post by **RoryOF** » Mon Nov 18, 2013 11:21 pm

Excel Compatible HTML, circa 2005
see
http://codesnipers.com/?q=excel-compatible-html

rah · Post by **rah** » Mon Nov 18, 2013 11:33 pm

So reading in HTML code is something Excel has done since 2003 but it's not a feature that Calc incorporated?

Greengiant224 · Post by **Greengiant224** » Mon Nov 18, 2013 11:35 pm

Yes, It's an xml type html file. A precursor to the proprietary M$ .xlsx files that are now prevalent everywhere.
It only contains the formatting/styles for the display in a web browser/excel.

The data is contained in another file. Looks like C:\Mappe1.htm where the data is structured within tables?

Not rocket science really ! ! !

Greengiant224

rah · Post by **rah** » Tue Nov 19, 2013 12:13 am

No, the data is contained in one file. As I said in my initial post, I only included the top portion of the file for the sake of brevity. Don't know what the line "Content-Location: file:///C:/Mappe1.htm" means since that file is non-existant.

Not particularly useful in it's current format. If it's not rocket science, maybe it's magic ! ! !

Below is a snippet of formatting code from the file...

--></style></head><body link="blue" vlink="purple"><table border="0" cellpadding="0" cellspacing="0" style="border-collapse:collapse;table-layout:fixed" x:str=""><tr xmlns="" height="17" style="height:12.75pt"><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Vendor No.</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Vendor Name</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Product</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Material description</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Plnt</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">POrg</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">ReqmtsDate</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Order quantity</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">BUn</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Buyer Name</td><td class="x139" style="
 mso-pattern:auto none;border:.5pt solid windowtext;
 background:#DDDDDD;
 ">Name</td></tr><tr xmlns="" height="17" style="height:12.75pt"><td class="x139" style="" x:str="2003412">2003412</td><td class="x139" style="" x:str="WISCO INDUSTRIES INC / OREGON WI 53575-0">WISCO

Greengiant224 · Post by **Greengiant224** » Tue Nov 19, 2013 2:00 pm

rah:
I've spent several hours retrieving/restructuring this file. I must be

It is displayed as a table, the data is stored elsewhere and the table is populated when called from a web browser or excel.

==================================================================================================
MIME-Version: 1.0 X-Document-Type: Worksheet Content-Type: multipart/related; boundary="
----=_NextPart_01C5084F.FEF9A7A0" ------=_NextPart_01C5084F.FEF9A7A0
Content-Location: file:///C:/Mappe1.htm Content-Transfer-Encoding: text/html
Content-Type: text/html; charset="utf-8"

Vendor No.
Vendor Name
Product
Material description
Plnt
POrg
ReqmtsDate
Order quantity
BUn
Buyer Name
Name

2003412
WISCO

===================================================================================================

You may be able to re-design a new spreadsheet or perhaps a database in AOO/LO.
Obviously, the choice is yours entirely. Good Luck.

Greengiant224

rah · Post by **rah** » Tue Nov 19, 2013 4:20 pm

Thank you for your efforts.

I don't trust the generator of the file to not make more changes to it. I will probably have to do something even more distasteful than trying to extract the data ... buy a copy of MS Excel :-b

Here's an excerpt from an email between the project manager and a web programmer that I don't think I was supposed to see but they copied me anyway.

Hi Jim
I've checked with the lead developer, we would need to add special code to try and adapt the download.
Sorry, I was hoping that we would have an easy work around for this.
Thanks

...

Thanks Steve,
Lets not worry about the supplier that does not run Excel.
Jim

Oh well...

Thanks again.

rah · Post by **rah** » Tue Nov 19, 2013 7:16 pm

Just an interesting addendum to my issue.

As a last chance sort of thing, I tried using a copy of Libre Office (v4.0.1) to open this file and that failed similarly to OO. I checked and there was a new version of Libre Office (v4.1.3) so I figure "nothing to lose" so I installed it and huzzah! It opened! First it opened in Writer, which was a start. I then opened Libre Office Calc and then opened the file from it and it opened as a spreadsheet with just one row at the top of code from the file that it could not interpret as anything but text.

Well done Libre Office!

[Solved] Weird .XLS file that is treated as text

[Solved] Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: Weird .XLS file that is treated as text

Re: [SOLVED] Weird .XLS file that is treated as text