Android Question Problem reading large excel file

[goehberry]

Hi Guys,

I have an excel file which contain around 40,000 row and the size of the file around 7 megabytes. And this file can be larger in the future.

I am having an issue when try to read this large excel file.

Below is my code

    Dim workbook1 As ReadableWorkbook
    Dim sheet1 As ReadableSheet
      workbook1.Initialize(File.DirDefaultExternal, "MYFILE.XLS")
    sheet1 = workbook1.GetSheet(0) 'this where my application terminate unexpectedly

Btw, with an excel file with the size of 1 or 2mb, i have no issue.

Does anyone have a solution for my issue please?

Thanks in advance

 

[barx]

Well, for a start, please explain your 'issues' and are there any related errors? could it be an out of memory error?

 

[goehberry]

Hi,

Thanks for your response.

My issue is when my code come this line
sheet1 = workbook1.GetSheet(0) 'this where my application terminate unexpectedly

i received the error message "Unfortunately, App has stopped", if i looked to the log window, it seems that it was out of memory.

Any solution to overcome this out of memory problem?

Thanks

 

[DonManfred]

Consider to convert your xls to an sqlite-database and do queries on that db.

 

[goehberry]

Hi Manfred,

Thanks for your response.

Actually i'm trying to read an excel file and insert into sqlite database. It works fine with smaller size of excel file. Is there maybe a better or easier way to convert excel file to sqlite table from B4A?

Thanks!

 

[DonManfred]

What is the final goal? Getting the data from XLS to a sqlite-db? Maybe you should write a converter and run it on your server.
It could be an windows-program running as server which reads the xls and report back the data from xls.

Or you can use php to read the xls and output the data.

In both cases the needed memory is not ececuted on your device. It´s more on your server and probably you did not run into OOMs

If the data is not exclusive you can upload a zip containing your XLS here. Plus description of what exactly you want to archieve

 

[Erel]

You can try to save the Excel file as a CSV file and then parse it with StringUtils. It might be able to handle larger files.

 

[goehberry]

Manfred,

My app has functionality to import excel file into sqlite db as a master table. I have other table also and will be join with the master table. the Final goal would be export sqlite table back to excel files.

Actually i did tried to do it from my pc and it was really fast. Thanks Manfred.

Erel,

I did tried convert the excel to csv and parse with StringUtils, it took around 20 minutes and no errors. Thanks for that Erel.

My conclusion would be better to import the large excel/csv in the PC and copy the sqlite db back to my device


Thanks again!