| 1 | import xlrd
|
|---|
| 2 | import csv
|
|---|
| 3 | import os
|
|---|
| 4 |
|
|---|
| 5 | ## worksheet_name = "Archaeological reports"
|
|---|
| 6 |
|
|---|
| 7 |
|
|---|
| 8 | # https://code.activestate.com/recipes/546518-simple-conversion-of-excel-files-into-csv-and-yaml/download/1/
|
|---|
| 9 | def format_excelval(book, type, value, wanttupledate):
|
|---|
| 10 | """ Clean up the incoming excel data """
|
|---|
| 11 | ## Data Type Codes:
|
|---|
| 12 | ## EMPTY 0
|
|---|
| 13 | ## TEXT 1 a Unicode string
|
|---|
| 14 | ## NUMBER 2 float
|
|---|
| 15 | ## DATE 3 float
|
|---|
| 16 | ## BOOLEAN 4 int; 1 means TRUE, 0 means FALSE
|
|---|
| 17 | ## ERROR 5
|
|---|
| 18 | returnrow = []
|
|---|
| 19 | if type == 2: # TEXT
|
|---|
| 20 | if value == int(value): value = int(value)
|
|---|
| 21 | elif type == 3: # NUMBER
|
|---|
| 22 | datetuple = xlrd.xldate_as_tuple(value, book.datemode)
|
|---|
| 23 | value = datetuple if wanttupledate else tupledate_to_isodate(datetuple)
|
|---|
| 24 | elif type == 5: # ERROR
|
|---|
| 25 | value = xlrd.error_text_from_code[value]
|
|---|
| 26 | return value
|
|---|
| 27 |
|
|---|
| 28 |
|
|---|
| 29 | def excel_to_bound_pdf_csv(excel_filename):
|
|---|
| 30 | workbook = xlrd.open_workbook(excel_filename, formatting_info=True)
|
|---|
| 31 | ## worksheet = workbook.sheet_by_name(worksheet_name)
|
|---|
| 32 | worksheet = workbook.sheet_by_index(0)
|
|---|
| 33 |
|
|---|
| 34 | excel_filename_split = os.path.split(excel_filename)
|
|---|
| 35 | excel_dirname = excel_filename_split[0]
|
|---|
| 36 | excel_file = excel_filename_split[1]
|
|---|
| 37 | excel_file_splitext = os.path.splitext(excel_file)
|
|---|
| 38 | excel_file_root = excel_file_splitext[0];
|
|---|
| 39 |
|
|---|
| 40 | pdfbound_filename = os.path.join(excel_dirname,"PDF-BOUND "+excel_file_root+".csv")
|
|---|
| 41 | unbound_filename = os.path.join(excel_dirname,"UNBOUND "+excel_file_root+".csv")
|
|---|
| 42 |
|
|---|
| 43 | # print "Worksheet: " + worksheet_name
|
|---|
| 44 | csv_pdfbound_ofile = open(pdfbound_filename, 'wb')
|
|---|
| 45 | #csv_unbound_ofile = open('{}.csv'.format(excel_file_root), 'wb')
|
|---|
| 46 | csv_unbound_ofile = open(unbound_filename, 'wb')
|
|---|
| 47 |
|
|---|
| 48 | pdfbound_wr = csv.writer(csv_pdfbound_ofile, quoting=csv.QUOTE_ALL)
|
|---|
| 49 | unbound_wr = csv.writer(csv_unbound_ofile, quoting=csv.QUOTE_ALL)
|
|---|
| 50 |
|
|---|
| 51 | # 1. Skip lines until "Report Identifier" encountered
|
|---|
| 52 | # 2. Remove "Report Identifier" line entries to turn
|
|---|
| 53 | # into Greenstone friendly metadata names
|
|---|
| 54 | # 3. Process the rest of the file, checking if accompanying
|
|---|
| 55 | # PDF file present or not
|
|---|
| 56 |
|
|---|
| 57 | num_rows = worksheet.nrows
|
|---|
| 58 |
|
|---|
| 59 | row_i = 0
|
|---|
| 60 | found_header = False
|
|---|
| 61 | while row_i<num_rows:
|
|---|
| 62 | first_cell = worksheet.cell_value(row_i,0);
|
|---|
| 63 | if first_cell == "Report Identifier":
|
|---|
| 64 | found_header = True
|
|---|
| 65 | break
|
|---|
| 66 | print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
|
|---|
| 67 | row_i = row_i + 1
|
|---|
| 68 |
|
|---|
| 69 | if found_header:
|
|---|
| 70 | entry_utf8_row = []
|
|---|
| 71 | for entry in worksheet.row_values(row_i):
|
|---|
| 72 | if entry == "Report Identifier":
|
|---|
| 73 | entry = "hnz.Identifier"
|
|---|
| 74 |
|
|---|
| 75 | entry_utf8 = unicode(entry).encode("utf-8")
|
|---|
| 76 | metadata_entry_utf8 = entry_utf8.replace(" ", "")
|
|---|
| 77 |
|
|---|
| 78 | entry_utf8_row.append(metadata_entry_utf8)
|
|---|
| 79 | unbound_wr.writerow(entry_utf8_row)
|
|---|
| 80 |
|
|---|
| 81 | # Add in 'Filename' as first column in pdfbound CSV file
|
|---|
| 82 | entry_utf8_row.insert(0, "Filename")
|
|---|
| 83 | pdfbound_wr.writerow(entry_utf8_row)
|
|---|
| 84 |
|
|---|
| 85 |
|
|---|
| 86 | row_i = row_i + 1
|
|---|
| 87 | while row_i<num_rows:
|
|---|
| 88 | num_cols = worksheet.row_len(row_i)
|
|---|
| 89 | entry_utf8_row = []
|
|---|
| 90 |
|
|---|
| 91 | found_pdf = False
|
|---|
| 92 |
|
|---|
| 93 | # for entry in worksheet.row_values(row_i):
|
|---|
| 94 | for col_j in range(num_cols):
|
|---|
| 95 | cell = worksheet.cell(row_i,col_j)
|
|---|
| 96 | # xf = workbook.xf_list[cell.xf_index]
|
|---|
| 97 | # format = workbook.format_map[xf.format_key]
|
|---|
| 98 | # format_str = format.format_str
|
|---|
| 99 |
|
|---|
| 100 | # print 'rowx=%d colx=%d ctype=%d xfx=%d s_value=%s fmt=%s' \
|
|---|
| 101 | # % (row_i, col_j, cell.ctype, cell.xf_index, str(cell.value), format_str)
|
|---|
| 102 |
|
|---|
| 103 | cell_type = worksheet.cell_type(row_i,col_j)
|
|---|
| 104 | cell_value = worksheet.cell_value(row_i,col_j)
|
|---|
| 105 |
|
|---|
| 106 | format_cell = format_excelval(workbook,cell_type,cell_value,False)
|
|---|
| 107 | #print "**** format cell str = " +str(format_cell)
|
|---|
| 108 |
|
|---|
| 109 | # entry = worksheet.cell_value(row_i,col_j)
|
|---|
| 110 | entry = format_cell
|
|---|
| 111 |
|
|---|
| 112 | if col_j == 0:
|
|---|
| 113 | # Check to see if companion PDF file present
|
|---|
| 114 | pdf_filename = os.path.join(excel_dirname,"pdfs",entry+".pdf")
|
|---|
| 115 | if os.path.exists(pdf_filename):
|
|---|
| 116 | found_pdf = True
|
|---|
| 117 | pdf_file = os.path.join("pdfs",entry+".pdf")
|
|---|
| 118 | entry_utf8_row.insert(0, pdf_file)
|
|---|
| 119 |
|
|---|
| 120 | entry_utf8 = unicode(entry).encode("utf-8")
|
|---|
| 121 | entry_utf8_row.append(entry_utf8)
|
|---|
| 122 | if found_pdf:
|
|---|
| 123 | pdfbound_wr.writerow(entry_utf8_row)
|
|---|
| 124 | else:
|
|---|
| 125 | unbound_wr.writerow(entry_utf8_row)
|
|---|
| 126 |
|
|---|
| 127 | row_i = row_i + 1
|
|---|
| 128 | else:
|
|---|
| 129 | print "Failed to find metadata label 'Report Identifier' in column 0"
|
|---|
| 130 |
|
|---|
| 131 |
|
|---|
| 132 | csv_pdfbound_ofile.close()
|
|---|
| 133 | csv_unbound_ofile.close()
|
|---|
| 134 |
|
|---|
| 135 | if __name__ == "__main__":
|
|---|
| 136 | excel_to_bound_pdf_csv(os.sys.argv[1])
|
|---|
| 137 |
|
|---|
