source: main/trunk/model-sites-dev/heritage-nz/collect/pdf-reports/prepare/xls-to-csv.py@ 33222

Last change on this file since 33222 was 33222, checked in by davidb, 5 years ago

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