CODEWITHSUNDEEP
phpmysqlparsingexplode
Drizzle53
Drizzle53

Reputation: 124

Parse text file line by line and store data in MYSQL database

I do not care about security at this point. I have been stuck on this code for 4 days. Please help. This is the code I have at this point. I am uploading a text file inside my php web application. Upload is successful. I move the file to a directory and this works also. I just need help with the parsing algorithm during the foreach loop. It will store the two values once then it wont ever do it again. I already have the database settings saved in my file. The text file is tab delimited and all the data types are right in the database table.

     $file_name= $_FILES['file1']['name'];
     $add= "uploads/$file_name";
     $move = move_uploaded_file($_FILES['file1']['tmp_name'], $add);
     $get = file_get_contents("$add");
     $delimiter = "\n";
     $line = explode($delimiter, $get);
     foreach ($line as $value => $data) {
         $delimiter = "\t";
         $tab = explode($delimiter, $data);
         $datetime = $tab[0];
         $deltatime = $tab[1]; 
         $sql = "INSERT INTO RobotData (datetime, deltatime) VALUES('$datetime', '$deltatime')"; 
         $store = mysqli_query($conn, $sql);
     }

Upvotes: 0

Views: 2757

Answers (3)

Aleksey Ratnikov
Aleksey Ratnikov

Reputation: 569

Try LOAD DATA INFILE - it was created to extra fast loading large amount of data from text files: 

LOAD DATA LOCAL INFILE '/full/path/to/text/file'
INTO TABLE RobotData
COLUMNS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

Upvotes: 1

Shrikant Mavlankar
Shrikant Mavlankar

Reputation: 1153

Try something like this and let me know 

$file_name = $_FILES['file1']['name'];
$add = "uploads/$file_name";
$move = move_uploaded_file($_FILES['file1']['tmp_name'], $add);

$handle = fopen($add, "r");

if ($handle) {

    while (($line = fgets($handle)) !== false) {

        $arr = explode("\t", $line);
        $datetime = mysqli_real_escape_string($arr[0]);
        $deltatime = mysqli_real_escape_string($arr[1]);

        $sql = "INSERT INTO RobotData (datetime, deltatime) VALUES('$datetime', '$deltatime')";
        $store = mysqli_query($conn, $sql);
    }

    fclose($handle);
} else {
    // error opening the file.
}

Edit

Added escaping special characters in a string for use in an SQL statement

Upvotes: 1

Drizzle53
Drizzle53

Reputation: 124

This is the code I'm using now...

     $file_name= $_FILES['file1']['name'];
     $add= "uploads/$file_name";
     $move = move_uploaded_file($_FILES['file1']['tmp_name'], $add);
     $handle = fopen($add, "r");
     if ($handle) {
         while (($line = fgets($handle)) !== FALSE) {
            $delimiter = "\t";
            $tab = explode($delimiter, $line);
            $datetime = $tab[0];
            $deltatime = $tab[1];  
             $sql = "INSERT INTO RobotData (datetime, deltatime) VALUES ('$datetime', '$deltatime')"; 
             $store = mysqli_query($conn, $sql);
              if($store === TRUE) {
                  fclose($handle);
                 $delete= unlink($add);
                 echo "YOU DID IT!!";
             }
             else {
                 fclose($handle);
                 $delete= unlink($add);
                 echo "YOU SUCK!!";
             }
         }
    }
    else {
         fclose($handle);
         $delete= unlink($add);
         echo "Something went wrong";
    }

Upvotes: 0

Related Questions