I have a script that reads through a list of stock symbols, and for each symbol pulls data from Yahoo Finance. That data is then written to a CSV file. The attached function (see below) then reads the CSV content and writes it to a MySQL database. This process works perfectly except for some random cases when it bizarrely fails.
Example:
I just ran the script, and the first two stock symbols in the list worked perfectly, but the third failed. When the SQL import function read the last line in the CSV, it stopped short of the end of the line. The final line in the CSV is this:
Code: Select all
2011-10-26,53.48,53.75,53.07,53.65,8455700,53.65Code: Select all
insert into `abt` values('','2011-10-26','53.48','53.75','53.07','53.65','84');I am baffled. Any ideas?
Thanks!
Also...I am not a developer by trade, so I'm sure there are better ways of accomplishing what this hacked-together script does (or is supposed to do); in which case I am totally open to your suggestions.
Function:
Code: Select all
function importCSV($csvfile, $ticker) {
$databasehost = "localhost";
$databasename = "database";
$databaseusername = "user";
$databasepassword = "pass";
$fieldseparator = ",";
$lineseparator = "\n";
// Insert blank field for auto-increment ID in DB?
$addauto = 1;
// Save SQL for debugging?
$save = 0;
$outputfile = "output.sql";
// Begin import from csvfile to DB
if (!file_exists($csvfile)) {
echo "File not found. Make sure you specified the correct path.\n";
die;
}
$file = fopen($csvfile, "r");
if (!$file) {
echo "Error opening data file.\n";
die;
}
$size = filesize($csvfile);
if (!$size) {
echo "File is empty.\n";
die;
}
$csvcontent = fread($file, $size);
fclose($file);
$con = @mysql_connect($databasehost, $databaseusername, $databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());
$lines = 0;
$queries = "";
$linearray = array();
foreach (preg_split("/(\r?\n)/", trim($csvcontent)) as $line) {
$lines++;
/* Skips header line
if ($lines == 1){
continue;
}
*/
$line = trim($line, " \t");
$line = str_replace("\r", "", $line);
/* * **********************************
This line escapes the special character. remove it if entries are already escaped in the csv file
* ********************************** */
//$line = str_replace("'", "\'", $line);
/* * ********************************** */
$linearray = explode($fieldseparator, $line);
$lineDate = $linearray[0];
//$linearray = array_slice($linearray, 1);
$linemysql = implode("','", $linearray);
if ($addauto)
$query = "insert into $ticker values('','$linemysql');";
else
$query = "insert into $ticker values('$linemysql');";
$queries .= $query . "\n";
$tableCreator = "CREATE TABLE IF NOT EXISTS `$ticker`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`date` DATE NOT NULL ,
`open` DECIMAL(6,2) NOT NULL ,
`high` DECIMAL(6,2) NOT NULL ,
`low` DECIMAL(6,2) NOT NULL ,
`close` DECIMAL(6,2) NOT NULL ,
`vol` INT NOT NULL,
`adjClose` DECIMAL(6,2) NOT NULL
);";
mysql_query($tableCreator) or die ("$tableCreator");
mysql_query($query) or die ("$query");
}
}