我在Mysql中创建了一个具有表名(CSV文件名)和字段名(CSV列名)的数据结构。
现在我正在成功地将数据从csv导入Mysql表,因为我正在脚本中硬编码csv文件名和字段名。如何动态获取bec我有manny csv文件要导入到mysql中。
<?php
include "db.php";
$filename = "C:'REQ'Status.csv";
if (($handle = fopen($filename, 'r')) !== FALSE)
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
print_r($data);
$import="INSERT into status(status) values('$data[1]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
?>
我已经实现了这段代码,它是经过测试的代码。我认为这是非常有用的全
你必须遵循一些规则:-
1.根据数据库表名创建csv文件(例如:数据库表名为users,csv应为users.csv)
2.您的csv文件的第一行应该是启动数据输入后的数据库表字段名称(例如:Id、名称等)
3.您可以从以下位置下载数据源类:-http://code.google.com/p/php-csv-parser/因为我在下面的代码中有require_one'CSV/DataSource.php';
<?php
ini_set('memory_limit','512M');
$dbhost = "localhost";
$dbname = "excel_import";
$dbuser = "root";
$dbpass = "";
$conn=mysql_connect ($dbhost, $dbuser, $dbpass) or die ("I cannot connect to the database because: " . mysql_error());
mysql_select_db($dbname) or die("Unable to select database because: " . mysql_error());
require_once 'CSV/DataSource.php';
$filename = "users.csv";
$ext = explode(".",$filename);
$path = "uploads/".$filename;
$dbtable = $ext[0];
import_csv($dbtable, $path);
function import_csv($dbtable, $csv_file_name_with_path)
{
$csv = new File_CSV_DataSource;
$csv->load($csv_file_name_with_path);
$csvData = $csv->connect();
$res='';
foreach($csvData as $key)
{
$myKey ='';
$myVal='';
foreach($key as $k=>$v)
{
$myKey .=$k.',';
$myVal .="'".$v."',";
}
$myKey = substr($myKey, 0, -1);
$myVal = substr($myVal, 0, -1);
$query="insert into ".$dbtable." ($myKey)values($myVal)";
$res= mysql_query($query);
}
if($res ==1)
{
echo "record successfully Import.";
}else{
echo "record not successfully Import.";
}
}
类似这样的东西:
function integrate($filename)
{
if (($handle = fopen($filename, 'r')) !== FALSE)
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
print_r($data);
$import="INSERT into status(status) values('$data[1]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
}
$files = scandir('folder/');
foreach($files as $file) {
//pre-checking (if already integrated, if CSV etc..)
integrate($file);
}
如果您将其作为PHP脚本运行,则可以使用$argv[1]
获取文件名作为参数。不过,您需要检查文件是否存在。
如果文件名和表名匹配,那么无论在哪里输入或检索文件名,都可以在查询中使用相同的值:
$filename = "Status";
$table_name = strtolower( $filename );
$filename = "C:'REQ'{$filename}.csv";
if (($handle = fopen($filename, 'r')) !== FALSE)
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
print_r($data);
$import="INSERT into {$table_name}(status) values('$data[1]')";
对于列名,我建议将它们放在CSV文件的第一行中,然后从那里进行解析,例如:
if (($handle = fopen($filename, 'r')) !== FALSE)
$columns = fgets($handle, 1000);
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
print_r($data);
$import="INSERT into {$table_name}({$columns}) values('{$data[1]}')";
我还建议考虑为多行INSERT构建一个SQL查询字符串。例如
$values = array();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
print_r($data);
$values[] = "('{$data[1]}')";
...
}
$values = join( ",'n'n", $values );
$insert = "INSERT into {$table_name}({$columns}) values {$values}";