Macros and large spreadsheet.

Discuss the spreadsheet application
Post Reply
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Macros and large spreadsheet.

Post by Stockman1 »

I have a csv file sent to me every hour with 220,000 rows and just two colums.

Column A shows a stock code.

Column B shows the number of items of each stock code we have in our warehouse.

For those items we have in stock I need to to put '1 day delivery' in column B. For those items we do not have in stock I need to put '2 day delivery' in column B.

I left a macro to do this and it stalls / collapses because the calc spreadsheet is not designed to handle 220,000 rows.

My question is, how should I best get over this? Should I write a macro to split the sheet into, say 22 parts then do the calculation and then join the sheet together again? Or should I use a database combined with a macro (never really had to use a database before) to do the job. I understand how macros work in calc but don't know how or even if they can be used in base.

Sorry if this question is a little basic for some.

Thanks
Open Office 4.1 / Windows Vista
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macros and large spreadsheet.

Post by robleyd »

Calc can store 1048576 rows; are you saving in native .ods format?

Are you getting an error message that you haven't mentioned?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Post by RoryOF »

The internal counters of the macro may hit a 64k limit. Have you investigated that?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Macros and large spreadsheet.

Post by Stockman1 »

robleyd wrote:Calc can store 1048576 rows; are you saving in native .ods format?

Are you getting an error message that you haven't mentioned?
Thanks robleyd

Making the macro change it to ods before calculation seems to help.

I'll try it for a couple of hours before setting as solved though. The last pass took about 45 seconds.
Open Office 4.1 / Windows Vista
Stockman1
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Macros and large spreadsheet.

Post by Stockman1 »

RoryOF wrote:The internal counters of the macro may hit a 64k limit. Have you investigated that?
How do I check that as a matter of interest please RoryOF?
Open Office 4.1 / Windows Vista
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Post by RoryOF »

That depends on the macro code. If it, for example, uses a For loop, the maximum value of the loop index might exceed a 64k limit. We would need to see the macro code to decide for certain.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macros and large spreadsheet.

Post by robleyd »

Making the macro change it to ods before calculation seems to help.
Note that some spreadsheet formats, e.g. earlier MS Excel, were limited to 65536 rows
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macros and large spreadsheet.

Post by FJCC »

Displaying the macro code might allow us to make suggestions about improving it. I can think of various ways to do this task that vary greatly in efficiency.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Post by RoryOF »

When studying programming one is regularly told that optimising code makes an improvement in execution time, but that use of a different algorithm can be much more effective.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macros and large spreadsheet.

Post by Villeroy »

What is the central software in a modern warehouse? A database.
Where do csv files with millions of rows come from? From a database. Learn how to use your tools.

Until you are ready to use your tools, you may try a csv editor: https://csved.sjfrancke.nl/#csved
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply