1 | import xlrd
|
---|
2 | import csv
|
---|
3 | import os
|
---|
4 |
|
---|
5 | import xlsutil
|
---|
6 |
|
---|
7 | letter_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 |
|
---|
36 | def 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 |
|
---|
54 | def 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 |
|
---|
79 | def 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 |
|
---|
129 | header_col_j = header_names_mapping[header_name]
|
---|
130 | ## if header_name in header_names_mapping:
|
---|
131 | ## header_col_j = header_names_mapping[header_name]
|
---|
132 |
|
---|
133 | ## header_name = worksheet.cell_value(row_i,header_col_j)
|
---|
134 |
|
---|
135 | if header_name == "Report Identifier":
|
---|
136 | header_name = "hnz.Identifier"
|
---|
137 |
|
---|
138 | if header_name == "Title":
|
---|
139 | header_name = "dc.Title"
|
---|
140 |
|
---|
141 | if header_name == "RelevantTLA's":
|
---|
142 | header_name = "TLA"
|
---|
143 |
|
---|
144 | header_name_utf8 = unicode(header_name).encode("utf-8")
|
---|
145 | metadata_name_utf8 = header_name_utf8.replace(" ", "")
|
---|
146 |
|
---|
147 | entry_utf8_row.append(metadata_name_utf8)
|
---|
148 |
|
---|
149 | ## else:
|
---|
150 | ## print("Warning: Failed to column mapping in spreadsheet for header name \""+header_name+"\" => skipping")
|
---|
151 |
|
---|
152 | unbound_wr.writerow(entry_utf8_row)
|
---|
153 |
|
---|
154 | # Add in 'Filename' as first column in pdfbound CSV file
|
---|
155 | entry_utf8_row.insert(0, "Filename")
|
---|
156 | pdfbound_wr.writerow(entry_utf8_row)
|
---|
157 |
|
---|
158 | # 3. Process the rest of the file (metadata values) ...
|
---|
159 |
|
---|
160 | row_i = xlsutil.header_row_pos + 1
|
---|
161 | while row_i<num_rows:
|
---|
162 | num_cols = worksheet.row_len(row_i)
|
---|
163 | formatted_utf8_row = []
|
---|
164 |
|
---|
165 | found_pdf = False
|
---|
166 |
|
---|
167 | for col_j in range(num_cols):
|
---|
168 | ## for header_name in xlsutil.sanitized_headers:
|
---|
169 | header_name = worksheet.cell_value(xlsutil.header_row_pos,col_j)
|
---|
170 |
|
---|
171 | if header_name and header_name.strip():
|
---|
172 | col_j = header_names_mapping[header_name]
|
---|
173 | ## if header_name in header_names_mapping:
|
---|
174 | ## col_j = header_names_mapping[header_name]
|
---|
175 |
|
---|
176 | cell_value = worksheet.cell_value(row_i,col_j)
|
---|
177 |
|
---|
178 | formatted_cell_value = xlsutil.format_if_int(cell_value)
|
---|
179 |
|
---|
180 | if header_name == "Report Identifier":
|
---|
181 | # Check to see if companion PDF file present
|
---|
182 | # pdf_filename = os.path.join(excel_dirname,"pdfs",formatted_cell_value+".pdf")
|
---|
183 | id = formatted_cell_value.replace(" ","")
|
---|
184 |
|
---|
185 | pdf_file = id_to_relative_pdf_file(id)
|
---|
186 | pdf_file_root, pdf_ext = os.path.splitext(pdf_file)
|
---|
187 | PDF_file = pdf_file_root + ".PDF"
|
---|
188 |
|
---|
189 | pdf_filename = os.path.join(excel_dirname,pdf_file)
|
---|
190 | PDF_filename = os.path.join(excel_dirname,PDF_file)
|
---|
191 |
|
---|
192 | if os.path.exists(pdf_filename):
|
---|
193 | found_pdf = True
|
---|
194 | formatted_utf8_row.insert(0, pdf_file)
|
---|
195 | elif os.path.exists(PDF_filename):
|
---|
196 | found_pdf = True
|
---|
197 | formatted_utf8_row.insert(0, PDF_file)
|
---|
198 | else:
|
---|
199 | print "Unbound id: '" + id + "'"
|
---|
200 |
|
---|
201 | if isinstance(formatted_cell_value, basestring):
|
---|
202 | # Remove any trailing whitespace.
|
---|
203 | # Newline at end particular harmful for a entry in the CSV file
|
---|
204 | formatted_cell_value = formatted_cell_value.rstrip();
|
---|
205 |
|
---|
206 | formatted_cell_value_utf8 = unicode(formatted_cell_value).encode("utf-8")
|
---|
207 |
|
---|
208 | ## Perform any cell transformations to make DL used spreadsheet
|
---|
209 | ## cleaner to build
|
---|
210 | # formatted_cell_value_utf8 = fixup_cell(col_j,formatted_cell_value_utf8)
|
---|
211 |
|
---|
212 | formatted_utf8_row.append(formatted_cell_value_utf8)
|
---|
213 | ## else:
|
---|
214 | ## print "Warning: No column number mapping for header name \""+header_name+"\" => skipping"
|
---|
215 |
|
---|
216 | if found_pdf:
|
---|
217 | pdfbound_wr.writerow(formatted_utf8_row)
|
---|
218 | else:
|
---|
219 | unbound_wr.writerow(formatted_utf8_row)
|
---|
220 |
|
---|
221 | row_i = row_i + 1
|
---|
222 |
|
---|
223 |
|
---|
224 | csv_pdfbound_ofile.close()
|
---|
225 | csv_unbound_ofile.close()
|
---|
226 |
|
---|
227 | if __name__ == "__main__":
|
---|
228 | excel_to_bound_pdf_csv(os.sys.argv[1])
|
---|
229 |
|
---|