Large External File Usage
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Large External File Usage
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.
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.
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Large External File Usage
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
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
-
- VIP Livecode Opensource Backer
- Posts: 9842
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: Large External File Usage
What is the size of the file? And can you post a few lines from it so we can see its format?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
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.
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.
Re: Large External File Usage
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!
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!
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!
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!
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
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.
Re: Large External File Usage
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?
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
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.
Re: Large External File Usage
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.
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
@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?
Re: Large External File Usage
Copied from a stackoverflow page http://stackoverflow.com/questions/1045 ... nto-sqlite 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
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>
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
Re: Large External File Usage
Hi,
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!
Never used Workbench. Tried it once, experienced it as a bloated, over-featured monster, deleted it thus ;-)Not a lot of thought wrote:Does HeidiSQL have an advantage over MySQL Workbench [...]
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!
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!
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!
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
@SparkOut Are you referring to the doing something like this?
where the theFilePathIni is a variable containing the selected file name.
Code: Select all
put ".mode csv" into tSQL
revExecuteSQL lDatabaseID, tSQL
put ".import "&theFilePathIni&" Test_Table" into tSQL
revExecuteSQL lDatabaseID, tSQL
Re: Large External File Usage
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.
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Large External File Usage
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.