Macros and large spreadsheet.
Macros and large spreadsheet.
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
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
Re: Macros and large spreadsheet.
Calc can store 1048576 rows; are you saving in native .ods format?
Are you getting an error message that you haven't mentioned?
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.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Macros and large spreadsheet.
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
Re: Macros and large spreadsheet.
Thanks robleydrobleyd wrote:Calc can store 1048576 rows; are you saving in native .ods format?
Are you getting an error message that you haven't mentioned?
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
Re: Macros and large spreadsheet.
How do I check that as a matter of interest please RoryOF?RoryOF wrote:The internal counters of the macro may hit a 64k limit. Have you investigated that?
Open Office 4.1 / Windows Vista
Re: Macros and large spreadsheet.
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
Re: Macros and large spreadsheet.
Note that some spreadsheet formats, e.g. earlier MS Excel, were limited to 65536 rowsMaking the macro change it to ods before calculation seems to help.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Macros and large spreadsheet.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macros and large spreadsheet.
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
Re: Macros and large spreadsheet.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice