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