source: gs3-installations/whakatohea-dl/trunk/sites/wmtb/bin/script/metamaster-xlsx-to-csv.py@ 37416

#!/usr/bin/env python3

import os
import sys

import csv
import openpyxl
import re

import os
import re
import sys

#import argparse





argc=len(sys.argv)
progname= sys.argv[0]

if argc == 1 or argc == 2:
    print("",file=sys.stderr)
    print("Usage: ", file=sys.stderr)
    print("    " + progname + " sheet-name metamaster.xslx [metamaster.csv]",file=sys.stderr)
    print("",file=sys.stderr)
    print("For example:",file=sys.stderr)
    print("   " + progname + " Image Metamaster.xlsx",file=sys.stderr)
    print("",file=sys.stderr)
    sys.exit(1)

sheet_name     = sys.argv[1]
xlsx_ifilename = sys.argv[2]

if argc == 3:
    csv_ofilename= os.path.splitext(xlsx_ifilename)[0]+'.csv'
else:
    csv_ofilename = sys.argv[2]
    

if os.path.exists(xlsx_ifilename) and os.path.exists(csv_ofilename):

    diff_time = os.stat(csv_ofilename).st_mtime - os.stat(xlsx_ifilename).st_mtime
    if diff_time >= 0:
        print("CSV file newer than XSLX file => no need to convert")
        sys.exit(0)
        
workbook  = openpyxl.load_workbook(xlsx_ifilename)
worksheet = workbook[sheet_name]

    
# No,Date,S'n/Conc,Artist,Composer,Work,Venue,Attendance,Review,Notes
IMID        =  1
Date        =  2
ProgAndPage =  3
Artist      =  4
Composer    =  5
Work        =  6
Venue       =  7
Attendance  =  8
Review      =  9
Notes       = 10

GSFriendlyID     = 11
GSFriendlyDate   = 12
GSFriendlyProg   = 13
GSFriendlyPage   = 14
GSFriendlyArtist = 15


#worksheet.cell(row=1,column=ProgAndPage,      value="ProgAndPage")
#worksheet.cell(row=1,column=GSFriendlyID,     value="GSFriendlyID")
#worksheet.cell(row=1,column=GSFriendlyDate,   value="GSFriendlyDate")
#worksheet.cell(row=1,column=GSFriendlyProg,   value="GSFriendlyProg")
#worksheet.cell(row=1,column=GSFriendlyPage,   value="GSFriendlyPage")
#worksheet.cell(row=1,column=GSFriendlyArtist, value="GSFriendlyArtist")


num_rows = worksheet.max_row;

# Tweak the data

# (skip the first row with headers)
row_i = 2

#print("Converting Excel spreadsheet to CSV (+ Greenstone friendly fields)")
print("Converting Excel spreadsheet to CSV")

while row_i<=num_rows:

    print("  Processing row: " + str(row_i))
    
    #
    # Massage Intermuse ID
    #

#    id_cell = worksheet.cell(row=row_i,column=IMID)    
#    id_value = id_cell.value
#    gs_id_value_str = "D"+id_value
    
#    worksheet.cell(row=row_i,column=GSFriendlyID, value=gs_id_value_str)
    
    #
    # Massage Date
    #
#    date_cell = worksheet.cell(row=row_i,column=Date)    
#    date_value = date_cell.value

    #print(repr(date_value))
    
#    date_str = date_value.strftime('%d-%m-%Y')
#    gs_date_str = date_value.strftime('%Y%m%d')

#    worksheet.cell(row=row_i,column=Date, value=date_str)
#    worksheet.cell(row=row_i,column=GSFriendlyDate, value=gs_date_str)


    #
    # Split ProgAndPage into two separate parts
    #
#    progandpage_cell = worksheet.cell(row=row_i,column=ProgAndPage)    
#    progandpage_value = progandpage_cell.value
#    prog_page_array = progandpage_value.split("/")
#    if (len(prog_page_array) == 2):
#        [prog_value,page_value] = prog_page_array;
    
#       worksheet.cell(row=row_i,column=GSFriendlyProg, value=prog_value)
#        worksheet.cell(row=row_i,column=GSFriendlyPage, value=page_value)
    
    #
    # Massage Artist
    #
#    artist_cell = worksheet.cell(row=row_i,column=Artist)    
#    artist_value = artist_cell.value

#    gs_artist_value = re.sub("\s+\(.*?\)","",artist_value)
                                           
#    worksheet.cell(row=row_i,column=GSFriendlyArtist, value=gs_artist_value)    

    row_i = row_i + 1

          
        
    

ob = csv.writer(open(csv_ofilename,'w', newline = ""))
    
for r in worksheet.rows:
    row = [a.value for a in r]
    ob.writerow(row)










#if __name__ == "__main__":

#    parser = argparse.ArgumentParser()
#    parser.add_argument('--sheetname', help="The name of the sheet within the Excel file to extractc data from")
#    parser.add_argument('--votingtype', choices=["J","T", "JT"], help="Filter to only J=Jury, T=Tele cast votes, JT=Combined jury and tele votes")
#    parser.add_argument('input-file.xlsx')
#    parser.add_argument('output-file.json', nargs='?')

#    args = parser.parse_args()

#    sheetname = getattr(args,'sheetname');
#    voting_type = getattr(args,'votingtype');

#    excel_input_filename = getattr(args,'input-file.xlsx');
#    json_output_filename = getattr(args,'output-file.json');

#    if (json_output_filename == None):
#        json_output_filename = os.path.splitext(excel_input_filename)[0]+'.json'