[32807] | 1 | import xlrd
|
---|
| 2 | import csv
|
---|
| 3 | import os
|
---|
| 4 |
|
---|
| 5 | worksheet_name = "Archaeological reports"
|
---|
| 6 |
|
---|
| 7 | def excel_to_bound_pdf_csv(excel_filename):
|
---|
| 8 | workbook = xlrd.open_workbook(excel_filename)
|
---|
| 9 | worksheet = workbook.sheet_by_name(worksheet_name)
|
---|
| 10 |
|
---|
| 11 | excel_filename_split = os.path.split(excel_filename)
|
---|
| 12 | excel_dirname = excel_filename_split[0]
|
---|
| 13 | excel_file = excel_filename_split[1]
|
---|
| 14 | excel_file_splitext = os.path.splitext(excel_file)
|
---|
| 15 | excel_file_root = excel_file_splitext[0];
|
---|
| 16 |
|
---|
| 17 | pdfbound_filename = os.path.join(excel_dirname,"PDF-BOUND "+excel_file_root+".csv")
|
---|
| 18 | unbound_filename = os.path.join(excel_dirname,"UNBOUND "+excel_file_root+".csv")
|
---|
| 19 |
|
---|
| 20 | # print "Worksheet: " + worksheet_name
|
---|
| 21 | csv_pdfbound_ofile = open(pdfbound_filename, 'wb')
|
---|
| 22 | #csv_unbound_ofile = open('{}.csv'.format(excel_file_root), 'wb')
|
---|
| 23 | csv_unbound_ofile = open(unbound_filename, 'wb')
|
---|
| 24 |
|
---|
| 25 | pdfbound_wr = csv.writer(csv_pdfbound_ofile, quoting=csv.QUOTE_ALL)
|
---|
| 26 | unbound_wr = csv.writer(csv_unbound_ofile, quoting=csv.QUOTE_ALL)
|
---|
| 27 |
|
---|
| 28 | # 1. Skip lines until "Report Identifier" encountered
|
---|
| 29 | # 2. Remove "Report Identifier" line entries to turn
|
---|
| 30 | # into Greenstone friendly metadata names
|
---|
| 31 | # 3. Process the rest of the file, checking if accompanying
|
---|
| 32 | # PDF file present or not
|
---|
| 33 |
|
---|
| 34 | num_rows = worksheet.nrows
|
---|
| 35 |
|
---|
| 36 | row_i = 0
|
---|
| 37 | found_header = False
|
---|
| 38 | while row_i<num_rows:
|
---|
[32808] | 39 | first_cell = worksheet.cell_value(row_i,0);
|
---|
[32807] | 40 | if first_cell == "Report Identifier":
|
---|
| 41 | found_header = True
|
---|
| 42 | break
|
---|
| 43 | print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
|
---|
| 44 | row_i = row_i + 1
|
---|
| 45 |
|
---|
| 46 | if found_header:
|
---|
| 47 | entry_utf8_row = []
|
---|
| 48 | for entry in worksheet.row_values(row_i):
|
---|
| 49 | entry_utf8 = unicode(entry).encode("utf-8")
|
---|
| 50 | metadata_entry_utf8 = entry_utf8.replace(" ", "")
|
---|
| 51 |
|
---|
| 52 | entry_utf8_row.append(metadata_entry_utf8)
|
---|
| 53 | unbound_wr.writerow(entry_utf8_row)
|
---|
| 54 |
|
---|
[32808] | 55 | # Add in 'Filename' as first column in pdfbound CSV file
|
---|
| 56 | entry_utf8_row.insert(0, "Filename")
|
---|
| 57 | pdfbound_wr.writerow(entry_utf8_row)
|
---|
| 58 |
|
---|
| 59 |
|
---|
[32807] | 60 | row_i = row_i + 1
|
---|
| 61 | while row_i<num_rows:
|
---|
[32808] | 62 | num_cols = worksheet.row_len(row_i)
|
---|
[32807] | 63 | entry_utf8_row = []
|
---|
[32808] | 64 |
|
---|
| 65 | found_pdf = False
|
---|
| 66 |
|
---|
| 67 | # for entry in worksheet.row_values(row_i):
|
---|
| 68 | for col_j in range(num_cols):
|
---|
| 69 | entry = worksheet.cell_value(row_i,col_j)
|
---|
| 70 |
|
---|
| 71 | if col_j == 0:
|
---|
| 72 | # Check to see if companion PDF file present
|
---|
| 73 | pdf_filename = os.path.join(excel_dirname,"pdfs",entry+".pdf")
|
---|
| 74 | if os.path.exists(pdf_filename):
|
---|
| 75 | found_pdf = True
|
---|
| 76 | pdf_file = os.path.join("pdfs",entry+".pdf")
|
---|
| 77 | entry_utf8_row.insert(0, pdf_file)
|
---|
| 78 |
|
---|
[32807] | 79 | entry_utf8 = unicode(entry).encode("utf-8")
|
---|
| 80 | entry_utf8_row.append(entry_utf8)
|
---|
[32808] | 81 | if found_pdf:
|
---|
| 82 | pdfbound_wr.writerow(entry_utf8_row)
|
---|
| 83 | else:
|
---|
| 84 | unbound_wr.writerow(entry_utf8_row)
|
---|
[32807] | 85 |
|
---|
| 86 | row_i = row_i + 1
|
---|
| 87 | else:
|
---|
| 88 | print "Failed to find metadata label 'Report Identifier' in column 0"
|
---|
| 89 |
|
---|
| 90 |
|
---|
| 91 | csv_pdfbound_ofile.close()
|
---|
| 92 | csv_unbound_ofile.close()
|
---|
| 93 |
|
---|
| 94 | if __name__ == "__main__":
|
---|
| 95 | excel_to_bound_pdf_csv(os.sys.argv[1])
|
---|
| 96 |
|
---|