Python 将 Excel 转换为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26029095/
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
Python Convert Excel to CSV
提问by MrBubbles
Seems there are a lot of posts on this subject and my solution is in line with what the most common answer seems to be, however I'm encountering an encoding error that I don't know how to address.
似乎有很多关于这个主题的帖子，我的解决方案与最常见的答案一致，但是我遇到了一个我不知道如何解决的编码错误。
>>> def Excel2CSV(ExcelFile, SheetName, CSVFile):
import xlrd
import csv
workbook = xlrd.open_workbook(ExcelFile)
worksheet = workbook.sheet_by_name(SheetName)
csvfile = open(CSVFile, 'wb')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in xrange(worksheet.nrows):
wr.writerow(worksheet.row_values(rownum))
csvfile.close()
>>> Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations",
r"C:\Temp\StoreList.csv")
Traceback (most recent call last):
File "<pyshell#2>", line 1, in <module>
Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations", r"C:\Temp\StoreList.csv")
File "<pyshell#1>", line 10, in Excel2CSV
wr.writerow(worksheet.row_values(rownum))
UnicodeEncodeError: 'ascii' codec can't encode character u'\xa0' in position 14:
ordinal not in range(128)
>>>
Any help or insight is greatly appreciated.
非常感谢任何帮助或见解。
采纳答案by Rob?
As @davidism points out, the Python 2 csvmodule doesn't work with unicode. You can work around this by converting all of your unicodeobjects to strobjects before submitting them to csv:
正如@davidism 指出的那样，Python 2csv模块不适用于 unicode。您可以通过在将所有对象提交unicode到str对象之前将它们转换为对象来解决此问题csv：
def Excel2CSV(ExcelFile, SheetName, CSVFile):
import xlrd
import csv
workbook = xlrd.open_workbook(ExcelFile)
worksheet = workbook.sheet_by_name(SheetName)
csvfile = open(CSVFile, 'wb')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in xrange(worksheet.nrows):
wr.writerow(
list(x.encode('utf-8') if type(x) == type(u'') else x
for x in worksheet.row_values(rownum)))
csvfile.close()
回答by davidism
The Python 2 csvmodule has some problems with unicode data. You can either encode everything to UTF-8 before writing, or use the unicodecsvmodule to do it for you.
Python 2csv模块在处理 unicode 数据方面存在一些问题。您可以在编写之前将所有内容编码为 UTF-8，也可以使用该unicodecsv模块为您完成。
First pip install unicodecsv. Then, instead of import csv, just import unicodecsv as csv. The API is the same (plus encoding options), so no other changes are needed.
首先pip install unicodecsv。然后，而不是import csv，只是import unicodecsv as csv。API 是相同的（加上编码选项），因此不需要其他更改。
回答by evinhas
Another fashion for doing this: cast to string, so as you have a string, you may codify it as "utf-8".
执行此操作的另一种方式：强制转换为字符串，因此当您有一个字符串时，您可以将其编码为“utf-8”。
str(worksheet.row_values(rownum)).encode('utf-8')
The whole function:
整个功能：
def Excel2CSV(ExcelFile, SheetName, CSVFile):
import xlrd
import csv
workbook = xlrd.open_workbook(ExcelFile)
worksheet = workbook.sheet_by_name(SheetName)
csvfile = open(CSVFile, 'wb')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
for rownum in xrange(worksheet.nrows):
wr.writerow(str(worksheet.row_values(rownum)).encode('utf-8'))
csvfile.close()
