How to import a large number of records into Tiny DB

MIT App Inventor Help
#1

I have an app that imports a 4MByte csv file into Tiny DB which has worked for several years but the latest App Inventor version only works on relatively small files. This may be because I've changed to using 'EFile' as 'File' has a bug in process of being fixed. But I've also read that text files are now limited to 2 MByte. Is the 2 MByte limit also applied to TinyDB? Are these fundamental limits? Is there a method for importing larger files? I could split up the database into smaller units and load them separately, so long as the 2MByte limit is not also applied to Tiny DB but would like to know if there are alternative approaches.

image

image
image702×454 30.6 KB

Thanks Peter

#2

Where did you read this?

This csv: https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_confirmed_usafacts.csv
has 2.5 MB and ...

Try this: readBigCsv.aia (21.0 KB)

grafik
grafik1449×904 94.1 KB

Screenshot

grafik
grafik477×948 249 KB

#3

... and see here:

grafik
grafik1507×501 52.3 KB

1 Like
#4

Thank you Anke for those examples, Their basic text read operations are similar to mine so that suggests I should be able to read at least 2.5 Mbyte. In the past the largest file I have read was 3.9 MByte but I can no longer read text files that big. I'm testing various file sizes to find the limit or issue.

While searching for information I found two contradictory statements one which suggests the text file is limited by the size of the device (computer?) system memory.Variable Size Limits

The other suggests a limit of about 2 MByte
https://puravidaapps.com/read.php

#5

That limit is for a uri entered into the address bar of a webviewer browser, not for normal download/upload functions using HTTP with the web component (GET/POST etc)

Have a read HERE

I have found that the android webview we use starts to get a bit wobbly @ 1.2 mb / 1,258,291.2 Bytes

2 Likes
#6

Hello Peter, that is about how much text you can put into a Text Block (or Label or TextBox), nothing specific to importing a CSV, unless you are simply importing the whole file into a Text Block (which you shouldn't be).

....and that is concerned with the method, which attaches to a URL and thus the limit is the max length of a URL - and as Taifun has pointed out right there, the method is now obsolete anyway.

It's possible that there is something wrong with the CSV file itself. If you post a representative large file and just the code snippet you use to process the file, we can help you more.

1 Like
#8

Many thanks Anke - There must be something very wrong with what I'm doing and I'm guessing it has to do with Chris' observation that I shouldn't be loading all the text into a single block. I've been looking for alternatives and must be missing something obvious. I've finished listening to Beethovens 5th and my latest 100,000 record test database still hasn't loaded. At Chris' suggestion I'm sending him an image of my cut down method and the test file that I know is not corrupted i.e. it works as a small file but extending it to a large file either takes far too long or is not working.

1 Like
#9

Sorry, I think I did a copy / paste error in my block.
I was still loading the small csv (2.5 MB).
I remove my post. :upside_down_face:

#10

The problem is not reding the text, but displaying it in a Label.
It needs about 2 sec (and 5 sec for csv file of 10MB) to read. You can save the text in a variable "data":

grafik
grafik1681×986 156 KB

1 Like
#11

Thanks for the explanation Chris and Tim. Anke has demonstrated that large files can be handled in about 12 seconds so there must be something wrong with my method. I've attached a cut down version of my database read write operations and a test file that I believe is not corrupted as it works when it has a small number of records i.e 15,000 but not when it is extended to 100,000. I am reading the records as a single text file using the Read File / Got Text component so that may be the problem if I understand you correctly. I'd appreciate any advice you can give,
MyDB8.csv (2.6 MB)
image
image

#12

It might go against your storage approach, but I would prefer to store the data from the csv into a list, then store the list as a whole in the tinydb. Call back the list from the tinydb and work with the list, as opposed to 100,000 tags in the tinydb. I haven't dealt with saving back to the csv because I loaded it in the assets, but all you do is save the list to the file (using a "csv table from list" block

image
image583×664 88.4 KB

took @ 9 seconds to load up the file first time, very quick once in the tinydb.

image

2 Likes
#13

I don't display the text in a label but do save it in a list and then transfer it to TinyDB - perhaps that is where the excessive delay is and where there may be a better method? Once TinyDB is loaded up with the database it performs quickly and well but it has always taken a long time (half an hour or more) to read the external database into TinyDB.
It could be that the excessive TinyDB load time is just too long for the phone to handle without going to sleep or shutting down?

#14

You should probably consider using sqlite instead of tinydb for a database of this size....

2 Likes
#15

Chris, Thanks for that suggestion - would it still allow the App user to search on tags or key words in the list? That's a critical function in my application, searching and retrieving a record has to be as simple as possible as the user will be working outdoors in a dark environment and will be focused on setting up and using the telescope rather than manipulating lists.

Would it be reasonable after the csv has been rapidly loaded to convert the list to a tinyDB format with tags? My experience with tiny db so far is that once loaded it is very fast at searching and retrieving records.

#16

I use SQL occasionally - not all that user friendly in the environment i'm thinking of and each record only has four items so SQL might be overkill - once loaded tiny db has worked well for over the last year or two.

#17

"Chris, Thanks for that suggestion" - did you mean me ?

These blocks search through the records in all fields for the search term, and return the first find. This could be developed to return a list of records that contain the search term for a more refined selection. All depends on how unique records and fields are. Searching on just a unique fields would return a single result.

image

returns a list

image

image
image347×586 74.6 KB

takes about 3 seconds to search entire database

1 Like
¿Cómo importar un archivo ".csv", a mi base de datos TinyDB
#18

In fact, using the list method, you do not need tinydb at all - although it does provide a "backup" of the data between file saves.

#19

If your database is astronomical, then I assume it is unchanging.

Consider loading it onto a desktop sqlite database, adding indices to the database, then uploading the indexed db file to the Media folder.

Here's the tricky part ...

Can sqlite open the db file directly from the Media folder, read-only?
(I don't know.)

#20

the sqlite database needs to be imported before being able to use it...
https://puravidaapps.com/sqlite.php

Taifun

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

#21

Thanks to everyone, especially Tim for his List approach which works very well and Anke.for demonstrating that the problem was in the processing and not the data file read operation. Also apologies for getting Tim's name wrong - it was 2 am in Australia.

I've tried Tim's list approach - It's way faster than Tiny DB and fits with my App software well. Both Tiny DB and Tim's List approach retrieve records from the database quickly, Tiny DB has almost instant record retrieval, and the list approach takes about 15 seconds to retrieve records that are near the bottom of a 100,000 record list. Still perfectly acceptable for my application. Tim's approach sets up the 100,000 record list in seconds Tiny DB takes hours to assign tags to the database.

Unless my App is flawed I'd suggest the practical limit for tiny DB is about 2,000 records. It takes about 90 seconds to set up 2000 tags. The test blocks I used are copied below. I used the 100,000 record database packaged with the APK and controlled the number of records assigned tags using the text box called File Size.

Thank you, Peter

TinyDBSpeedTest
TinyDBSpeedTest744×713 47.6 KB