Python Forum
Convert Excel to CSV and retain formatting - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Convert Excel to CSV and retain formatting (/thread-28559.html)



Convert Excel to CSV and retain formatting - slider09 - Jul-23-2020

Hi all,

Wonder if someone could point me in the right direction for a little problem I have. I have written a Python script which converts an Excel document (and all worksheets within said Excel document) into a UTF8 CSV file. The problem I have is the output is coming out raw, i.e. not as it 'appears' in the Excel sheet.

For example, one of the fields is a 'Timestamp' field (displayed as 07/23/2020 21:00). However my output once converted to a csv is something like '4250.12345678'. Likewise, there are many numeric values present, presented in the Excel document as '0', '50' etc. Again, once converted these are coming out as '0.0', '50.0'.

Is there anyway I can output the data in the format that it is shown in Excel?

Thank you.


RE: Convert Excel to CSV and retain formatting - Gribouillis - Jul-23-2020

You say you wrote the Python script, so you are the only one to blame for the wrong output. How did you code the output of the Timestamp field?


RE: Convert Excel to CSV and retain formatting - slider09 - Jul-24-2020

(Jul-23-2020, 10:59 PM)Gribouillis Wrote: You say you wrote the Python script, so you are the only one to blame for the wrong output. How did you code the output of the Timestamp field?

I am not blaming anyone else.

What I was asking is, is there a way when writing out the csv to retain the formatting from Excel.


RE: Convert Excel to CSV and retain formatting - Gribouillis - Jul-24-2020

I'm sure posting the python code as well as an small excel file demonstrating the issue would make it much easier to help you.


RE: Convert Excel to CSV and retain formatting - slider09 - Jul-24-2020

Hi,

Unfortunately I cannot include attachments as I am a new member with insufficient posts.

Example of Excel document:
timestamp var1 var2 var3
20/08/2018 15:15 1 2 3

Example of my csv:
"timestamp","var1","var2","var3"
"43332.6355524768","1.0","2.0","3.0"

As you can see from the above, the timestamp value in the csv is not as the Excel value, neither are the fields var1,2,3

I will include these as attachments as and when I am able to.

My code is as follows:

import xlrd
import csv
import glob

def main():

    file_list = []

    for file in glob.glob('*.xlsx'):
      file_list.append(file)
   
    for filename in file_list:
      hidden = 0
      print(filename)
      print('')
      wb = xlrd.open_workbook(filename, on_demand=True)
      num_of_sheets = len(wb.sheet_names())
      for sheet in wb.sheets():
        sh = wb.sheet_by_name(sheet.name)
        if sh.visibility == 1:
          print(sheet.name,'hidden')
          hidden += 1
        else:
          your_csv_file = open(sheet.name + '.txt', 'w', encoding='utf-8', newline='')
          wr = csv.writer(your_csv_file, delimiter=',', quoting=csv.QUOTE_ALL)
          for rownum in range(sh.nrows):
            wr.writerow(sh.row_values(rownum))
          your_csv_file.close()
         
      print('')
      print(num_of_sheets,'worksheets found,',hidden,'hidden')
      print('')

if __name__ == "__main__":
    main()
Thank you.


RE: Convert Excel to CSV and retain formatting - Gribouillis - Jul-24-2020

The module xlrd.xldate apparently contains various functions to convert to and from excel dates. You could use one of them to convert the timestamps to a datetime object. Also the class Cell contains a ctype member which helps you determine if the cell contains a date or not. This could be used by your program to know whether the cell's content needs conversion.