Page 1 of 1

Importing CSV to MySQL doesn't import all data

Posted: Thu Oct 27, 2011 4:07 pm
by atyler
I have been banging my head against this one for days, and I can't seem to figure out what is wrong:

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.65
However, the SQL line generated by the script was this:

Code: Select all

insert into `abt` values('','2011-10-26','53.48','53.75','53.07','53.65','84');
Why would this stop reading the line after the '84'? This happens completely randomly; sometimes the same error will occur after processing 10 stock symbols, sometimes 3, sometimes another number.

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");


}

}

Re: Importing CSV to MySQL doesn't import all data

Posted: Thu Oct 27, 2011 6:47 pm
by Eric!
I've been working on a Database class for automatic offsite backups and viewing the database in various formats including CSV. So I took a quick look at your code.

I don't know why it isn't working. Where exactly is it breaking? I forced your test case and commented out the mysql stuff and it worked. Perhaps there is something in your file. I changed the code around a little to speed it up and added comments where I didn't understand what you were doing. Without access to the data I had to make some guesses. I also remove the @ so the errors weren't suppressed. Also preg_replace is faster. FYI you should at least be using mysqli and better yet PDO.

Code: Select all

<?php
importCSV("filename", "ticker");

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";

// commented out for testing
//    $csvcontent = file_get_contents($csvfile);
//  Begin import from csvfile to DB
//    if ($csvcontent === FALSE) {
//       echo "File not found. Make sure you specified the correct path.\n";
//        die; // ??
//    }

//    $con = mysql_connect($databasehost, $databaseusername, $databasepassword) or die(mysql_error());
//   mysql_select_db($databasename) or die(mysql_error());

    $lines = 0;
    $queries = "";

    //TEST CASE INSERTED HERE
    $csvcontent = "2011-10-26,53.48,53.75,53.07,53.65,8455700,53.65";
    $linearray = array();

    $csvcontent=trim($csvcontent);  //might be faster to do it once here then in the foreach loop each time -- not sure, but cleaner
    foreach (preg_split("/(\r?\n)/", $csvcontent) as $line) {
        $lines++;
        /* Skips header line
          if ($lines == 1){
          continue;
          }
         */
        $line = trim($line, " \t");
        $line = preg_replace("/\r/", "", $line);

        /*         * **********************************
          This line escapes the special character. remove it if entries are already escaped in the csv file
         * ********************************** */
        // use mysql_real_escape_string instead of following line
        //$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"; //??? used somewhere -- i took advantage of it to echo out the loop's results
        // SHOULD THE FOLLOWING STUFF SHOULD BE OUTSIDE OF YOUR LOOP?
        $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
);";

// should this be outside your loop and $queries used instead of $query?  how many lines per ticker are you expecting?
// if more than one, i think you probably don't need to try to create the table every line.
//        mysql_query($tableCreator) or die("$tableCreator");
//        mysql_query($query) or die("$query");
    }
    echo $queries; // for testing  -- why not just send this once to the mysql_query instead of once per loop?
}
?>
Output:[text]insert into ticker values('','2011-10-26','53.48','53.75','53.07','53.65','8455700','53.65'); [/text]

Re: Importing CSV to MySQL doesn't import all data

Posted: Thu Oct 27, 2011 8:55 pm
by mikosiko
is there any reason for you to not use LOAD DATA INFILE to import the CSV file instead of all that code?

Re: Importing CSV to MySQL doesn't import all data

Posted: Fri Oct 28, 2011 1:41 pm
by Eric!
Personally I avoid it because not all SQL databases support it. But I think atyler probably has a problem with his source data that is unrelated to his code.

Re: Importing CSV to MySQL doesn't import all data

Posted: Mon Oct 31, 2011 12:00 pm
by atyler
Thanks for all of the replies; I still haven't been able to track down where this is going wrong, and I understand that I probably haven't given enough information for y'all to work with. Once I'm back at my workstation, I will try to post some more thorough information to work off of (and hopefully a solution, if I've discovered one by then).

@Eric! - Thanks for your effort in helping me test/troubleshoot this!