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

Revision 33202, 7.9 KB (checked in by davidb, 2 months ago)

Bound and Unbound CSV files changed to pring out all non-empty header columns

Line 
1import xlrd
2import csv
3import os
4
5import xlsutil
6
7letter_to_folder = {
8  'a': "A - B",
9  'b': "A - B",
10  'c': "C - D",
11  'd': "C - D",
12  'e': "E - F",
13  'f': "E - F",
14  'g': "G - H",
15  'h': "G - H",
16  'i': "I - J",
17  'j': "I - J",
18  'k': "K - L",
19  'l': "K - L",
20  'm': "M - N",
21  'n': "M - N",
22  'o': "O - P",
23  'p': "O - P",
24  'q': "Q - R",
25  'r': "Q - R",
26  's': "S - T",
27  't': "S - T",
28  'u': "U - V",
29  'v': "U - V",
30  'w': "W - Z",
31  'x': "W - Z",
32  'y': "W - Z",
33  'z': "W - Z"
34}
35
36def id_to_relative_pdf_file(id):
37    first_char = id[0:1]
38    first_char_lower = first_char.lower()
39
40    file_tail = letter_to_folder[first_char_lower]
41    file_root = "Reports " + file_tail
42
43    pdf_file = os.path.join("Digital-Library-PDFs",file_root,id+".pdf")
44
45    return pdf_file
46
47
48# **** Written, but never used
49# **** Col numbers refer to older version of spreadsheet
50#
51# There is now a mapping of header-name to index position which would be
52# a better way to do thing
53
54def fixup_cell(col_num,cell_str):
55    # col == 1 => Author
56    # col == 2 => Title
57    # col == 4 => TLA
58    # col == 5 => AuthorityNo
59    # col == 6 => SiteNo
60
61    if col_num == 1: #Author
62        cell_str = re.sub('\|?(et al)|(Associates)', '', cell_str)
63
64    if col_num == 2: #Title
65        # Detect any ALL-CAPS titles, and change to title-case
66        onlyletters = re.sub('[^A-Za-z]+', '', cell_str)
67        if onlyletters.isupper:
68            cell_str = cell_str.title()
69
70    if col_num == 4: #TLA
71        if cell_str == "All":     cell_str = ""
72        if cell_str == "n/a":     cell_str = ""
73        if cell_str == "various": cell_str = "" # not a meaningful TLA
74
75
76    return cell_str
77
78
79def excel_to_bound_pdf_csv(excel_filename):
80    workbook = xlrd.open_workbook(excel_filename)
81
82    worksheet = workbook.sheet_by_index(xlsutil.worksheet_index_pos)
83    ## worksheet = workbook.sheet_by_name(xlsutil.worksheet_name)
84
85    excel_filename_split = os.path.split(excel_filename)
86    excel_dirname = excel_filename_split[0]
87    excel_file = excel_filename_split[1]
88    excel_file_splitext = os.path.splitext(excel_file)
89    excel_file_root = excel_file_splitext[0];
90
91    pdfbound_filename = os.path.join(excel_dirname,"PDF-BOUND "+excel_file_root+".csv")
92    unbound_filename  = os.path.join(excel_dirname,"UNBOUND "+excel_file_root+".csv")
93
94    csv_pdfbound_ofile = open(pdfbound_filename, 'wb')
95    csv_unbound_ofile  = open(unbound_filename, 'wb')
96
97    pdfbound_wr = csv.writer(csv_pdfbound_ofile, quoting=csv.QUOTE_ALL)
98    unbound_wr  = csv.writer(csv_unbound_ofile, quoting=csv.QUOTE_ALL)
99
100    # 1. For header-line, build up hashmap of header-names to column number
101    # 2. Write out header-line as Greenstone friendly Metadata terms
102    #      => remove sp spaces, change "Report Identifier" to hnz.Identifier
103    # 3. Process the rest of the file, checking if accompanying
104    #     PDF file present or not and only printing out the header-names
105    #     as long as it has a non-empty header-name
106##    #     specified in 'sanitized_headers'
107
108    num_rows = worksheet.nrows
109
110    # 1. Get header-line hashmap of header-names to column numbers
111    header_names_mapping = xlsutil.getHeaderMappingToColNum(worksheet)
112
113    if header_names_mapping is None:
114        print "Failed to find metadata label 'Report Identifier' in column 0"
115        exit()
116    else:
117        # 2. Process Header into Greenstone friendly metadata terms     
118
119        entry_utf8_row = []
120        num_header_cols = worksheet.row_len(xlsutil.header_row_pos)
121
122        for col_j in range(num_header_cols):
123##        for header_name in xlsutil.sanitized_headers:
124
125            header_name = worksheet.cell_value(xlsutil.header_row_pos,col_j)
126
127            if header_name and header_name.strip():
128####                print "*** 2. header_name = " + header_name
129
130                header_col_j = header_names_mapping[header_name]
131##            if header_name in header_names_mapping:
132##                header_col_j = header_names_mapping[header_name]
133
134##                header_cell_value = worksheet.cell_value(row_i,header_col_j)
135               
136                if header_name == "Report Identifier":
137                    header_name = "hnz.Identifier"
138
139                if header_name == "Title":
140                    header_name = "dc.Title"
141
142                if header_name == "RelevantTLA's":
143                    header_name = "TLA"
144
145                header_name_utf8 = unicode(header_name).encode("utf-8")
146                metadata_name_utf8 = header_name_utf8.replace(" ", "")
147
148                entry_utf8_row.append(metadata_name_utf8)
149
150##            else:
151##                print("Warning: Failed to column mapping in spreadsheet for header name \""+header_name+"\" => skipping")
152
153        unbound_wr.writerow(entry_utf8_row)
154
155        # Add in 'Filename' as first column in pdfbound CSV file
156        entry_utf8_row.insert(0, "Filename")
157        pdfbound_wr.writerow(entry_utf8_row)
158                     
159        # 3. Process the rest of the file (metadata values) ...
160
161        row_i = xlsutil.header_row_pos + 1
162        while row_i<num_rows:
163            num_cols = worksheet.row_len(row_i)
164            formatted_utf8_row = []
165
166            found_pdf = False
167
168            for col_j in range(num_cols):
169##            for header_name in xlsutil.sanitized_headers:
170                header_name = worksheet.cell_value(xlsutil.header_row_pos,col_j)
171
172                if header_name and header_name.strip():
173                    col_j = header_names_mapping[header_name]
174##                if header_name in header_names_mapping:
175##                    col_j = header_names_mapping[header_name]
176
177                    cell_value = worksheet.cell_value(row_i,col_j)
178
179                    formatted_cell_value = xlsutil.format_if_int(cell_value)
180
181                    if header_name == "Report Identifier":
182                        # Check to see if companion PDF file present
183                        # pdf_filename = os.path.join(excel_dirname,"pdfs",formatted_cell_value+".pdf")
184                        id = formatted_cell_value.replace(" ","")
185                       
186                        pdf_file = id_to_relative_pdf_file(id)
187                        pdf_file_root, pdf_ext = os.path.splitext(pdf_file)
188                        PDF_file = pdf_file_root + ".PDF"
189                   
190                        pdf_filename = os.path.join(excel_dirname,pdf_file)
191                        PDF_filename = os.path.join(excel_dirname,PDF_file)
192
193                        if os.path.exists(pdf_filename):
194                            found_pdf = True
195                            formatted_utf8_row.insert(0, pdf_file)
196                        elif os.path.exists(PDF_filename):
197                            found_pdf = True
198                            formatted_utf8_row.insert(0, PDF_file)
199                        else:
200                            print "Unbound id: '" + id + "'"
201
202                    if isinstance(formatted_cell_value, basestring):
203                        # Remove any trailing whitespace. 
204                        # Newline at end particular harmful for a entry in the CSV file
205                        formatted_cell_value = formatted_cell_value.rstrip();
206
207                    formatted_cell_value_utf8 = unicode(formatted_cell_value).encode("utf-8")
208
209                    ## Perform any cell transformations to make DL used spreadsheet
210                    ## cleaner to build
211                    # formatted_cell_value_utf8 = fixup_cell(col_j,formatted_cell_value_utf8)
212
213                    formatted_utf8_row.append(formatted_cell_value_utf8)
214##                else:
215##                    print "Warning: No column number mapping for header name \""+header_name+"\" => skipping"
216
217            if found_pdf:
218                pdfbound_wr.writerow(formatted_utf8_row)
219            else:
220                unbound_wr.writerow(formatted_utf8_row)
221
222            row_i = row_i + 1
223
224
225    csv_pdfbound_ofile.close()
226    csv_unbound_ofile.close()
227
228if __name__ == "__main__":
229    excel_to_bound_pdf_csv(os.sys.argv[1])
230
Note: See TracBrowser for help on using the browser.