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

Revision 32820, 4.6 KB (checked in by davidb, 9 months ago)

Tidy up

Line 
1import xlrd
2import csv
3import 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/
9def 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
29def 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 Header encountered (as defined by encountering "Report Identifier")
52    # 2. Write out Header line as Greenstone friendly Metadata terms
53    #      => remove sp spaces, change "Report Identifier" to hnz.Identifier
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    # 1, Skip lines until Header encountered
60    row_i = 0
61    found_header = False
62    while row_i<num_rows:
63        first_cell = worksheet.cell_value(row_i,0);
64        if first_cell == "Report Identifier":
65            found_header = True
66            break
67        print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
68        row_i = row_i + 1
69
70    # 2. Process Header into Greenstone friendly metadata terms     
71    if found_header:
72        entry_utf8_row = []
73        for entry in worksheet.row_values(row_i):
74            if entry == "Report Identifier":
75                entry = "hnz.Identifier"
76
77            entry_utf8 = unicode(entry).encode("utf-8")
78            metadata_entry_utf8 = entry_utf8.replace(" ", "")
79
80            entry_utf8_row.append(metadata_entry_utf8)
81        unbound_wr.writerow(entry_utf8_row)
82
83        # Add in 'Filename' as first column in pdfbound CSV file
84        entry_utf8_row.insert(0, "Filename")
85        pdfbound_wr.writerow(entry_utf8_row)
86
87
88        row_i = row_i + 1
89        while row_i<num_rows:
90            num_cols = worksheet.row_len(row_i)
91            formatted_utf8_row = []
92
93            found_pdf = False
94
95            for col_j in range(num_cols):
96                cell = worksheet.cell(row_i,col_j)
97
98                cell_type  = worksheet.cell_type(row_i,col_j)
99                cell_value = worksheet.cell_value(row_i,col_j)
100
101                formatted_cell = format_excelval(workbook,cell_type,cell_value,False)
102
103                if col_j == 0:
104                    # Check to see if companion PDF file present
105                    pdf_filename = os.path.join(excel_dirname,"pdfs",formatted_cell+".pdf")
106                    if os.path.exists(pdf_filename):
107                        found_pdf = True
108                        pdf_file = os.path.join("pdfs",formatted_cell+".pdf")
109                        formatted_utf8_row.insert(0, pdf_file)
110                                 
111                formatted_cell_utf8 = unicode(formatted_cell).encode("utf-8")
112                formatted_utf8_row.append(formatted_cell_utf8)
113            if found_pdf:
114                pdfbound_wr.writerow(formatted_utf8_row)
115            else:
116                unbound_wr.writerow(formatted_utf8_row)
117
118            row_i = row_i + 1
119    else:
120        print "Failed to find metadata label 'Report Identifier' in column 0"
121
122
123    csv_pdfbound_ofile.close()
124    csv_unbound_ofile.close()
125
126if __name__ == "__main__":
127    excel_to_bound_pdf_csv(os.sys.argv[1])
128
Note: See TracBrowser for help on using the browser.