Python convert xls with multiple worksheets to csv without newlines or bytes

Question

Code below takes an xls file and destination path. The code loops through all the worksheets in each file and converts the to a pipe delimited csv file.

def ExceltoCSV(excel_file, csv_file_base_path):
    head, tail = os.path.split(excel_file)
    print(tail)
    workbook = xlrd.open_workbook(excel_file)
    for sheet_name in workbook.sheet_names():
        print('processing - ' + sheet_name)
        worksheet = workbook.sheet_by_name(sheet_name)
        csv_file_full_path = csv_file_base_path + sheet_name.lower().replace(" - ", "_").replace(" ","_") + '.csv'
        csvfile = open(csv_file_full_path, 'w')
        writetocsv = csv.writer(csvfile, quoting = csv.QUOTE_ALL, delimiter='|')
        for rownum in range(worksheet.nrows):
             writetocsv.writerow(list((x.encode('utf-8')).decode() if type(x) == type(u'') else x for x in worksheet.row_values(rownum)))
        csvfile.close()
        print(sheet_name + ' has been saved at - ' + csv_file_full_path)

My issue with the function is that it creates the file with a \n after each line and it wraps each valve it writes inside "b'value'". I have tried the open with the wb option but I get an error: a bytes-like object is required, not 'str'

what should i change to avoid the extra lines and the wrapping of "b''"

Sample file output

"b''"|"b'Totals'"|"b'321'"|"b'734'"|"b'2,941,740'"|"b''"|"b''"|"b'Under - 11 Totals'"|"b'203'"|"b'77'"|"b''"|"b''"

"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"|"b''"

Does this answer your question? [Convert bytes to a string](https://stackoverflow.com/questions/606191/convert-bytes-to-a-string) — sushanth, Aug 11 '20 at 16:08
Can u edit the post with the updated code, its looks like you have missing `()` you have just ``.decode`` instead it should be ``.decode()`` — sushanth, Aug 11 '20 at 16:53
writetocsv.writerow(list((x.encode('utf-8')).decode if type(x) == type(u'') else x for x in worksheet.row_values(rownum))) — Leo Torres, Aug 11 '20 at 17:04
as mentioned in above commend it should be ``..(x.encode('utf-8')).decode()...``, its a func call — sushanth, Aug 11 '20 at 17:05
thank you for clarification. Correction made now have a unicode error — Leo Torres, Aug 11 '20 at 17:19
UnicodeEncodeError: 'charmap' codec can't encode character '\u2264' in position 77: character maps to — Leo Torres, Aug 11 '20 at 17:19
Thank you drum.. as stated above wb does not work. Tried it again got the same error — Leo Torres, Aug 11 '20 at 18:23
def ExceltoCSV2(excel_file, csv_file_base_path): head, tail = os.path.split(excel_file) df = pd.DataFrame() xl = pd.ExcelFile(excel_file) for sheet in xl.sheet_names: df_tmp = xl.parse(sheet) df = df.append(df_tmp, ignore_index=True,sort=False) print(len(df)) csvfile = head+'/'+sheet+'.csv' print(csvfile) df.to_csv(csvfile, index=False, sep='|') — Leo Torres, Aug 12 '20 at 19:14

Python convert xls with multiple worksheets to csv without newlines or bytes

0 Answers0