a6april commented on a Page, CSV to mySQL - Create Table and Insert Data - Nov 08, 2012

How do I add a browse button for this?

Lets say I need to select the file can I use something like:

//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");

With a form like this:

Choose your file:

I tried using the script as is and I always get the error:
'Please provide a file name'

I would really like to get this working so any additional information would be greatly appreciated

4 Responses

tekagami - Oct 31, 2014

How about:

$uploadfile = basename($_FILES['csv']['name']);

if ($uploadfile != "") {
$file= $uploadfile;
move_uploaded_file($_FILES['csv']['tmp_name'], "/path/to/".$file);
}

Hawkee - Oct 31, 2014

@tekagami Be sure to wrap your code with ``` to ensure it shows up properly.

acosonic - Aug 26, 2015

Well the script kind'a makes few gltiches on windoze... To fix it
remove notices by:
line 14 replace$_SERVER[argv]; with $_SERVER['argv'];
After line 30 $frow = fgetcsv($fp);
Add $columns=false;
And line 42 now fix path (Windows only though) like this

$file = addslashes(realpath(dirname(__FILE__)).'\\'.$file);
For Linodoze just use / instead of \ ...

THANKS!

gnovaro - Nov 04, 2015

I made some modification to improve your code, this is my version

<?php
/**
 * http://hawkee.com/snippet/8320/
 * 
 * Installation
 * ------------
 * People who are using mysql version 5 and above.
 *
 * Before loading the file into mysql must ensure that below tow line are added in side etc/mysql/my.cnf
 *
 * to edit my.cnf command is 
 * sudo vi /etc/mysql/my.cnf
 *
 * [mysqld]  
 * local-infile
 *
 * [mysql]  
 * local-infile 
 * 
 * Example manual run from console
 * mysql -u root -proot
 * mysql use base;
 * load data local infile '/home/gustavo/www/maps-importer/hotel_all_active_09-10-14.csv' into table hotel_data fields terminated by ';' ignore 1 lines;
 */
error_reporting(E_ALL);
$host = 'localhost';
$user = 'root';
$pass = 'root';
$database = 'mapshotels';
$delimiter = ';'; //By default ,

$db = mysqli_connect($host, $user, $pass,$database);

if (!$db) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

/********************************************************************************/
// Parameters: filename.csv table_name

$argv = $_SERVER['argv'];

if($argv[1]) { $file = $argv[1]; }
else {
    echo "Please provide a file name\n"; exit; 
}
if($argv[2]) { $table = $argv[2]; }
else {
    $table = pathinfo($file);
    $table = $table['filename'];
}

/********************************************************************************/
// Get the first row to create the column headings

$fp = fopen($file, 'r');
$frow = fgetcsv($fp,0,$delimiter);
$columns = '';
foreach($frow as $column) {
    if($columns) $columns .= ', ';
    $columns .= "`$column` varchar(191)"; //Max varchar for utf8mb4!!
}

$create = "create table if not exists $table ($columns);";
//Debug
echo $create."\n";
mysqli_query($db,$create);

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].DIRECTORY_SEPARATOR.$file;
$q = "load data local infile '$file' into table $table fields terminated by '$delimiter' ignore 1 lines";
//Debug
echo $q."\n";
mysqli_query($db,$q);