Write a list of lists into csv and having UnicodeEncodeError in python-CodePudding

I am converting an xlsm file to csv using the openpyxl library. I have the problem that when I try to pass the list of lists to the csv python returns the following error:

Traceback (most recent call last): File "test.py", line 21, in listIntoCsv(finalList) File "test.py", line 16, in listIntoCsv write.writerows(list) UnicodeEncodeError: 'ascii' codec can't encode character u'\xfa' in position 20: ordinal not in range(128)

These are 2 example of 1 list inside the final list:

[[u'[email protected]', u'[email protected], [email protected]', datetime.datetime(2022, 7, 18, 10, 7, 16), 1L, '0', 1L, 2L, 'NO', None, '[email protected]', '[email protected]', None, None, False, False, None, None, False, False, False, None, None, True, 'SI', 'N/A', 3182L, 0L, None, None, None, '#N/A', 'RE: NHYJIJJP< >LWWM', u'a7d2a497-e4f1-40b2-8dc8-699b270bbcd4', u'Comentario: \xd1<GRC?SST"&"\\ A', None], [u'[email protected]', u'[email protected], [email protected]', datetime.datetime(2022, 7, 18, 9, 3, 49), 1L, 'MMUN19', 1L, 2L, 'NO', None, '[email protected]', '[email protected]', None, None, False, False, None, None, False, False, False, None, None, True, 'SI', 'N/A', 'SI', 'N/A', None, None, None, 'DGT Madrid', 'RE: NIPGGVJK< >FJRQ', u'2cd7a6f2-4ce4-4678-b592-bff465f9f411', u'Comentario: \xd1@GHJ<FCX"&"\\ A', None]]

I am executing the code with python2. I have tried to apply different solutions so that the code looks like this:

from openpyxl import load_workbook
import csv
import codec

  excelFile = load_workbook('test.xlsm', data_only=True)
  currentSheet = excelFile.worksheets[0]
  
  def iter_rows(currentSheet):
    for row in currentSheet.iter_rows():
      yield [cell.value for cell in row]

  def listIntoCsv(list):
    with codecs.open('test','w',encoding='UTF-8') as f:
      write = csv.writer(f)
      write.writerows(list)
  
  finalList = list(iter_rows(currentSheet))
  print(finalList)
  listIntoCsv(finalList)

CodePudding user response：

from types import NoneType
import unicodedata
from openpyxl import load_workbook
import csv
from datetime import datetime
import os
     
  def iterRows(currentSheet):
    for row in currentSheet.iter_rows():
      yield [cell.value for cell in row]

  def listIntoCsv(list):
    with open('excel.csv','w') as f:
      write = csv.writer(f)
      write.writerows(list)
  
  def encodeList(list):
    for x,y in enumerate(list):
      for j,element in enumerate(y):

        if isinstance(element,unicode):
          element = unicodedata.normalize('NFKD', element).encode('ascii', 'ignore')
          list[x][j] = element

        elif isinstance(element,datetime):
          element = element.strftime('%d/%m/%Y-%H:%M:%S')
          list[x][j] = element

        elif isinstance(element,long):
          element = str(element)
          list[x][j] = element

        elif isinstance(element,NoneType):
          element = str(element).replace('None','Nada')
          list[x][j] = element

        elif isinstance(element,bool):
          element = str(element)
          list[x][j] = element

        else:
          list[x][j] = element
          
    return list

  ubicationExcel = 'bin/mailList/Investigacion_DLP_enmascarado.xlsm'
  excelFile = load_workbook(ubicationExcel, data_only=True)
  currentSheet = excelFile.worksheets[0]
  dirtyList = list(iterRows(currentSheet))
  finalList = encodeList(dirtyList)
  listIntoCsv(finalList)