64k line limited spreadsheet is still line limited when using program with larger limit.

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

Reply to
legg
Loading thread data ...

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 
 Click to see the full signature
Reply to
Rick C

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

Reply to
legg

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

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 
 Click to see the full signature
Reply to
Rick C

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

Reply to
buecherk

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

--
  When I tried casting out nines I made a hash of it.
Reply to
Jasen Betts

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.

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

--
Regards, 
Martin Brown
Reply to
Martin Brown

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
Reply to
Martin Brown

Right, select that format, and save your spreadsheet.

--
 Thanks, 
    - Win
Reply to
Winfield Hill

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.

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

Reply to
legg

You don't believe in the KISS principle?

RL

Reply to
legg

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

Reply to
legg

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
Reply to
Winfield Hill

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

RL

Reply to
legg

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

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
Reply to
Martin Brown

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.
Reply to
Jasen Betts

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 heavi ly 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 mo re 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 runni ng 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 flexib le and easy to learn, requires less programming, & so much more accessible to most users. It tends to get integrated into office workflow so much tha t anything else becomes "non standards".

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

Rich Sulin

Reply to
Rich S

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.

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 have not seen too much instability apart from the original 2007. Prior to that 2002 and 2003 were both good vintages.

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

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.

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:

formatting link

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

--
Regards, 
Martin Brown
Reply to
Martin Brown

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

Reply to
legg

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

Reply to
makolber

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.