Macros and large spreadsheet.

Discuss the spreadsheet application

Macros and large spreadsheet.

Postby Stockman1 » Fri Sep 13, 2019 11:40 am

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
Stockman1
 
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Macros and large spreadsheet.

Postby robleyd » Fri Sep 13, 2019 11:48 am

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3057
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macros and large spreadsheet.

Postby RoryOF » Fri Sep 13, 2019 12:30 pm

The internal counters of the macro may hit a 64k limit. Have you investigated that?
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29893
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Postby Stockman1 » Fri Sep 13, 2019 12:44 pm

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.

Postby Stockman1 » Fri Sep 13, 2019 12:55 pm

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
Stockman1
 
Posts: 33
Joined: Tue Jul 08, 2014 1:08 pm

Re: Macros and large spreadsheet.

Postby RoryOF » Fri Sep 13, 2019 1:01 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29893
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Postby robleyd » Fri Sep 13, 2019 1:59 pm

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3057
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macros and large spreadsheet.

Postby FJCC » Fri Sep 13, 2019 2:36 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7385
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macros and large spreadsheet.

Postby RoryOF » Fri Sep 13, 2019 2:50 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29893
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros and large spreadsheet.

Postby Villeroy » Fri Sep 13, 2019 3:33 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27376
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests