1 | import xlrd
|
---|
2 | import csv
|
---|
3 | import 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/
|
---|
9 | def 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 |
|
---|
29 | def 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 Header encountered (as defined by encountering "Report Identifier")
|
---|
52 | # 2. Write out Header line as Greenstone friendly Metadata terms
|
---|
53 | # => remove sp spaces, change "Report Identifier" to hnz.Identifier
|
---|
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 | # 1, Skip lines until Header encountered
|
---|
60 | row_i = 0
|
---|
61 | found_header = False
|
---|
62 | while row_i<num_rows:
|
---|
63 | first_cell = worksheet.cell_value(row_i,0);
|
---|
64 | if first_cell == "Report Identifier":
|
---|
65 | found_header = True
|
---|
66 | break
|
---|
67 | print "Skipping row {} as not yet encountered 'Report Identifier' metadata label in column 0".format(row_i)
|
---|
68 | row_i = row_i + 1
|
---|
69 |
|
---|
70 | # 2. Process Header into Greenstone friendly metadata terms
|
---|
71 | if found_header:
|
---|
72 | entry_utf8_row = []
|
---|
73 | for entry in worksheet.row_values(row_i):
|
---|
74 | if entry == "Report Identifier":
|
---|
75 | entry = "hnz.Identifier"
|
---|
76 |
|
---|
77 | entry_utf8 = unicode(entry).encode("utf-8")
|
---|
78 | metadata_entry_utf8 = entry_utf8.replace(" ", "")
|
---|
79 |
|
---|
80 | entry_utf8_row.append(metadata_entry_utf8)
|
---|
81 | unbound_wr.writerow(entry_utf8_row)
|
---|
82 |
|
---|
83 | # Add in 'Filename' as first column in pdfbound CSV file
|
---|
84 | entry_utf8_row.insert(0, "Filename")
|
---|
85 | pdfbound_wr.writerow(entry_utf8_row)
|
---|
86 |
|
---|
87 |
|
---|
88 | row_i = row_i + 1
|
---|
89 | while row_i<num_rows:
|
---|
90 | num_cols = worksheet.row_len(row_i)
|
---|
91 | formatted_utf8_row = []
|
---|
92 |
|
---|
93 | found_pdf = False
|
---|
94 |
|
---|
95 | for col_j in range(num_cols):
|
---|
96 | cell = worksheet.cell(row_i,col_j)
|
---|
97 |
|
---|
98 | cell_type = worksheet.cell_type(row_i,col_j)
|
---|
99 | cell_value = worksheet.cell_value(row_i,col_j)
|
---|
100 |
|
---|
101 | formatted_cell = format_excelval(workbook,cell_type,cell_value,False)
|
---|
102 |
|
---|
103 | if col_j == 0:
|
---|
104 | # Check to see if companion PDF file present
|
---|
105 | pdf_filename = os.path.join(excel_dirname,"pdfs",formatted_cell+".pdf")
|
---|
106 | if os.path.exists(pdf_filename):
|
---|
107 | found_pdf = True
|
---|
108 | pdf_file = os.path.join("pdfs",formatted_cell+".pdf")
|
---|
109 | formatted_utf8_row.insert(0, pdf_file)
|
---|
110 |
|
---|
111 | formatted_cell_utf8 = unicode(formatted_cell).encode("utf-8")
|
---|
112 | formatted_utf8_row.append(formatted_cell_utf8)
|
---|
113 | if found_pdf:
|
---|
114 | pdfbound_wr.writerow(formatted_utf8_row)
|
---|
115 | else:
|
---|
116 | unbound_wr.writerow(formatted_utf8_row)
|
---|
117 |
|
---|
118 | row_i = row_i + 1
|
---|
119 | else:
|
---|
120 | print "Failed to find metadata label 'Report Identifier' in column 0"
|
---|
121 |
|
---|
122 |
|
---|
123 | csv_pdfbound_ofile.close()
|
---|
124 | csv_unbound_ofile.close()
|
---|
125 |
|
---|
126 | if __name__ == "__main__":
|
---|
127 | excel_to_bound_pdf_csv(os.sys.argv[1])
|
---|
128 |
|
---|