Large External File Usage

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

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

Large External File Usage

Post by Not a lot of thought » Sat Feb 04, 2017 12:30 pm

I have a large file 10,000,000+ records roughly 3.5 Gigs. It is in CSV format. I need to import it into a database for analysis, however I can't seem to work with it. The file is too large to copy and paste into excel due to record limitations (without splitting some way). I've tried importing directly into MySQL, but I get runtime error 2013 because the query runs for too long (from what I've read). I tried making adjustments to the query connection time limits in mysql and no luck. So, I figured, well I can split the data up with a program and import it that way, however I can't get any of the information to drop into a text field. I use an "answer file....URL" variable "put lines to pull the first 100 lines to see if it would work, no go. So, I copied and pasted the information into both a text file and an excel based csv. Then tried importing, both worked. I'm not sure what the issue is. Could it be a line delimiter problem? A size problem? Does a file that size need to be opened a different way?

answer file "A text file"

If it <> "" then
put it into theFilePath
set the itemdelimiter to return
put line 1 to 100 of URL("file:" & theFilePath) into field "Content"
else
--no file was selected, or cancel was pressed
beep
end if

I've been trying to get this thing working off and on for months with little luck.

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 251
Joined: Tue Apr 10, 2012 9:18 am

Re: Large External File Usage

Post by ghettocottage » Sat Feb 04, 2017 7:42 pm

Do you have ssh access to the Mysql server?
If you do, I have had better luck with large files by uploading the file to the server and then importing like:

mysql --user=mysqluser --password=yourpassword --host=localhost thedatabase < thefile.csv

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

Re: Large External File Usage

Post by FourthWorld » Sat Feb 04, 2017 9:12 pm

What is the size of the file? And can you post a few lines from it so we can see its format?
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

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

Re: Large External File Usage

Post by Not a lot of thought » Sun Feb 05, 2017 4:34 am

I am using Amazon RDS for my MySQL Server. I'm not familiar with ssh access. I do not have local access to the server. It is remote. File size roughly 3.5G as perviously noted. I attempted to open the file on the windows portion of my Mac, but it was too large for excel, access, and NotePad. Only TextEdit will open the full file. I can copy and paste portions of the file into TextEdit or the Mac Version of Excel and save as CSVs to access it from livecode. I just can't access the whole file. A few of lines of the source information are below.

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
A,1999-11-19,42.94,43.0,39.81,40.38,10897100.0,0.0,1.0,27.86413240804469,27.903066920026124,25.83304869968,26.202926563503603,10897100.0
A,1999-11-22,41.31,44.0,40.06,44.0,4705200.0,0.0,1.0,26.807221181602316,28.55283785985238,25.99606101512924,28.55283785985238,4705200.0
A,1999-11-23,42.5,43.63,40.25,40.25,4274400.0,0.0,1.0,27.584915059323162,28.318349271488696,26.124537203241346,26.124537203241346,4274400.0

In some applications it has " at the beginning and end and in other applications nothing. I can't help, but think it is something simple that I'm overlooking, but that doesn't explain why I can import small chunks, but not the whole thing.

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

Re: Large External File Usage

Post by AxWald » Sun Feb 05, 2017 9:34 am

Hi,

I'd use any real text editor (BBEdit or such on Mac) to split the file into 4 - 8 parts. Then I'd use HeidiSQL to import these into the MySQL. Since you talk about "the windows portion of my Mac" this should work - Wine would make it possible too.

The data itself look rather simple, they should go in w/o much massaging. HeidiSQL has some advanced options for SQL imports (Tools/ CSV import), in your case it should already work with the defaults.

Have fun, and good luck!
Livecode programming until the cat hits the fan ...

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

Re: Large External File Usage

Post by Not a lot of thought » Sun Feb 05, 2017 12:36 pm

Does HeidiSQL have an advantage over MySQL Workbench for importing these kinds of files? I've tried using the LOAD DATA LOCAL INFILE function to import the file a numerous occasions with no luck.

SparkOut
Posts: 1743
Joined: Sun Sep 23, 2007 4:58 pm

Re: Large External File Usage

Post by SparkOut » Sun Feb 05, 2017 1:16 pm

I don't have any experience with sqlite and a file that large, but could you try importing to a local sqlite database with just one table for this purpose and then using livecode to retrieve the lines you want with queries against the sqlite database locally?

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

Re: Large External File Usage

Post by Not a lot of thought » Sun Feb 05, 2017 1:26 pm

File was delivered as a CSV. At present I'm going through the file manually and splitting it up into manageable sections of data to import to either SQLITE or MySQL depending on if SQLITE can handle that amount of data on this machine. I would prefer a local database to a remote one as it will make queries faster and less hindered by connectivity, but I don't believe the file can be sent to me as a SQLITE table. Would be nice if it could be, though.

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 586
Joined: Fri Jun 27, 2008 9:00 pm

Re: Large External File Usage

Post by Mikey » Sun Feb 05, 2017 2:11 pm

It's weird that this is causing a problem. When I scrape I wind up with similarly large files in csv, but I am able to manipulate them. I start by passing them through my scrape parser in LC and then I can open them in something like libre or excel.

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

Re: Large External File Usage

Post by Not a lot of thought » Sun Feb 05, 2017 6:58 pm

@Sparkout You mentioned importing into SQLITE. Let me clarify, I've only had luck inserting 1 line at a time. Is there an import file function like MySQL's LOAD DATA function? If so, that would be handy.
SparkOut wrote:I don't have any experience with sqlite and a file that large, but could you try importing to a local sqlite database with just one table for this purpose and then using livecode to retrieve the lines you want with queries against the sqlite database locally?

SparkOut
Posts: 1743
Joined: Sun Sep 23, 2007 4:58 pm

Re: Large External File Usage

Post by SparkOut » Sun Feb 05, 2017 7:29 pm

Copied from a stackoverflow page http://stackoverflow.com/questions/1045 ... nto-sqlite

Code: Select all

To go from SCRATCH with SQLite DB to importing the CSV into a table:

Get SQLite from the website.
At a command prompt run sqlite3 <your_db_file_name> 
*It will be created as an empty file.
Make a new table in your new database. The table must match your CSV fields for import.
You do this by the SQL command: CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>);
Once you have the table created and the columns match your data from the file then you can do the above...

.mode csv <table_name>
.import <filename> <table_name>
If the first row of the csv file contains column names then you should be to leave out the table column setup.

You can see how to run sqlite command line statements here https://www.sqlite.org/cli.html


Again, I don't know how it will cope with a 3.5GB csv file

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

Re: Large External File Usage

Post by AxWald » Mon Feb 06, 2017 11:46 am

Hi,
Not a lot of thought wrote:Does HeidiSQL have an advantage over MySQL Workbench [...]
Never used Workbench. Tried it once, experienced it as a bloated, over-featured monster, deleted it thus ;-)
HeidiSQL is small, fast & reliable, does anything I want from it, can work with MS SQL & PostgreSQL additionally, and even runs from an USB stick - this is how I like my software.

For SQLite, I use the SQLite manager addon for Firefox, btw.

Have fun!
Livecode programming until the cat hits the fan ...

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

Re: Large External File Usage

Post by Not a lot of thought » Mon Feb 06, 2017 1:43 pm

@SparkOut Are you referring to the doing something like this?

Code: Select all

   put ".mode csv" into tSQL
   revExecuteSQL lDatabaseID, tSQL
   put ".import "&theFilePathIni&" Test_Table" into tSQL
   revExecuteSQL lDatabaseID, tSQL
   
where the theFilePathIni is a variable containing the selected file name.

SparkOut
Posts: 1743
Joined: Sun Sep 23, 2007 4:58 pm

Re: Large External File Usage

Post by SparkOut » Mon Feb 06, 2017 8:12 pm

Well that might even be a possibility, but I meant using sqlite command line to try and import the csv file external to livecode. Then once you have a database table, you can run queries against it from livecode to retrieve the lines you want. I don't know if that would work but might be a possibility.

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

Re: Large External File Usage

Post by Not a lot of thought » Mon Feb 06, 2017 11:09 pm

What I did above didn't work. I'm not familiar with SQLite command line. I will have to research that. Right now my computer is running through a very lengthy line by line import. I'll test the idea on a different machine and see if that works.

Post Reply

Return to “Databases”