*jika isi struktur table di MySQL sama dengan yang di CSV maka tinggal melakukan perintah berikut :
* jika strukturnya berbeda maka harus dilakukan dengan script php :
example :
$ext="";
//generate unique file name using time:
$newfilename= md5(rand() * time());
//do we have a file?
if((!empty($_FILES["file"])) && ($_FILES['file']['error'] == 0))
{
echo "Upload: " . $_FILES["file"]["name"] . "";
echo "Type: " . $_FILES["file"]["type"] . "";
if($_FILES["file"]["size"] >= 1024) echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb"; else echo"Size: " . ($_FILES["file"]["size"]) . " Kb";
if (file_exists("jadwal/" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}else{
$filename =strtolower(basename($_FILES['file']['name']));
$ext = substr($filename, strrpos($filename, '.') + 1);
if ((($ext == "csv")||($ext == "CSV") || ($ext == "txt") || ($ext == "TXT") || ($ext == "sql") || ($ext == "SQL"))){
//Determine the path to which we want to save this file
$ext=".".$ext;
$newname = dirname(__FILE__).'/upload/'.$newfilename.$ext;
if ((move_uploaded_file($_FILES['file']['tmp_name'],$newname)))
{
echo "File uploaded successfully!";
echo "Stored in: " . $newname . "";
// di sini perintah untuk mengimport data csv ke mysql
if (($handle = fopen($newname, "r")) !== FALSE) {
# Set the parent multidimensional array key to 0.
$nn = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
# Count the total keys in the row.
$c = count($data);
# Populate the multidimensional array.
for ($x=0;$x<$c;$x++)
{
$data[$x] = str_replace('"','',$data[$x]);
$array[] = explode(";",$data[$x]);
}
$nn++;
}
# Close the File.
fclose($handle);
}
foreach($array as $b){
$sql="INSERT into table_name(field1,field2,field3,field4,field5,field6,field7,date) values('$b[3]','$b[4]','$b[5]','$b[6]','$b[7]','$b[8]','$b[9]','$b[0]-$b[1]-$b[2]')";
$ex = mysql_query($sql) or die(mysql_error());
}
}
}
}
}
}
by devarizal
dengan cara load data infile : LOAD DATA INFILE '/path/to/csvFile.csv' INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';"
* jika strukturnya berbeda maka harus dilakukan dengan script php :
example :
$ext="";
//generate unique file name using time:
$newfilename= md5(rand() * time());
//do we have a file?
if((!empty($_FILES["file"])) && ($_FILES['file']['error'] == 0))
{
echo "Upload: " . $_FILES["file"]["name"] . "";
echo "Type: " . $_FILES["file"]["type"] . "";
if($_FILES["file"]["size"] >= 1024) echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb"; else echo"Size: " . ($_FILES["file"]["size"]) . " Kb";
if (file_exists("jadwal/" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}else{
$filename =strtolower(basename($_FILES['file']['name']));
$ext = substr($filename, strrpos($filename, '.') + 1);
if ((($ext == "csv")||($ext == "CSV") || ($ext == "txt") || ($ext == "TXT") || ($ext == "sql") || ($ext == "SQL"))){
//Determine the path to which we want to save this file
$ext=".".$ext;
$newname = dirname(__FILE__).'/upload/'.$newfilename.$ext;
if ((move_uploaded_file($_FILES['file']['tmp_name'],$newname)))
{
echo "File uploaded successfully!";
echo "Stored in: " . $newname . "";
// di sini perintah untuk mengimport data csv ke mysql
if (($handle = fopen($newname, "r")) !== FALSE) {
# Set the parent multidimensional array key to 0.
$nn = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
# Count the total keys in the row.
$c = count($data);
# Populate the multidimensional array.
for ($x=0;$x<$c;$x++)
{
$data[$x] = str_replace('"','',$data[$x]);
$array[] = explode(";",$data[$x]);
}
$nn++;
}
# Close the File.
fclose($handle);
}
foreach($array as $b){
$sql="INSERT into table_name(field1,field2,field3,field4,field5,field6,field7,date) values('$b[3]','$b[4]','$b[5]','$b[6]','$b[7]','$b[8]','$b[9]','$b[0]-$b[1]-$b[2]')";
$ex = mysql_query($sql) or die(mysql_error());
}
}
}
}
}
}
by devarizal