将 SQL 文件导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3820898/
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
Export SQL file into Excel
提问by 109221793
I have a sql file which has a lot of insert statements (1000+),
我有一个 sql 文件，里面有很多插入语句（1000+），
e.g.
例如
insert into `pubs_for_client` (`ID`, `num`, `pub_name`, `pub_address`, `pub_tele`, `pub_fax`, `pub_email`, `publ_website`, `publ_vat`, `pub_last_year`, `titles_on_backlist`, `Personnel`) values('2475','2473','xxx xxx xxx','xxx xxx, xxxx, xxxx, xxxx, ','000000 ','0000 ',NULL,NULL,NULL,NULL,NULL,NULL);
My client wants these in an excel file.
我的客户想要这些在 excel 文件中。
Is it possible to extract the information from this sql file and import it into an excel spreadsheet? I'm using excel 2007.
是否可以从此sql文件中提取信息并将其导入excel电子表格？我正在使用 excel 2007。
回答by cweiske
If you have a mysql installation somewhere and phpMyAdmin, just import the .sql file and export the table as .xls file directly from within phpMyAdmin.
如果您在某处安装了 mysql 和 phpMyAdmin，只需导入 .sql 文件并将表直接从 phpMyAdmin 中导出为 .xls 文件。
回答by codeulike
edit: If you want to extract the data from the Insert statements without actually running them, you could:
编辑：如果您想从 Insert 语句中提取数据而不实际运行它们，您可以：
- Save the SQL file as a CSV file
- Open it in Excel and it will automatically split the statements up into bits using the commas
- Most of the columns to the left of the data wont be worth keeping, so delete them
- The right-most columns should contain the data
- 将 SQL 文件另存为 CSV 文件
- 在 Excel 中打开它，它会使用逗号自动将语句拆分为位
- 数据左侧的大多数列都不值得保留，因此请删除它们
- 最右边的列应该包含数据
回答by Mulki
If you are not keen on installing any tools you could try this...its simple but needs some regex matching with trial and error for strings.
如果你不热衷于安装任何工具，你可以试试这个......它很简单，但需要一些正则表达式匹配字符串的反复试验。
- Copy over the column names to excel
- Open the file with the insert statements and hit replace all. Insert into pubs_for_client (ID, num, pub_name, pub_address, pub_tele, pub_fax, pub_email, publ_website, publ_vat, pub_last_year, titles_on_backlist, Personnel) values( with nothing.
- Find )\n with wild card and remove all of them.
- Now this text file is ready to be imported to excel. You can copy it over to clipboard and then to excel or open with excel and use.
- 复制列名到excel
- 使用插入语句打开文件并点击全部替换。插入 pubs_for_client（ID、num、pub_name、pub_address、pub_tele、pub_fax、pub_email、publ_website、publ_vat、pub_last_year、titles_on_backlist、Personnel）值（什么都没有。
- 使用通配符查找 )\n 并删除所有这些。
- 现在此文本文件已准备好导入到 Excel 中。您可以将其复制到剪贴板，然后将其复制到 excel 或使用 excel 打开并使用。
Side Note : you could also try asking your client to send it in excel henceforth its usually available in most data providers.
旁注：您也可以尝试要求您的客户将其发送到 excel 中，此后通常在大多数数据提供程序中可用。
回答by Rup
You mean export the values?
你的意思是导出值？
Assuming you're using the SQL server + tools the simplest method would be to
假设您使用的是 SQL 服务器 + 工具，最简单的方法是
- actually insert those values into a table (creating a temp table if necessary)
select * from pubs_for_client- copy + paste the results table into Excel.
- 实际上将这些值插入到表中（如有必要，创建一个临时表）
select * from pubs_for_client- 将结果表复制并粘贴到 Excel 中。
You'll have to set the column widths yourself, and if you have number-as-a-string columns you want to keep as strings (e.g. to preserve leading zeroes) then you'll need to set the receiving column type first.
您必须自己设置列宽，如果您想将数字作为字符串列保留为字符串（例如保留前导零），那么您需要先设置接收列类型。
If not, and if you can't use similar tools for your database, you can use regexps or just use your text editor to convert your SQL file into a CSV which Excel will read. You'd also have to edit out quotes from values and nulls and doubled-to-quote apostrophes. Unfortunately you can't then specify column types in advance and you would e.g. lose leading zeros on numbers.
如果没有，并且您不能为您的数据库使用类似的工具，您可以使用正则表达式或仅使用文本编辑器将您的 SQL 文件转换为 Excel 将读取的 CSV。您还必须从值和空值以及双引号撇号中删除引号。不幸的是，您无法提前指定列类型，并且您会丢失数字的前导零。
