Importing CSV Data Using PHP/MySQL

Importing CSV data using PHP/MySQL

I answered a virtually identical question just the other day: Save CSV files into mysql database

MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.

Simple example:

<?php
$query = <<<eof
    LOAD DATA INFILE '$fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (field1,field2,field3,etc)
eof;

$db->query($query);
?>

It's as simple as that.

No loops, no fuss. And much much quicker than parsing it in PHP.

MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps

Importing csv file into mysql database using php

I need to make some estimations because I don't have your Excel File.

Imagine your $sheet_data now looks like that

Array
(
    [0] => Array
        (
            [0] => Street1
        )

    [1] => Array
        (
            [0] => Street2
        )

    [2] => Array
        (
            [0] => Street3 with a long name, more than 40 characters
        )

)

Each Array-Element in the 1st level representing one line - each element in the 2nd level contains the corresponding columns.

foreach ($sheet_data as $line){
    //maybe you need to skip the first line because it could contain just some headers
    //in my estimation $line[0] contains the address
    $address = $line[0];

    //split address after 40 characters
    $split = str_split($address, 40);

    //$conn - mysqli connection
    $stmt = $conn->prepare("INSERT INTO address_table (Add1, Add2, Add3)
    VALUES (?, ?, ?)");

    $addr1=""; $addr2=""; $addr3="";
    if(isset($split[0]) $addr1 = $split[0];
    if(isset($split[1]) $addr2 = $split[1];
    if(isset($split[2]) $addr3 = $split[2];

    //fill query params params with values
    $stmt->bind_param("sss", $addr1, $addr2, $addr3);

    $stmt->execute();
}

For the 3rd line - table entry would be filled with

Add1 => Street3 with a long name, more than 40 c
Add2 => haracters   

One thing you still need to check - if address has more than 120 characters the additional characters are lost atm.

And please use prepared statements like in my example - when you fill your query directly with variables you are vulnerable to SQL-Injection

Try getting data from CSV to insert in mysql using PHP, Read but it is not storing in the database

Welcome to stackoverflow. Since firstname, middlename and last name are likely char types in SQL, they need single quotes in the insert. Using your syntax, something like this should do it:

$q = "INSERT INTO file (first,middle,last,age) VALUES ('".$firstname."','".$middlename."',"'.$lastname."',".$age.")";

A good debugging method here would be to paste the $q value that you echo into a SQL tool or command line and execute it.

Uploading CSV file into mysql database using PHP

Try

<?php
    // version 1.02
    // display errors for debugging
    ini_set("display_errors", true);
    error_reporting(E_ALL);

    // Open a connection to the SQL server so we can run queries later. 
    $conn = new mysqli(removed for privacy); // DON'T FORGET TO EDIT THIS PART!
    // Output error info if there was a connection problem
    if ($conn->connect_errno) {
        die("<h3> Uh oh! It looks like we're having trouble connecting to the 
 website at the moment. Try again soon! {$conn->connect_error}</h3>");
    }

    // file name
    $file_contents = $_FILES["uploadedfile"]["tmp_name"];

    $SQL_statement = "LOAD DATA LOCAL INFILE '$file_contents' INTO TABLE C_CAMPER FIELDS TERMINATED BY ','
                        LINES TERMINATED BY '\n' IGNORE 3 LINES
                        (camperFName,campLName,camperAddress,camperCity,camperZip,camperCountry,camperPhone,camperEmail,@camperAltEmail,@camperDOB,camperAge,camperSchool,camperGradYear,camperGPA,`camperACT/SAT`,camperPar,camperParPhone,camperPrimPos,camperSecPos)
                        SET camperDOB = STR_TO_DATE(@camperDOB, '%m/%d/%Y'),
                            camperAltEmail = NULLIF(@camperAltEmail, 'null');";
    // Run SQL query
    if( !$conn->query($SQL_statement)) {
        echo $conn->error;
    }

    // Close the SQL connection
    $conn->close();
?>

---

Related Topics