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

Last change on this file since 32813 was 32813, checked in by davidb, 3 years ago

Changes after testing

File size: 4.9 KB
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 "Report Identifier" encountered
52 # 2. Remove "Report Identifier" line entries to turn
53 # into Greenstone friendly metadata names
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 row_i = 0
60 found_header = False
61 while row_i<num_rows:
62 first_cell = worksheet.cell_value(row_i,0);
63 if first_cell == "Report Identifier":
64 found_header = True
65 break
66 print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
67 row_i = row_i + 1
68
69 if found_header:
70 entry_utf8_row = []
71 for entry in worksheet.row_values(row_i):
72 if entry == "Report Identifier":
73 entry = "hnz.Identifier"
74
75 entry_utf8 = unicode(entry).encode("utf-8")
76 metadata_entry_utf8 = entry_utf8.replace(" ", "")
77
78 entry_utf8_row.append(metadata_entry_utf8)
79 unbound_wr.writerow(entry_utf8_row)
80
81 # Add in 'Filename' as first column in pdfbound CSV file
82 entry_utf8_row.insert(0, "Filename")
83 pdfbound_wr.writerow(entry_utf8_row)
84
85
86 row_i = row_i + 1
87 while row_i<num_rows:
88 num_cols = worksheet.row_len(row_i)
89 entry_utf8_row = []
90
91 found_pdf = False
92
93# for entry in worksheet.row_values(row_i):
94 for col_j in range(num_cols):
95 cell = worksheet.cell(row_i,col_j)
96# xf = workbook.xf_list[cell.xf_index]
97# format = workbook.format_map[xf.format_key]
98# format_str = format.format_str
99
100# print 'rowx=%d colx=%d ctype=%d xfx=%d s_value=%s fmt=%s' \
101# % (row_i, col_j, cell.ctype, cell.xf_index, str(cell.value), format_str)
102
103 cell_type = worksheet.cell_type(row_i,col_j)
104 cell_value = worksheet.cell_value(row_i,col_j)
105
106 format_cell = format_excelval(workbook,cell_type,cell_value,False)
107 #print "**** format cell str = " +str(format_cell)
108
109 # entry = worksheet.cell_value(row_i,col_j)
110 entry = format_cell
111
112 if col_j == 0:
113 # Check to see if companion PDF file present
114 pdf_filename = os.path.join(excel_dirname,"pdfs",entry+".pdf")
115 if os.path.exists(pdf_filename):
116 found_pdf = True
117 pdf_file = os.path.join("pdfs",entry+".pdf")
118 entry_utf8_row.insert(0, pdf_file)
119
120 entry_utf8 = unicode(entry).encode("utf-8")
121 entry_utf8_row.append(entry_utf8)
122 if found_pdf:
123 pdfbound_wr.writerow(entry_utf8_row)
124 else:
125 unbound_wr.writerow(entry_utf8_row)
126
127 row_i = row_i + 1
128 else:
129 print "Failed to find metadata label 'Report Identifier' in column 0"
130
131
132 csv_pdfbound_ofile.close()
133 csv_unbound_ofile.close()
134
135if __name__ == "__main__":
136 excel_to_bound_pdf_csv(os.sys.argv[1])
137
Note: See TracBrowser for help on using the repository browser.