0

im trying to archive the following:

input: xls file output: csv file

I want to read the xls and do some manipulations (rewrite the headers (original: customernumer, csv needs Customer_Number__c), removing some columns, etc.

Right now I'm already reading the xls and try to write as csv (without any manipulations), but I'm struggling because of the coding. The original file contains some "special" characters like "/", "\", and most impoartant "ä, ü, ö, ß".

I get the following error:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe4' in position 8: ordinal not in range(128)

I have no clue which special characters can be in a file, this changes from time to time.

here is my current sandbox code:

    # -*- coding: utf-8 -*-
__author__ = 'adieball'


import xlrd
import csv
from os import sys
import argparse

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("inname", type=str,
                        help="Names of the Input File in single quotes")

    parser.add_argument("--outname", type=str,
                        help="Optional enter the name of the output (csv) file. if nothing is given, "
                             "we use the name of the input file and add .csv to it")

    args = parser.parse_args()

    if args.outname is None:
        outname = args.inname + ".csv"
    else:
        outname = args.outname



    wb = xlrd.open_workbook(args.inname)
    xl_sheet = wb.sheet_by_index(0)
    print args.inname
    print ('Retrieved worksheet: %s' % xl_sheet.name)
    print outname



    output = open(outname, 'wb')
    wr = csv.writer(output, quoting=csv.QUOTE_ALL)

    for rownum in xrange(wb.sheet_by_index(0).nrows):
        wr.writerow(wb.sheet_by_index(0).row_values(rownum))

    output.close()

anything I can do here to make sure these special characters get written to the csv in the same way as they appeared in the original xls?

thanks

andre

f0rd42
  • 1,429
  • 4
  • 19
  • 30

4 Answers4

2

a simple

from os import sys reload(sys) sys.setdefaultencoding("utf-8")

did the trick

Andre

f0rd42
  • 1,429
  • 4
  • 19
  • 30
0

You could convert the script to Python 3, and then set the write mode when opening the the output file to "w" instead to write Unicode. Not trying to evangelize, but Python 3 makes this sort of thing easier. If you wanna stay with Python 2 checkout this guide: https://docs.python.org/2/howto/unicode.html

0

If you want to write a utf-8 encoded file, you have to use the codecs.open. Try this small example:

o1 = open('/tmp/o1.txt', 'wb')
try:
    o1.write(u'\u20ac')
except Exception, exc:
    print exc
o1.close()

import codecs
o2 = codecs.open('/tmp/o2.txt', 'w', 'utf-8')
o2.write(u'\u20ac')
o2.close()
return42
  • 543
  • 3
  • 10
0

Why not using UnicodeWriter class as in examples in csv doc https://docs.python.org/2/library/csv.html#examples . I think it should solve your problem.

If not I'll propose you different look to your problem if you have Excel - use win32com, Dispatch excel, and use Excel Object model. You can use build-in excel functions to rename, delete columns etc. and then save it as csv. E.g.

import win32com.client
excelInstance = win32com.client.gencache.EnsureDispatch('Excel.Application')
workbook = excelInstance.Workbooks.Open(filepath)
worksheet = workbook.Worksheets('WorksheetName')
#### do what you like
worksheet.UsedRange.Find('customernumer').Value2 = 'Customer_Number__c'
####
workbook.SaveAs('Filename.csv', 6) #6 means csv in XlFileFormat enumeration
Grysik
  • 807
  • 7
  • 16