Macros and large spreadsheet.

[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

[robleyd]

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

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

[RoryOF]

The internal counters of the macro may hit a 64k limit. Have you investigated that?

[Stockman1]

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.

[Stockman1]

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?

[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.

[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

[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.

[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.

[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