root/main/trunk/model-sites-dev/heritage-nz/collect/reports-2019/prepare/xls-to-csv.py @ 32808

Revision 32808, 3.3 KB (checked in by davidb, 7 months ago)

Splitting between unbound and pdf-bound added in

Line 
1import xlrd
2import csv
3import os
4
5worksheet_name = "Archaeological reports"
6
7def 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:
39        first_cell = worksheet.cell_value(row_i,0);
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
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
60        row_i = row_i + 1
61        while row_i<num_rows:
62            num_cols = worksheet.row_len(row_i)
63            entry_utf8_row = []
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                                 
79                entry_utf8 = unicode(entry).encode("utf-8")
80                entry_utf8_row.append(entry_utf8)
81            if found_pdf:
82                pdfbound_wr.writerow(entry_utf8_row)
83            else:
84                unbound_wr.writerow(entry_utf8_row)
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
94if __name__ == "__main__":
95    excel_to_bound_pdf_csv(os.sys.argv[1])
96
Note: See TracBrowser for help on using the browser.