XLS II plugin

[Magnus Torell]

I am using XLS 11 for importing stock level from another system. (Many
Thanks)
I just receive excel files (.xls). It has been working for a year since I
started.
However the files that started to come a week ago will not be read with XLS
II
They open just fine in Excel, but the most simple commands with XLS II seem
to fail?
I just do following to see a difference between old and new files.
Ever since beginning with XLS II I have had to do a trick, and that is to
open and save
the file with excel -Mac excel 2011- on my computer. That seems to have
triggered
something that makes the file recognized by XLS II plugin)

C_TIME($dref)
$dref:=Open document($vName2;"*";Read mode)
If (OK=1)
$filePath:=Document
CLOSE DOCUMENT($dref)
$wb:=XLS Load ($filePath)
$sheet:=1
$cols:=XLS Get total columns ($wb;$sheet)
$rows:=XLS Get total rows ($wb;$sheet)

With the failing files I get no $cols or $rows at all.
Files seems identical to me. What can be the reason?
The supplier have automatic generated xls files and they do not know of any
changes.

What can be the change? Any ideas are appreciated.

Best Regards

Magnus Torell



--
View this message in context: http://4d.1045681.n5.nabble.com/XLS-II-plugin-tp5750156.html
Sent from the 4D Tech mailing list archive at Nabble.com.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
**********************

[Kirk Brooks]

Magnus,
I don't know that the issue is specific to the plugin. A couple of weeks
ago I downloaded some files with a .XLS file extension from a vendor who
uses SalesForce. They could not be opened by anything except xcel. Even
google sheets which is probably the most robust option for importing
anything.

This lead me to learn more about .XLS than I really wanted to know, find
that this format didn't conform to the 'traditional' .XLS format, and still
not fully understand why it's the way it is. In the case of the files I was
dealing with there is a pretty easy fix: open it in a text editor. Regular
.XLS files are standard XML. The ones I had were more like HTML and the
actual data are in a table defined by HTML tags. Strip everything from the
start of the file to <table> and everything after </table>. Which is to say
just copy the table. Then I used an online web page to convert the HTML
table to a .CSV and I was on my way.

This format would be easy-ish to read directly with 4D once you look at how
it's built. I probably won't 'cause I only have to do this once a year.

The IT guy with the vendor said they were limited by what salesForce would
give them when we talked about it. I doubt salesForce is the only entity
using this format and perhaps this is what you are dealing with.

Post by Magnus Torell
I am using XLS 11 for importing stock level from another system. (Many
Thanks)
​ ​
I just receive excel files (.xls). It has been working for a year since I
started.
​
However the files that started to come a week ago will not be read with XLS
​ ​
II
​
They open just fine in Excel, but the most simple commands with XLS II seem
to fail?

--
Kirk Brooks
San Francisco, CA
=======================
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
**************************************************************

[Koen Van Hooreweghe]

Hi Magnus, Kirk,

This is indeed a trick I have to admit I've been using too.
Excel is capable of opening files in html format, even if the file extension is .xls. Because the html format is (for my purpose) easier to generate from 4D than native xls, I use that <tr><td> etc html format. But to make sure the file is opened with Excel when a user double clicks it in the macOS Finder, the file extension .xls must be used. Otherwise it is opened in the default browser.

Obviously quick and dirty, but probably used by the big guys (SalesForce) too! :-)

Kind regards,
Koen

Post by Kirk Brooks
I don't know that the issue is specific to the plugin. A couple of weeks
ago I downloaded some files with a .XLS file extension from a vendor who
uses SalesForce. They could not be opened by anything except xcel. Even
google sheets which is probably the most robust option for importing
anything.

--------------------
Compass bvba
Koen Van Hooreweghe
Kloosterstraat 65
9910 Knesselare
Belgium
tel [phone]

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
******************************************

[Pat Bensky]

Magnus,
Has the format of the files changed from .XLS to .XLSX? If that's the case,
then I don't think the plugin you're using will work. We use Pluggers XL
plugin for these files. If you would like to send me a sample file, I'll
take a look at it for you.

Pat

Post by Magnus Torell
I am using XLS 11 for importing stock level from another system. (Many
Thanks)
I just receive excel files (.xls). It has been working for a year since I
started.
However the files that started to come a week ago will not be read with XLS
II
They open just fine in Excel, but the most simple commands with XLS II seem
to fail?
I just do following to see a difference between old and new files.
Ever since beginning with XLS II I have had to do a trick, and that is to
open and save
the file with excel -Mac excel 2011- on my computer. That seems to have
triggered
something that makes the file recognized by XLS II plugin)
C_TIME($dref)
$dref:=Open document($vName2;"*";Read mode)
If (OK=1)
$filePath:=Document
CLOSE DOCUMENT($dref)
$wb:=XLS Load ($filePath)
$sheet:=1
$cols:=XLS Get total columns ($wb;$sheet)
$rows:=XLS Get total rows ($wb;$sheet)
With the failing files I get no $cols or $rows at all.
Files seems identical to me. What can be the reason?
The supplier have automatic generated xls files and they do not know of any
changes.
What can be the change? Any ideas are appreciated.
Best Regards
Magnus Torell
--
View this message in context: http://4d.1045681.n5.nabble.
com/XLS-II-plugin-tp5750156.html
Sent from the 4D Tech mailing list archive at Nabble.com.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
**********************************************************************

--
*************************************************
CatBase - The Database Publishing Solution
tel: [phone]
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
*******************************************************

[Magnus Torell]

Dear Kirk, Koen, Pat,

Thanks for all replies.

I tried to open the file with an text editor, but I do not see any
xml or html tags. It seems to be some strange MS headers,
though later in the file I see the individual cell content that I expect.

If I try to parse with Pluggers XL Plugin I can read and parse the file.
So this can be a possible solution.

I then tried to open with LibreOffice and it surprisingly worked all the
way.
Namely: Open the .xls file with LibreOffice, just save it as is, then
open the file with 4D and the XLS II plugin and parse lines with
commands of XLS II.

I have no clue what causes this to happen or what LibreOffice does to the
file.
I just open the file and resave, confirming to use MS format excel 97-2003
in the save dialog.
I suspected that v15R5, that I use since 1 month, could be the reason, but
it wasn't.
Something changed last week and files prior to that could be parsed also in
v15,
but files coming from last week and on can not be parsed unless I do the
magic
trick to -open and resave- with LibreOffice.


Thanks for all replies.


Best Regards

Magnus



--
View this message in context: http://4d.1045681.n5.nabble.com/XLS-II-plugin-tp5750156p5750176.html
Sent from the 4D Tech mailing list archive at Nabble.com.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
**************************************

[Keisuke Miyako]

Hello,

as others have already pointed out,
".xls" is just a generic extension used to imply MS Excel as the preferred application to open it.

what we usually refer to as an XLS file are BIFF8 files.

https://support.office.com/en-us/article/File-formats-that-are-supported-in-Excel-a28ae1d3-6d19-4180-9209-5a950d51b719

but a file with the ".xls" file could be any kind of file you would open with Excel, such as TSV, CSV or HTML.

it sounds like your file is an XMLSS file. (see link above)

XLS II is a simple plugin based on an old Code Project article,
a tutorial of how to parse BIFF8 files.

only very basic features are supported,
and of course no other ".xls" files can be opened except BIFF8.

if the file is XMLSS,
you could simply parse it with 4D XML commands.



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4D_Tech-***@lists.4D.com
**************************************************