该电子表格类似于 this 我的'tbl_foot_teams'表如下所示:
id | name | rating
简单地说,我需要将电子表格中的两列输入到数据库字段名称和评级中。
有没有有效的方法来实现这一点?一个个输入将花费我荒谬的时间!
谢谢
将Excel文件另存为CSV格式,并使用LOAD DATA INFILE命令导入数据。
您的Excel文件中没有id
字段。请在表中创建id
字段并设置为自动增量,然后使用以下命令 -
LOAD DATA INFILE 'file_name.csv' INTO TABLE tbl_foot_teams
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
-- IGNORE 1 LINES -- if csv file has column headers
(name, rating)
SET id = NULL; -- this will set unique value for each row
您没有指定使用的数据库,但是使用MySQL实现此目的的简单方法是将电子表格导出为csv文件,然后使用mysqlimport导入到MySQL中。
用户Philippe Jausions在this MySQL page的评论中描述了这一过程:
If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows command/DOS prompt, try the following:
mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv
Between quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...
I usually run this command from the folder containing the YOUR_TABLE.csv file.
If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)
If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""
将文件转换为CSV格式后,您可以将其导入到支持从CSV文件导入的任何数据库中(如MySQL、PostgreSQL等),但您需要通过命令行执行以下操作:
您可以使用编程语言,例如Python,并使用其库,例如Excel电子表格读取库,然后再使用与SQL数据库进行交互的库。
您连接到数据,加载Excel文件,并循环遍历每一行并提取您想要的任何列数据。然后,您将该数据执行INSERT
语句。
如果您在服务器上安装了phpMyAdmin,则可以使用从CSV导入选项,但您首先必须将Excel电子表格另存为CSV(逗号分隔值)文件。
从Excel端开始工作,您可以使用ADO,例如Excel VBA:写入mysql数据库
Dim cn As ADODB.Connection
''Not the best way to get the name, just convenient for notes
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
''For this to work, you must create a DSN and use the name in place of
''DSNName, however, you can also use the full connection string
strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
& "Select AnyField As NameOfMySQLField FROM [Sheet1$];"
cn.Execute strSQL