MySQL 将 Excel 工作表导入 phpMyAdmin
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32626679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing Excel sheet into phpMyAdmin
提问by Dannys
I'm currently playing around with phpMyAdmin and I have encountered a problem. When importing my CSV into phpMyAdmin it's rounding the numbers. I have set the column to be a float and the column in Excel to be a Number (Also tried text/General) to no avail. Has anyone else encountered this issue and found a viable work-around?
我目前正在使用 phpMyAdmin，但遇到了一个问题。将我的 CSV 导入 phpMyAdmin 时，它会将数字四舍五入。我已将列设置为浮点数，将 Excel 中的列设置为数字（也尝试过文本/常规），但无济于事。有没有其他人遇到过这个问题并找到了可行的解决方法？
A second question, is it possible for me to upload the CSV file so that it matches the column names in phpMyAdmin to Excel column names and enters the data in the correct column?
第二个问题，我是否可以上传 CSV 文件，以便将 phpMyAdmin 中的列名与 Excel 列名相匹配并在正确的列中输入数据？
回答by Sauron
- Your file should be look like this(decimal fields are of general type):
- 您的文件应如下所示（十进制字段为一般类型）：
- Save as CSV. File will be probably saved with ; separated
- 另存为 CSV。文件可能会保存为 ; 分开
This is for new table:
这是新表：
- Open phpMyAdmin, choose your database, click to import and select file to upload
- Change format to CSV if there is not selected
- Change in format specific options - columns separated with: ;
- Be sure that checkbox (The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)) is SELECTED
- Click Go
- New table will be created with the structure according to the forst line in CSV.
- 打开phpMyAdmin，选择你的数据库，点击导入并选择要上传的文件
- 如果未选择，请将格式更改为 CSV
- 更改格式特定选项 - 列分隔： ;
- 确保复选框（文件的第一行包含表列名（如果未选中，第一行将成为数据的一部分））是 SELECTED
- 点击前往
- 将根据 CSV 中的 forst 行使用结构创建新表。
This is for existing table:
这是针对现有表：
- Open phpMyAdmin, choose your database, CHOOSE YOUR TABLEwhich match the structure of imported file, click to import and select file to upload
- Change format to CSV if there is not selected
- Change in format specific options - columns separated with: ;
- Change skip number of queries to 1 (this will skip the first line with column names)
- Click Go
- Selected table wich has the same structure as CSV will be updated and rows in CSV inserted.
- 打开phpMyAdmin，选择你的数据库，选择与导入文件结构匹配的表，点击导入并选择要上传的文件
- 如果未选择，请将格式更改为 CSV
- 更改格式特定选项 - 列分隔： ;
- 将跳过查询数更改为 1（这将跳过带有列名的第一行）
- 点击前往
- 与 CSV 具有相同结构的选定表将被更新并插入 CSV 中的行。
回答by Harsh Mathur
// connecting dB
$mysqli = new mysqli('localhost','root','','testdB');
// opening csv
$fp = fopen('data.csv','r');
// creating a blank string to store values of fields of first row, to be used in query
$col_ins = '';
// creating a blank string to store values of fields after first row, to be used in query
$data_ins = '';
// read first line and get the name of fields
$data = fgetcsv($fp);
for($field=0;$field< count($data);$field++){
$col_ins = "'" . $col[$field] . "' , " . $col_ins;
}
// reading next lines and insert into dB
while($data=fgetcsv($fp)){
for($field=0;$field<count($data);$field++){
$data_ins = "'" . $data[$field] . "' , " . $data_ins;
}
$query = "INSERT INTO `table_name` (".$col_ins.") VALUES(".$data_ins.")";
$mysqli->query($query);
}
echo 'Imported...';
回答by Thomas Jr
I've had the same issue. Solved changing the separator between the integer part and the decimal part from comma to point.
我有同样的问题。解决了将整数部分和小数部分之间的分隔符从逗号更改为点的问题。
i.e. 365,40 to 365.40
即 365,40 到 365.40
That worked for me.
那对我有用。
