Python Forum
Convert Excel to CSV and retain formatting
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert Excel to CSV and retain formatting
#1
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.
Reply
#2
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?
Reply
#3
(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.
Reply
#4
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.
Reply
#5
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.
Reply
#6
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How do I read in a Formula in Excel and convert it to do the computation in Python? JaneTan 2 349 Jul-07-2021, 02:06 PM
Last Post: Marbelous
  Filter Excel and Convert an Excel File giddyhead 0 617 May-13-2021, 06:31 PM
Last Post: giddyhead
  Importing text file into excel spreadsheet with formatting david_dsmn 1 701 Apr-05-2021, 10:21 PM
Last Post: david_dsmn
  Matplotlib: How do I convert Dates from Excel to use in Matplotlib JaneTan 1 854 Mar-11-2021, 10:52 AM
Last Post: buran
  Openpyxl: Excel formula & condition formatting removed JaneTan 0 978 Sep-25-2020, 07:02 AM
Last Post: JaneTan
  how to retain time format in df.to_csv Mekala 2 1,164 Aug-07-2020, 07:04 AM
Last Post: buran
  Convert Excel file to Text file marvel_plato 6 5,368 Jul-17-2020, 01:45 PM
Last Post: marvel_plato
  convert old excel files(xls) to xlsm zarize 1 1,279 Jul-14-2020, 02:12 PM
Last Post: DeaD_EyE
  Convert Excel to complex list and2handles 1 801 Jun-23-2020, 01:51 PM
Last Post: DPaul
  I'm dividing large numbers of about 25 million digits I need to retain decimal format Pleiades 2 1,663 Apr-26-2018, 07:50 AM
Last Post: Pleiades

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020