64k line limited spreadsheet is still line limited when usin

[legg]

Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

RL

 

[Rick C]

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:

Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

File format? Is the error there if you save it as a newer format first?

LibreOffice seems to have 2^20 lines. It is very easy to switch. I prefer LibreOffice actually, not that there is much difference.

--

Rick C.

- Get 5,000 miles of free Supercharging
- Tesla referral code - https://ts.la/richard11209

 

[legg]

On Fri, 24 May 2019 17:55:03 -0700 (PDT), Rick C
<gnuarm.deletethisbit@gmail.com> wrote:

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

File format? Is the error there if you save it as a newer format first?

LibreOffice seems to have 2^20 lines. It is very easy to switch. I prefer LibreOffice actually, not that there is much difference.

The error is a warning that the program cannot dump non-vacant lines
to permit insertion of the new lines.

I'm dealing with .xls file extensions. That IS the file format as far
as I'm aware.

It is expected to be handled by anyone with a spreadsheet program.
Basically text files, sequenced alpha-numerically (or numeri-
alphabetically by excel in practice)to be sorted, sequenced, searched
or manipulated under the end user's preferences, for whatever purpose
they want.

I'm running into rogue vendors who issue part marking numbers of
3chars +, generating >16k variations for a single part function with
fixed incremental factory adjustments. I'll bet they havent actually
shipped 100 of these variations.

I'm tempted to give them their own private page, that nobody will ever
look at. Otherwise it's just noise.

RL

 

[Rick C]

On Friday, May 24, 2019 at 10:59:59 PM UTC-4, legg wrote:

On Fri, 24 May 2019 17:55:03 -0700 (PDT), Rick C
gnuarm.deletethisbit@gmail.com> wrote:

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

File format? Is the error there if you save it as a newer format first?

LibreOffice seems to have 2^20 lines. It is very easy to switch. I prefer LibreOffice actually, not that there is much difference.

The error is a warning that the program cannot dump non-vacant lines
to permit insertion of the new lines.

I'm dealing with .xls file extensions. That IS the file format as far
as I'm aware.

I think that is the old format. Isn't .xlsx the new format?


> It is expected to be handled by anyone with a spreadsheet program.

Microsoft is the one who screwed the pooch years ago when they switched formats. The old versions of the programs can't read the new file formats. That was when I started using LibreOffice to open the .xlsx files and convert them to .xls format.

After a bit I thought, why not try using LibreOffice and it worked just as well as Excel other than a few compatibility issues with embedded drawings. Eventually they fixed that.

I do still miss Visio though. That was a great tool that MS bought.

--

Rick C.

+ Get 5,000 miles of free Supercharging
+ Tesla referral code - https://ts.la/richard11209

Basically text files, sequenced alpha-numerically (or numeri-
alphabetically by excel in practice)to be sorted, sequenced, searched
or manipulated under the end user's preferences, for whatever purpose
they want.

I'm running into rogue vendors who issue part marking numbers of
3chars +, generating >16k variations for a single part function with
fixed incremental factory adjustments. I'll bet they havent actually
shipped 100 of these variations.

I'm tempted to give them their own private page, that nobody will ever
look at. Otherwise it's just noise.

RL

 

For ins Office there is a file Converter new to old.
MS may have taken it off their Website.
It even allows office2000 to open new format

 

[Jasen Betts]

On 2019-05-24, legg <legg@nospam.magma.ca> wrote:

Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

Don't try to use a spreadsheet as a database.

--
When I tried casting out nines I made a hash of it.

 

[Martin Brown]

On 25/05/2019 04:03, legg wrote:

On Fri, 24 May 2019 17:55:03 -0700 (PDT), Rick C
gnuarm.deletethisbit@gmail.com> wrote:

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

File format? Is the error there if you save it as a newer format first?

LibreOffice seems to have 2^20 lines. It is very easy to switch. I prefer LibreOffice actually, not that there is much difference.

The error is a warning that the program cannot dump non-vacant lines
to permit insertion of the new lines.

I'm dealing with .xls file extensions. That IS the file format as far
as I'm aware.

You will have to resave it in the native format of post 2007 Excel
format .xlsx or .xlsm if it contains macros. Then and only then the line
limit may be lifted if you are very lucky. Legacy spreadsheets and Word
documents sometimes develop interesting quirks when moved over.

The one I am most familiar with is exponential growth in size each time
the hybrid new document is saved as orphanned metadata accumulates. You
may get warnings when you save it in the new format - pay attention to
them to be sure none of them affect features you are using.

It is expected to be handled by anyone with a spreadsheet program.
Basically text files, sequenced alpha-numerically (or numeri-
alphabetically by excel in practice)to be sorted, sequenced, searched
or manipulated under the end user's preferences, for whatever purpose
they want.

Once you make it greater than 2^16 lines it will only work on those
spreadsheets that support that functionality.

--
Regards,
Martin Brown

 

[legg]

On Sat, 25 May 2019 08:43:15 -0000 (UTC), Jasen Betts
<jasen@xnet.co.nz> wrote:

On 2019-05-24, legg <legg@nospam.magma.ca> wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

Don't try to use a spreadsheet as a database.

You don't believe in the KISS principle?

RL

 

[legg]

On Fri, 24 May 2019 20:53:57 -0700 (PDT), Rick C
<gnuarm.deletethisbit@gmail.com> wrote:

On Friday, May 24, 2019 at 10:59:59 PM UTC-4, legg wrote:
On Fri, 24 May 2019 17:55:03 -0700 (PDT), Rick C
gnuarm.deletethisbit@gmail.com> wrote:

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

File format? Is the error there if you save it as a newer format first?

LibreOffice seems to have 2^20 lines. It is very easy to switch. I prefer LibreOffice actually, not that there is much difference.

The error is a warning that the program cannot dump non-vacant lines
to permit insertion of the new lines.

I'm dealing with .xls file extensions. That IS the file format as far
as I'm aware.

I think that is the old format. Isn't .xlsx the new format?

I've avoided the .xlsx file format and regularly convert it back to
..xls before working on it. Initially this was because of the apparent
file size bloat and lack of backwards compatibility.
Just another MS licensing grab contributing to an already moribund
monopoly, stomping on the work of previous developers.
I was appalled that the new format was organized using hypertext,
which seemed likely to introduce a whole new can of worms.

It is expected to be handled by anyone with a spreadsheet program.

Microsoft is the one who screwed the pooch years ago when they switched formats. The old versions of the programs can't read the new file formats. That was when I started using LibreOffice to open the .xlsx files and convert them to .xls format.

After a bit I thought, why not try using LibreOffice and it worked just as well as Excel other than a few compatibility issues with embedded drawings. Eventually they fixed that.

I do still miss Visio though. That was a great tool that MS bought.

I'll see how libre office goes. If all the reader needs to do, to get
the same simple functionality of larger .xls formats, is to dump their
MS dependencies, then it may be worth the trouble.

This marking code information already has a section devoted to
incorrect, spurious or just plain fraudulent information, weeded out
of the available data. The current mfr that generates 16K lines for
just one type of factory trimmed part, per physical package type, is
probably just going to end up there. It's just noise.

RL

 

[Winfield Hill]

Rick C wrote...

legg wrote:
Rick C wrote:
On Friday, May 24, 2019, legg wrote:

Component marking database has hit the 64k line
limit of pre-2010 excel.

File format? Is the error there if you save it
as a newer format first?

I'm dealing with .xls file extensions.

I think that is the old format. Isn't .xlsx the
new format?

Right, select that format, and save your spreadsheet.


--
Thanks,
- Win

 

[Martin Brown]

On 25/05/2019 09:43, Jasen Betts wrote:

On 2019-05-24, legg <legg@nospam.magma.ca> wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

Don't try to use a spreadsheet as a database.

Although it is inadvisable to do so for a large database it is just
about tolerable to use a spreadsheet for a modest database.

--
Regards,
Martin Brown

 

[legg]

On 25 May 2019 06:10:55 -0700, Winfield Hill
<hill@rowland.harvard.edu> wrote:

Rick C wrote...
legg wrote:
Rick C wrote:
On Friday, May 24, 2019, legg wrote:

Component marking database has hit the 64k line
limit of pre-2010 excel.

File format? Is the error there if you save it
as a newer format first?

I'm dealing with .xls file extensions.

I think that is the old format. Isn't .xlsx the
new format?

Right, select that format, and save your spreadsheet.

In .xlsx format, in excel 2010, I get the warning that
'the copy and paste areas are not the same size and shape',
.. . . . when I'm using the 'insert' directive for 5000
lines being moved from the draft sheet to the actual list
sheet.

Size and shape have nothing to do with insertions, unless
2010 is still choking on a 64k line limit.

RL

 

[Winfield Hill]

legg wrote...

On 25 May 2019 06:10:55 -0700, Winfield Hill
hill@rowland.harvard.edu> wrote:

Rick C wrote...
legg wrote:
Rick C wrote:
On Friday, May 24, 2019, legg wrote:

Component marking database has hit the 64k line
limit of pre-2010 excel.

File format? Is the error there if you save it
as a newer format first?

I'm dealing with .xls file extensions.

I think that is the old format. Isn't .xlsx the
new format?

Right, select that format, and save your spreadsheet.

In .xlsx format, in excel 2010, I get the warning that
'the copy and paste areas are not the same size and shape',
. . . . when I'm using the 'insert' directive for 5000
lines being moved from the draft sheet to the actual list
sheet.

Size and shape have nothing to do with insertions, unless
2010 is still choking on a 64k line limit.

Rather than copy and paste, I use the data insert /
import command, and use the text-input wizard to
setup a filter, then save that setting for re-use.


--
Thanks,
- Win

 

[legg]

On Sat, 25 May 2019 09:40:15 -0400, legg <legg@nospam.magma.ca> wrote:

On 25 May 2019 06:10:55 -0700, Winfield Hill
hill@rowland.harvard.edu> wrote:

Rick C wrote...
legg wrote:
Rick C wrote:
On Friday, May 24, 2019, legg wrote:

Component marking database has hit the 64k line
limit of pre-2010 excel.

File format? Is the error there if you save it
as a newer format first?

I'm dealing with .xls file extensions.

I think that is the old format. Isn't .xlsx the
new format?

Right, select that format, and save your spreadsheet.

In .xlsx format, in excel 2010, I get the warning that
'the copy and paste areas are not the same size and shape',
. . . . when I'm using the 'insert' directive for 5000
lines being moved from the draft sheet to the actual list
sheet.

Size and shape have nothing to do with insertions, unless
2010 is still choking on a 64k line limit.

RL

You have to manually transfer the data into a new spreadsheet, for the
new limits to be applied.

RL

 

[Martin Brown]

On 25/05/2019 16:49, legg wrote:

On Sat, 25 May 2019 09:40:15 -0400, legg <legg@nospam.magma.ca> wrote:

On 25 May 2019 06:10:55 -0700, Winfield Hill
hill@rowland.harvard.edu> wrote:

Rick C wrote...
legg wrote:
Rick C wrote:
On Friday, May 24, 2019, legg wrote:

Component marking database has hit the 64k line
limit of pre-2010 excel.

File format? Is the error there if you save it
as a newer format first?

I'm dealing with .xls file extensions.

I think that is the old format. Isn't .xlsx the
new format?

Right, select that format, and save your spreadsheet.

In .xlsx format, in excel 2010, I get the warning that
'the copy and paste areas are not the same size and shape',
. . . . when I'm using the 'insert' directive for 5000
lines being moved from the draft sheet to the actual list
sheet.

Excel 2007 and onwards have a few minor differences which can affect
macros so that code which worked in previous versions can fail.

Size and shape have nothing to do with insertions, unless
2010 is still choking on a 64k line limit.

RL

You have to manually transfer the data into a new spreadsheet, for the
new limits to be applied.

Save as csv from the old Excel and import again into the new Excel
version. This will strip off any legacy baggage from Excel97 etc.

Save for web is another way to do it and that preserves any images
(though it quite often mangles things a bit).

Otherwise you can get some strange interactions - especially if people
with different versions of Office are allowed to edit the main file.

--
Regards,
Martin Brown

 

[Jasen Betts]

On 2019-05-25, legg <legg@nospam.magma.ca> wrote:

On Sat, 25 May 2019 08:43:15 -0000 (UTC), Jasen Betts
jasen@xnet.co.nz> wrote:

On 2019-05-24, legg <legg@nospam.magma.ca> wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

Don't try to use a spreadsheet as a database.

You don't believe in the KISS principle?

I absolutely do, hence the reccomendation above.

if it's just a list of markings and translations CSV is probably a
better format.


--
When I tried casting out nines I made a hash of it.

 

[Rich S]

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:

Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6 lines
in it's spec and 'help' files.

What's going on?

RL

I havent found a "expert blog" that reveals all the Excel problems I've had..

In practice, Excel has limitations, despite its claimed specs. I rely heavily on Excel in my projects. My file sizes are quite large, 1 to 10MB. Excel becomes slow, and downright unstable, as the workbook grows large. But more indicative of the "stress" is the working memory (which is 10-100x more than the file size. Excel can be very RAM hungry). Have Task Manager running as you open a large file, lo and behold.

I found Excel 2010 to be more faster and stable than 2013 and 2016 versions.. (I have not tried newer installed versions, or the cloud-based versions)..

Cells with very long text strings (paragraphs) seem to be biggest challenge to Excel, and really slow it down. Try to keep the amount of text low in cells, and low in general.

Yes, I know Excel wasn't meant to be a "true" database. But it is so flexible and easy to learn, requires less programming, & so much more accessible to most users. It tends to get integrated into office workflow so much that anything else becomes "non standards".

Save files in xlsx or xlsm format. The xlsb files *are* smaller, but I've had Excel freeze up on loading larger ones. The xlsb wasn't corrupted, but Excel 2016 seemed to not know how to open them.

Rich Sulin

 

[Martin Brown]

On 25/05/2019 23:15, Rich S wrote:

On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6
lines in it's spec and 'help' files.

What's going on?

RL

I havent found a "expert blog" that reveals all the Excel problems
I've had.

There used to be a reasonably active usenet group for Excel in the
comp.microsoft hierarcy. I haven't used it for a while but I did when
the ill fated 2007 was dumped on the world with many many bugs.

In practice, Excel has limitations, despite its claimed specs. I rely
heavily on Excel in my projects. My file sizes are quite large, 1 to
10MB. Excel becomes slow, and downright unstable, as the workbook
grows large. But more indicative of the "stress" is the working
memory (which is 10-100x more than the file size. Excel can be very
RAM hungry). Have Task Manager running as you open a large file, lo
and behold.

Strangely I haven't seen much of that but my spreadsheets tend to
contain very large arrays of numbers and numerical formulae.

I have seen internal race problems with the charting where if you are
doing it from VBA it is possible for Excel on a multiprocesor to attempt
to plot data before the axis scaling is well defined. Ends up in a blue
screen or locks up completely.

I found Excel 2010 to be more faster and stable than 2013 and 2016
versions. (I have not tried newer installed versions, or the
cloud-based versions).

I have not seen too much instability apart from the original 2007.
Prior to that 2002 and 2003 were both good vintages.

Cells with very long text strings (paragraphs) seem to be biggest
challenge to Excel, and really slow it down. Try to keep the amount
of text low in cells, and low in general.

Embedded images have caused the most grief in the things I have been
asked to look at that had gone horribly wrong.

Yes, I know Excel wasn't meant to be a "true" database. But it is so
flexible and easy to learn, requires less programming, & so much more
accessible to most users. It tends to get integrated into office
workflow so much that anything else becomes "non standards".

For small databases that involve some computation I'd say Excel was
ideal. I use it for our village hall booze stock control with about 50
line items (database would be overkill).

I'd still consider Excel for a few thousand items, above that then a
database program is a more sensible approach.

Save files in xlsx or xlsm format. The xlsb files *are* smaller, but
I've had Excel freeze up on loading larger ones. The xlsb wasn't
corrupted, but Excel 2016 seemed to not know how to open them.

Not experienced that but I can believe it happens. The most common
migration problem with large legacy documents Word or Excel that get
used on machines with different versions of Excel is a buildup of
orphanned metadata that manifests itself as an exponential increase in
the filesize each time around the loop. Microsoft used to actually admit
to this if you probe the MSKB with exactly the right question:

https://support.microsoft.com/en-us/help/111277

It fails 404 these days. I don't believe it is fixed.

--
Regards,
Martin Brown

 

[legg]

On Sun, 26 May 2019 09:27:00 +0100, Martin Brown
<'''newspam'''@nezumi.demon.co.uk> wrote:

On 25/05/2019 23:15, Rich S wrote:
On Friday, May 24, 2019 at 7:15:56 PM UTC-4, legg wrote:
Component marking database has hit the 64k line limit of pre-2010
excel.

When I try to work on the spreadsheet using post-2010 excel, the
program still chokes at the same line count while boasting 10^6
lines in it's spec and 'help' files.

What's going on?

RL

I havent found a "expert blog" that reveals all the Excel problems
I've had.

There used to be a reasonably active usenet group for Excel in the
comp.microsoft hierarcy. I haven't used it for a while but I did when
the ill fated 2007 was dumped on the world with many many bugs.
snip

The same query at m.public.excel.programming gets no response, though
the news group shows regular activity. There's no guarantee that
usenet messages migrate to the source of the posts seen there, or are
just trickle-down from an MS message board.

RL

 

I had a weird thing happen in Excel the other day.

I created a chart in a spreadsheet and copy and pasted just the chart into an email.

Then I kept the email open but did not send it yet.

Then I changed some numbers in the spreadsheet as a what if test and as expected the data on the chart in the spreadsheet changed to reflect the new data. So far so good.

But to my amazement, the chart in the email that I had previously copied and pasted ALSO CHANGED. This is NOT what I wanted or expected.

Apparently the chart in the email is no longer a static object but rather is linked back to the source spreadsheet somehow.

Who would think someone would WANT a feature like that.

Made me wonder that if they could, would they link it back even after I sent the email.

KISS is dead.

Mark