Max External File Size Limit?!?!

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Max External File Size Limit?!?!

Post by Not a lot of thought » Sun Dec 11, 2016 4:10 am

I've been working on some analytics for a project I'm involved in. I've been using test data up until this point and everything has been working fine. I just received the data for the project and it's pretty large 3.3G in .csv format. I went to start an import into the SQLite database that I set up for it, but nothing happens now. The test data, which is just a small portion of the full dataset. Is there a limit to the size file you can import/open with livecode? I tested the file (it's too large for numbers and excel, but it opened fine in text editor on MAC. Any ideas?

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Max External File Size Limit?!?!

Post by FourthWorld » Sun Dec 11, 2016 7:44 am

Like R and some other scripting languages, LiveCode uses 32-bit addressing for many internal operations. However, depending on the OS and the specifics of the task, sometimes factors outside of LiveCode can impose limits on variable size.

If your data lends itself to being processed in chunks, I've worked on files > 18 GB in LC.

If you can post the relevant portion of your code perhaps we can find ways to make that import work well for you.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Max External File Size Limit?!?!

Post by Not a lot of thought » Sun Dec 11, 2016 1:51 pm

There is a button for selecting the file (if I recall correctly, I modeled this after demonstration on the livecode website).
on mouseUp

answer file "A text file"

If it <> "" then
put it into theFilePath
put URL("file:" & theFilePath) into field "Content"

else
--no file was selected, or cancel was pressed
beep
end if

end mouseUp

Then there is a button to parse the information and upload it into a sqlite database:
global gDatabaseID

on mouseUp

databaseConnect
put getDatabaseID() into gDatabaseID

put "CREATE TABLE IF NOT EXISTS Ticker_1 (Ticker char(5), Date char(10), Open real(25), High real(25), Low real(25), Close real(25), Volume real(25), Dividends real(25), Splits real(25), Adj_Open real(25), Adj_High real(25), Adj_Low real(25), Adj_Close real(25), Adj_Volume real(25));" into tSQL
revExecuteSQL gDatabaseID, tSQL

set lockscreen to true
set lockmessages to true

put empty into field "headers"
set the itemdelimiter to return
put 1 into x
put 1 into y
put item x of field "Content" into textOne

repeat until textone is empty
put item y of field "Content" into textone
add 1 to y
end repeat
subtract 1 from y
repeat y times

If x = 1 then
put item x of field "Content" into textOne
put "("&textone&")" into field "headers"
add 1 to x
else
set the itemdelimiter to return
put item x of field "Content" into textOne
delete first char of textone
delete last char of textone
set the itemdelimiter to comma
put item 1 of textOne into Ticker
put item 2 of textOne into DateV
put item 3 of textOne into OpenV
put item 4 of textOne into HighV
put item 5 of textOne into LowV
put item 6 of textOne into CloseV
put item 7 of textOne into VolumeV
put item 8 of textOne into DividendsV
put item 9 of textOne into SplitsV
put item 10 of textOne into Adj_OpenV
put item 11 of textOne into Adj_HighV
put item 12 of textOne into Adj_LowV
put item 13 of textOne into Adj_CloseV
put item 14 of textOne into Adj_VolumeV

--insert into Ticker Data Table the import data
put "INSERT INTO Ticker_1 (Ticker, Date, Open, High, Low, Close, Volume, Dividends, Splits, Adj_Open, Adj_High, Adj_Low, Adj_Close, Adj_Volume) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)" into tSQL
revExecuteSQL gDatabaseID, tSQL, "Ticker", "DateV", "OpenV", "HighV", "LowV", "CloseV", "VolumeV", "DividendsV", "SplitsV", "Adj_OpenV", "Adj_HighV", "Adj_LowV", "Adj_CloseV", "Adj_VolumeV"

--set the progress status indicator
set lockscreen to false
put round(x / y * 100,1) into percComp
set the text of field "Perc_Comp" to percComp&"%"
add 1 to x
set lockscreen to true

end if
end repeat

set the lockscreen to false
set the lockmessages to false
end mouseUp


Here is a sample of the data (just in case this may have something to do with it, I don't think it does though):
A,1999-11-18,45.5,50.0,40.0,44.0,44739900.0,0.0,1.0,29.526230059620076,32.44640665892316,25.957125327138527,28.55283785985238,44739900.0

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Max External File Size Limit?!?!

Post by AxWald » Sun Dec 11, 2016 3:34 pm

Hi,

as I understand you have test data as .csv, and it's already 3.3GB? So it's to expect that the real data are a multiple of it? So 9GB as full data, and 18GB after 2 years, for instance? Remember, data tend to grow uncontrollable once they feel at home ...

What I'd do:
  • Import your data .csv into a real database before you let LC even touch it.
    Maybe PostgreSQL, MS SQL, MySQL (in this order). There's more of 'em.
  • Use LC to show & manipulate the data (i.e. as frontend).
  • If desired and necessary, use SQLite as cache db.
Reason:
  • The estimated size of the data is at least as large as the expected RAM of any expectable PC - so you'll run into swapping in any case. Except you spent a lot of time & brain in approbate coding.
  • Real databases are made to handle such amounts of data, and usually live on adequate computers. So why not use one?
  • SQLite is an awesome product, but it's not made for such - again, I'd not want to have such amounts of data on a local workstation at all, there are much better solutions.
Just some basic thoughts. Hope I could help.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Max External File Size Limit?!?!

Post by Not a lot of thought » Sun Dec 11, 2016 4:27 pm

Since I already had something set up for importing data into a SQLite db, I was hoping to avoid recreating a more sophisticated system for the storage. The initial test data is only a few Megs. Enough just to make sure the import would work. The initial dump is 3.3G. My machine is running 16G of RAM w/ a 1T hard drive. Now, I do agree that I will eventually have to move over to a more heavy duty platform and am currently researching my options for that future endeavor. However, I do want to run something on a low cost (preferably free) with minimal development beyond the framework of the SQL and only enough application to run the SQL and return results in order to test the viability of the option. So, I guess in that sense you could view the 3.3G of data as test data also, but just a much more sophisticated test.

Any ideas? I mean I could just shorten the test data to incorporate a fewer number of years, but I was hoping somebody might have a middle ground idea that I could use to test the viability of the information and get it migrated to a more permanent host. I suspect that the data will eventually be several T, so I'm definitely going to have to find another location, but that is down the road **crossing fingers** hopefully?!?!

Post Reply

Return to “Talking LiveCode”