I am an extreme noob at this so please bear with me. I have this small project where I am trying to upload a csv file and insert it into MySQL.
I have read all the similar posts here and tried out the things that I understood but I am still getting errors :D
<?php
$databasehost = "localhost";
$databasename = "hutchreport";
$databasetable = "intervalreport";
$databaseusername ="root";
$databasepassword = "";
if(isset($_POST['SUBMIT']))
{
$fname = $_FILES['csv_file']['name'];
$chk_ext = explode(".",$fname);
if(strtolower($chk_ext[1]) == "csv")
{
$filename = $_FILES['csv_file']['tmp_name'];
$con = @mysql_connect($databasehost,$databaseusername,
$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());
$sql = "LOAD DATA LOCAL INFILE '$fname'
INTO TABLE intervalreport
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',,,\\
'
IGNORE 1 LINES (intervstartdate, intervstarttime, intervenddate,
intervendtime, loginname, loginnumber,
callsoffered, callsanswered, abandonedcalls,
waittime, staffedtime, auxtime, meeting,
coaching, logintime, inboundtalktime,
avginboundtalktime, inboundacwtime,
avginboundacwtime, inboundhandlingtime,
avginboundhandlingtime, heldcalls,
inboundholdtime, avginboundholdtime,
notreadytime, avgnotreadytime)";
mysql_query($sql) or die(mysql_error());
fclose($handle);
echo "Successfully Imported";
}
else
{
echo "Invalid File";
}
}
?>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' enctype="multipart/form-data" method='post'>
<input type='file' name='csv_file' size='20'>
<input type='submit' name='SUBMIT' value='SUBMIT'>
</form>
</body>
</html>
I am getting the "Can't find file 'Book1.csv'" with this code. Please help!
EDIT: Finally got it to work. Here's the working code:
<html>
<head>
<title>
test
</title>
</head>
<body>
<?php
$databasehost = "localhost";
$databasename = "hutchreport";
$databasetable = "intervalreport";
$databaseusername="root";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "
";
//$csvfile = "csv/Book1.csv";
if(isset($_POST['SUBMIT']))
{
$csvfile = $_FILES['csv_file']['tmp_name'];
move_uploaded_file($csvfile, $csvfile);
if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}
$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."IGNORE 1 LINES (intervstartdate, intervstarttime, intervenddate, intervendtime, loginname, loginnumber, callsoffered, callsanswered, abandonedcalls, waittime, staffedtime, auxtime, meeting, coaching, logintime, inboundtalktime, avginboundtalktime, inboundacwtime, avginboundacwtime, inboundhandlingtime, avginboundhandlingtime, heldcalls, inboundholdtime, avginboundholdtime, notreadytime, avgnotreadytime)");
echo "Loaded a total of $affectedRows records from this csv file.
";
} else { echo "invalid file";}
?>
<form action="" enctype="multipart/form-data" method='post'>
<input type='file' name='csv_file' size='20'>
<input type='submit' name='SUBMIT' value='SUBMIT'>
</form>
</body>
</html>
it would be so much better if have posted an image related to your error!And as php it self has expired the mysql functions it's better to use either PDO or mysqli! i have corrected some part of the codes! please check and let me know if it has helped or not.
<?php
$databasehost = "localhost";
$databasename = "hutchreport";
$databasetable = "intervalreport";
$databaseusername ="root";
$databasepassword = "";
if(isset($_POST['SUBMIT']))
{
$fname = $_FILES['csv_file']['name'];
$chk_ext = explode(".",$fname);
if(strtolower($chk_ext[1]) == "csv")
{
$filename = $_FILES['csv_file']['tmp_name'];
$con = new mysqli($databasehost,$databaseusername,$databasepassword, databasename);
move_uploaded_file($filename, $filename);
$sql = "LOAD DATA LOCAL INFILE {$filename}
INTO TABLE intervalreport
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',,,\\
'
IGNORE 1 LINES (intervstartdate, intervstarttime, intervenddate,
intervendtime, loginname, loginnumber,
callsoffered, callsanswered, abandonedcalls,
waittime, staffedtime, auxtime, meeting,
coaching, logintime, inboundtalktime,
avginboundtalktime, inboundacwtime,
avginboundacwtime, inboundhandlingtime,
avginboundhandlingtime, heldcalls,
inboundholdtime, avginboundholdtime,
notreadytime, avgnotreadytime)";
$con->query($sql);
fclose($handle);
unlink($filename);
echo "Successfully Imported";
}
else
{
echo "Invalid File";
}
}
?>
<form action="" enctype="multipart/form-data" method='post'>
<input type='file' name='csv_file' size='20'>
<input type='submit' name='SUBMIT' value='SUBMIT'>
</form>
</body>
</html>
the thing i have done is i have uploaded file and save it to the parent directory! and then read the file and when the import thing has finished i have deleted the file!
hope it works