我该如何将CSV文件导入MySQL表中?我希望使用数据的第一行作为列名。
我阅读了如何将CSV文件导入MySQL表中?,但唯一的答案是使用GUI而不是shell?
听起来很费力,但使用Emacs很容易...
我尝试使用Hawkee提供的脚本,但其中一些命令已经过时。使用mysql_X已被弃用,需要替换为mysqli_x。经过一些故障排除后,我编写了以下脚本,并且它运行得很好。
请注意:以下代码假定您输入的是浮点数。我使用此脚本从WHO导入百分位数,以进行与增长相关的统计。
如果要删除表,请在文件名之前使用-drop。
<?php
//This script is for importing the percentile values.
//Written by Daniel Pflieger @ GrowlingFlea Software
$host = 'localhost';
$user = 'root';
$pass = '';
$database = '';
//options. This is what we need so the user can specify whether or not to drop the table
$short_options = "d::";
$options = getopt($short_options);
//check if the flag "-drop" is entered by the end user.
if (!empty($options) && $options['d'] != "rop"){
echo "The only available argument is -drop \n";
exit;
} else if (!empty($options)){
$dropTable = true;
} else {
$dropTable = false;
}
//we use mysqli_* since this is required with newer versions of php
$db = mysqli_connect($host, $user, $pass, $database);
// argv changes if the drop flag is used. here we read in the name of the .csv file we want to import
if (isset($argv[1]) && empty($options) ) {
$file = $argv[1];
} else if (isset($argv[2]) && $options[1] = "rop" ) {
$file = $argv[2];
}
//we call the table name the name of the file. Since this script was used to import who growth chart info
//I appended the '_birth_to_5yrs' to the string. You probably want to remove this and add something that
//makes sense to you
$table = pathinfo($file);
$table = "who_" . $table['filename'] . "_birth_to_5yrs";
$table = str_replace('-', '_', $table);
// We read the first line of the .csv file. It is assumed that these are the headers.
$fp = fopen($file, 'r');
$frow = fgetcsv($fp);
$columns = '';
//we get the header names and for this purpose we make every value 'float'. If you are unsure of
//the datatype you can probably use varchar(250).
foreach($frow as $column) {
$columns .= "`" .$column . "` float,";
}
//drop the table to prevent data issues, if that is what the end user selects
if ($dropTable) {
mysqli_query($db, "drop table if exists $table");
}
// here we form the create statement and we create the table.
// we use the mysqli_real_escape_string to make sure we dont damage the DB
$create = "create table if not exists $table ($columns);";
$create = str_replace(',)', ')', $create);
$create = mysqli_real_escape_string($db, $create);
mysqli_query($db, $create);
// We read the values line-by-line in the .csv file and insert them into the table until we are done.
while ($frow = fgetcsv($fp)){
$insert = implode(", ", $frow);
$insert = "Insert into $table VALUES ( $insert )";
$insert = mysqli_real_escape_string($db, $insert);
$insert = mysqli_query($db, $insert);
}
运行脚本的示例:
php ../git/growlingflea-dev-tools/importCSV.php -drop wfh-female-percentiles-expanded-tables.csv
将CSV文件导入到mysql表中
LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Character Escape Sequence
\0 An ASCII NUL (0x00) character
\b A backspace character
\n A newline (linefeed) character
\r A carriage return character
\t A tab character.
\Z ASCII 26 (Control+Z)
\N NULL
访问量: http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html
我已经谷歌搜索了许多导入csv到mysql的方法,包括"load data infile"、使用mysql workbench等。
当我使用mysql workbench的导入按钮时,首先需要自己创建空表,并自行设置每个列类型。注意:您必须在末尾添加ID列作为主键和非空自动增量,否则导入按钮将在后面不可见。然而,当我开始加载CSV文件时,没有任何加载,似乎是一个bug。我放弃了。
幸运的是,到目前为止我发现最好的简单方法是使用Oracle的mysql for excel。您可以从这里下载mysql for excel
这是你要做的: 在Excel中打开CSV文件,在数据选项卡中找到mysql for excel按钮
选择所有数据,点击导出到mysql。 注意设置一个ID列作为主键。
完成后,转到mysql workbench以更改表, 例如货币类型应为decimal(19,4)用于大额小数(10,2)用于常规使用。 其他字段类型可以设置为varchar(255)。
LOAD DATA INFILE
,可以用来读取和格式化CSV文件。它非常强大,但文档不是很完善。这篇文章讲解了如何使用它:https://blog.terresquall.com/2021/11/importing-a-csv-file-into-an-sql-table/ - John Doe