gyetvaig

Python xlsb, xlsx to csv

Jan 20th, 2019
324
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 3.31 KB | None | 0 0
  1. import datetime as dt
  2. import os
  3. import time
  4. import win32com.client
  5.  
  6.  
  7. # FUNCTIONS
  8. def get_file_extension(src_file_path):
  9.     """
  10.    Returning file extension
  11.    :param src_file_path: str - file path
  12.    :return: str - the file extension
  13.    """
  14.     try:
  15.         if len(os.path.splitext(src_file_path)[1]) != 0:
  16.             return os.path.splitext(src_file_path)[1]
  17.  
  18.     except FileNotFoundError:
  19.         print('File not found, please re-run')
  20.  
  21.  
  22. def xl_file_to_csv(xl_file_file_path, sh_index):
  23.     """
  24.    Open a workbook, get the index of the worksheet (sh_index) and save sheet(s) as csv files
  25.    :param xl_file_file_path: workbook path
  26.    :param sh_index: index of the worksheet (0 - all worksheets)
  27.    :return: generated csv file names
  28.    """
  29.     start_time = time.process_time()
  30.     start_clock = time.perf_counter()
  31.  
  32.     xl_app = win32com.client.Dispatch("Excel.Application")
  33.     xl_app.Visible = 0
  34.     xl_app.DisplayAlerts = 0
  35.  
  36.     work_book = xl_app.Workbooks.Open(xl_file_file_path)
  37.  
  38.     if work_book.Worksheets.count >= sh_index:
  39.         csv_file_list = list()
  40.         if sheet_index == 0:
  41.             for sheet in work_book.Worksheets:
  42.                 work_sheet = sheet
  43.                 csv_file_list = save_csv_file(work_sheet, csv_file_list)
  44.         else:
  45.             work_sheet = work_book.Worksheets(sheet_index)
  46.             csv_file_list = save_csv_file(work_sheet, csv_file_list)
  47.  
  48.         work_book.Close(SaveChanges=0)
  49.         xl_app.Quit()
  50.  
  51.         # Print runtime
  52.         print('win32com process: {}'.format(time.process_time() - start_time))
  53.         print('win32com counter: {}'.format(time.perf_counter() - start_clock))
  54.  
  55.         return csv_file_list
  56.     else:
  57.         print('There is not a tab in the workbook with index: {}'.format(sh_index))
  58.  
  59.  
  60. def save_csv_file(work_sheet, file_name_list):
  61.     """
  62.    Saving a worksheet to a csv file and add the csv file name to a list, naming convention applied
  63.    :param work_sheet: worksheet to save csv (type: win32com.client.CDispatch)
  64.    :param file_name_list: list holding csv file names
  65.    :return: list of csv file names
  66.    """
  67.  
  68.     work_sheet_name = work_sheet.name
  69.     output_csv_name = work_sheet_name + '_' + time_stamp_generator() + '.csv'
  70.     work_sheet.SaveAs(os.path.join(os.getcwd(), output_csv_name), 6)
  71.     file_name_list.append(output_csv_name)
  72.  
  73.     return file_name_list
  74.  
  75.  
  76. def time_stamp_generator():
  77.     """
  78.    Generate a timestamp
  79.    :return: a timestamp (str)
  80.    """
  81.     time_stamp = str(dt.datetime.now())
  82.     time_stamp = time_stamp.replace(':', '_')
  83.     time_stamp = time_stamp.replace(' ', '_')
  84.     return time_stamp
  85.  
  86.  
  87. # PROGRAM STARTS HERE
  88. file_path = input('Please enter file path: ')
  89. file_extension = get_file_extension(file_path)
  90.  
  91. if file_extension in [".xlsb", ".xlsx"]:
  92.     try:
  93.         sheet_index = int(input('Please enter the index number of a sheet to convert to .csv (0 - all sheets): '))
  94.         src_csv = xl_file_to_csv(file_path, sheet_index)
  95.         if src_csv is not None:
  96.             print('')
  97.             print('CSV file(s) created: ')
  98.             print('-----------------------\n')
  99.             print(*src_csv, sep="\n")
  100.     except ValueError:
  101.         print('Sheet index number must be an integer.')
  102. else:
  103.     print("Program can only process '.xlsb' or '.xlsx' files.")
Advertisement
Add Comment
Please, Sign In to add comment