Importing CSV data vie PHP

Code Snippets
#1
#2
One thing is if there are duplicate entries, you might get an error message and the process might stop

You need to use IGNORE INTO TABLE and you wont get duplicates or errors.

#3

This may be of use to someone. You will need to tweak it per your needs. It works as is for the app it is in. (Dont ask for support on it, but feel free to post any improvements, and yes, there is room to improve it)

[php]<?php
/*
Upload Version 3.0
Last Modified 9/29/2015 3:49 PM
Author Galaxy Internet - Kevin Rubio

Change Log Ver 1.1: Added array for allowed file types.

Change Log Ver 3.0:
Added csv to array for allowed file types.
Changed Form action from $_SERVER[‘PHP_SELF’] to $_SERVER[‘SCRIPT_NAME’]

*/

?>

Upload File
<? //------------------------------------------------------------------------------ // Upload Path //------------------------------------------------------------------------------

$upload_dir = $path.DIRECTORY_SEPARATOR.‘uploads2’;
//echo $destination_path = getcwd().DIRECTORY_SEPARATOR. ‘uploads’; Same as above

//------------------------------------------------------------------------------
// Allowed File Types - 1=Allow 0=Not Allowed
//------------------------------------------------------------------------------

//Image Types
$allow_jpeg = ‘0’;
$allow_jpg = ‘0’;
$allow_gif = ‘0’;
$allow_png = ‘0’;

// Document Types
$allow_txt = ‘0’;
$allow_csv = ‘1’;
$allow_vnd_ms_excel=‘1’;

$allow_word = ‘0’;
$allow_pdf = ‘0’;

// Maximum upload size in bytes
$max_upload_size = ‘15000000’;//15000000 = 15 meg

//------------------------------------------------------------------------------

if (!isset($_FILES[‘file’]))
{
echo (‘

Select a file to Upload

’);
}

// Selected and uploaded a file
if (isset($_FILES[‘file’]) && $_FILES[‘file’][‘size’] > 0)
{

$file          = $_FILES['file'];
$file_name     = $_FILES["file"]["name"];
$file_type     = $_FILES["file"]["type"];
$file_size     = $_FILES["file"]["size"];
$file_location = $_FILES["file"]["tmp_name"];

//------------------------------------------------------------------------------
// Check for file named lots.csv
//------------------------------------------------------------------------------

if ($file_name != 'lots.csv')
    {
    die("<div class=\"error_custom\">The required filename is lots.csv. You attampted to upload $file_name</div>");
    }

//------------------------------------------------------------------------------
// Check for maximum file upload size
//------------------------------------------------------------------------------

if ($file_size > "$max_upload_size") // In Bytes
    {
    die("<div class=\"error_custom\">Your file is too big!\n<br> The file $file_name is $file_size bytes. Upload max = $max_upload_size bytes</font></div>");
    }

//------------------------------------------------------------------------------
// Check for allowed file types
//------------------------------------------------------------------------------

$allowed_file_type = array();

if ($allow_jpeg == '1')
    {
    array_push($allowed_file_type, "image/jpeg");
    }
if ($allow_jpg == '1')
    {
    array_push($allowed_file_type, "image/jpg");
    }
if ($allow_gif == '1')
    {
    array_push($allowed_file_type, "image/gif");
    }
if ($allow_png == '1')
    {
    array_push($allowed_file_type, "image/png");
    }
if ($allow_pdf == '1')
    {
    array_push($allowed_file_type, "application/pdf");
    }
if ($allow_txt == '1')
    {
    array_push($allowed_file_type, "text/plain");
    }
if ($allow_csv == '1')
    {
    array_push($allowed_file_type, "application/csv");
    }
if ($allow_vnd_ms_excel == '1')
    {
    array_push($allowed_file_type, "application/vnd.ms-excel");
    }
if ($allow_word == '1')
    {
    array_push($allowed_file_type, "application/msword");
    }

if (!in_array($file_type, $allowed_file_type))
    {
    die("<div class=\"error_custom\">File Type $file_type not allowed.</div>");
    }

//------------------------------------------------------------------
// Make sure our upload directory exists
//------------------------------------------------------------------

if (!file_exists("$upload_dir")) {
echo “<div class=“error_custom”>The required directory $upload_dir does not exist.\n”;
include("./includes/footer.php");
die;
}

//------------------------------------------------------------------
// Get our directory permissions
//------------------------------------------------------------------

$directory_permissions = substr(sprintf(’%o’, fileperms("$upload_dir")), -4);

//------------------------------------------------------------------
// Directory needs enough permissions to delete our csv file after database update
//------------------------------------------------------------------

if ($directory_permissions != ‘0777’) {
echo “<div class=“error_custom”>Incorrect permissions on folder $upload_dir. Permissions are $directory_permissions. They must be 0777.\n”;
include("./includes/footer.php");
die;
}

//------------------------------------------------------------------------------
// Display Uploaded File Details
//------------------------------------------------------------------------------

if ($_FILES["file"]["error"] > 0)
    {
    echo "Error: " . $_FILES["file"]["error"] . "<br />";
    }
else
    {
    /*echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Size: " . $_FILES["file"]["size"] . " Bytes<br />";
    echo "Stored in: " . $_FILES["file"]["tmp_name"];  */
    }

//------------------------------------------------------------------------------

// Temporary file name stored on the server
//$tmpName  = $_FILES['file']['tmp_name'];

//$destination = $upload_dir . DIRECTORY_SEPARATOR . $_FILES["file"]["tmp_name"];
//$destination_path = getcwd().DIRECTORY_SEPARATOR;

$target_path = $upload_dir .DIRECTORY_SEPARATOR. basename($_FILES['file']['name']);

if (move_uploaded_file($_FILES["file"]["tmp_name"], $target_path))
    {
    echo "The file " . basename($_FILES["file"]["name"]) . " has been uploaded.<br>";
    }
else
    {
    die("There was an error uploading your file.");
    }

//------------------------------------------------------------------
// Make sure our csv file exists
//------------------------------------------------------------------

if (!file_exists("$target_path")) {
echo “<div class=“error_custom”>The csv file $target_path does not exist in the directory.\n”;
include("./includes/footer.php");
die;
}

//------------------------------------------------------------------
// Open csv file and make sure row header matches db columns
//------------------------------------------------------------------

$fh = fopen("$target_path", “r”);
$csv_header = fgets($fh);
fclose($fh);

$csv_header=trim($csv_header);

// Tab delimited version
/*
$valid_header=“partner_id scene_network scene_website scene_url_gallery scene_description_short scene_description_long scene_url_thumb scene_number_of_videos scene_number_of_pictures scene_flash scene_tube scene_mobile scene_date_updated scene_model scene_other_model scene_featured scene_status”;
*/
// Pipe delimited version
//$valid_header=“partner_id|scene_network|scene_website|scene_url_gallery|scene_description_short|scene_description_long|scene_url_thumb|scene_number_of_videos|scene_number_of_pictures|scene_flash|scene_tube|scene_mobile|scene_date_updated|scene_model|scene_other_model|scene_featured|scene_status”;

$valid_header=“block_id,lot_number,lot_street_address,active,active_specialty”;

if ($csv_header !=$valid_header) {

echo "<div class=\"error_custom\">The row headers on your file $target_path are named incorrectly.<br /><br />Required headers:<br />$valid_header</div>";

echo "<div class=\"warning\">Your Header:<br />$csv_header</div>\n";

unlink("$target_path");// Delete bad file
include("./includes/footer.php");
exit;

}

//------------------------------------------------------------------
// Backup lot table tab delimited
//------------------------------------------------------------------

// Delete existing file. Mysql wont write file if same name exists
if (file_exists($upload_dir .DIRECTORY_SEPARATOR.‘lot.txt’)) {
unlink($upload_dir .DIRECTORY_SEPARATOR.‘lot.txt’);
}

$lot_text=$upload_dir .DIRECTORY_SEPARATOR.‘lot.txt’;

$sql="SELECT * INTO OUTFILE ‘$lot_text’
FIELDS TERMINATED BY ‘\t’
OPTIONALLY ENCLOSED BY ‘’
ESCAPED BY ‘’
LINES TERMINATED BY ‘\n’
FROM lot
";
/*
$stmt = $pdo->prepare($sql);
$stmt->execute();
*/
//echo $target_path;
//die;
//------------------------------------------------------------------
// Update DB
//------------------------------------------------------------------

//$sql = " LOAD DATA LOCAL INFILE ‘$realpath/$csv_upload_directory_name/$csvfile’ ";
// Galaxy
$sql = " LOAD DATA INFILE ‘$target_path’ ";

//LIVE $sql = " LOAD DATA LOCAL INFILE ‘$target_path’ “;
$sql .= " IGNORE”;
$sql .= " INTO TABLE lot";
$sql .= " FIELDS TERMINATED BY ‘,’ ";
$sql .= " LINES TERMINATED BY ‘\n’ ";
$sql .= " IGNORE 1 LINES "; // Ignores the first X amount of records. Example: 1 would remove header row

$sql .= “(block_id,lot_number,lot_street_address,active,active_specialty)”;

$stmt = $pdo->prepare($sql, array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));// Allows the use of LOAD DATA LOCAL INFILE

$stmt->execute();

//------------------------------------------------------------------
// Delete CSV file
//------------------------------------------------------------------

unlink("$target_path");

//------------------------------------------------------------------
// Show how many records added
//------------------------------------------------------------------

$num = $stmt->rowCount();
echo “<div class=“success”>

Records updated: $num

”; 
}

?>[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service