Loading CSV into MySQL Table with PHP

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

Importing CSV into MySQL with PHP script

Try this:

<?php
        $query = <<<eof
            LOAD DATA INFILE 'D:/User Directories/Desktop/test.csv'
             INTO TABLE nwcatalogue
             FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
             LINES TERMINATED BY '\n'
            (fields_go_here)
        eof;

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

As for the CSV, try something like this:

function process_csv($file) {
   $file = fopen($file, "r");
   $data = array();
   while (!feof($file)) {
      $data[] = fgetcsv($file,null,';');
   }
   fclose($file);
   return $data;
}

And then just call process_csv('D:/User Directories/Desktop/test.csv')

Load csv into MySQL, using a new table

Sure, phpMyAdmin can do this.

From the main page, look for the Import tab. This is, of course, how you expect to import any file including if you already have the database and table created, but if not phpMyAdmin creates a temporary database and table for you using generic names and best guesses as to the data types for your columns. Note that this is probably not the ideal structure, but is the best guess based on the existing data in your database. For best results, you'll probably want to put the desired column names at the top of your CSV file and select the corresponding option during import, or rename them after import (likewise with the database and table names) -- but it is absolutely possible to do the import to a new table.

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