时间戳

首页 > 解决方案 > Python 中的 CSV 到 XLSX

python - Python 中的 CSV 到 XLSX

问题描述

我正在尝试使用 python 2.7 版本将 csv 转换为 xlsx 但它给了我类似的错误

Traceback (most recent call last):
  File "C:/Users/XYZ/Desktop/converter.py", line 13, in <module>
    ws.cell(row=r, column=c).value = val
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 291, in value
    self._bind_value(value)
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 190, in _bind_value
    value = self.check_string(value)
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 149, in check_string
    value = unicode(value, self.encoding)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x93 in position 1037: invalid start byte

我的代码是

import os
import glob
import csv
import openpyxl 
for csvfile in glob.glob(os.path.join('.', 'i.csv')):
    wb = openpyxl.Workbook()
    ws = wb.active
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

下面是 CSV 文件名 i.csv AFFECTED PLATFORM列名和 1 行单元格数据。

"•  Adobe Photoshop CC 2018 version 19.1.3 and earlier. <br />
•   Adobe Photoshop CC 2017 version 18.1.3 and earlier for mac OS.<br />
•   Adobe Photoshop CC 2017 version 18.1.2 and earlier for Windows.<br />
•   Adobe Acrobat DC Consumer version 2018.011.20038 and earlier.<br />
•   Adobe Acrobat Reader DC Consumer version 2018.011.20038 and earlier.    <br />
•   Adobe Acrobat 2017 Classic 2017 version 2017.011.30079 and earlier. <br />
•   Adobe Acrobat Reader 2017 Classic 2017 version 2017.011.30079 and earlier.  <br />
•   Adobe Acrobat DC Classic 2015 version 2015.006.30417 and earlier. <br />
•   Adobe Acrobat Reader DC Classic 2015 version 2015.006.30417 and earlier.<br />
"

标签： pythonpython-2.7csvopenpyxlxlsx

解决方案

您可能使用 Windows 编码保存了文件，因此 unicodecsv 库可以按如下方式使用：

import os
import glob
import unicodecsv as csv
import openpyxl 

for csvfile in glob.glob(os.path.join('.', 'i.csv')):
    wb = openpyxl.Workbook()
    ws = wb.active
    print csvfile
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f, encoding='windows-1252')
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

但是，对于您提供的单元格，这将导致单个单元格包含多行。如果您想将此类条目转换为多个单元格，则需要进一步处理。为此，需要一个更好的示例 CSV 文件。

例如，以下内容可用于删除 HTML<br />标记：

ws.cell(row=r, column=c).value = val.replace('<br />', '')

推荐阅读