1 | import xlrd
|
---|
2 | import csv
|
---|
3 | import os
|
---|
4 |
|
---|
5 | import xlsutil
|
---|
6 |
|
---|
7 | ## worksheet_name = "Archaeological reports"
|
---|
8 |
|
---|
9 |
|
---|
10 | def excel_to_sanitized_csv(excel_filename):
|
---|
11 | workbook = xlrd.open_workbook(excel_filename)
|
---|
12 | # worksheet = workbook.sheet_by_name(worksheet_name)
|
---|
13 | worksheet = workbook.sheet_by_index(0)
|
---|
14 |
|
---|
15 | excel_filename_split = os.path.split(excel_filename)
|
---|
16 | excel_dirname = excel_filename_split[0]
|
---|
17 | excel_file = excel_filename_split[1]
|
---|
18 | excel_file_splitext = os.path.splitext(excel_file)
|
---|
19 | excel_file_root = excel_file_splitext[0];
|
---|
20 |
|
---|
21 | sanitized_csv_filename = os.path.join(excel_dirname,excel_file_root+".csv")
|
---|
22 |
|
---|
23 | # print "Worksheet: " + worksheet_name
|
---|
24 | sanitized_csv_ofile = open(sanitized_csv_filename, 'wb')
|
---|
25 |
|
---|
26 | sanitized_csv_wr = csv.writer(sanitized_csv_ofile, quoting=csv.QUOTE_ALL)
|
---|
27 |
|
---|
28 | # 1. For header line, Build up hashmap of header-names to column number
|
---|
29 | # 2. Write out CSV file for only the header-names in 'sanitized_headers'
|
---|
30 |
|
---|
31 | num_rows = worksheet.nrows
|
---|
32 |
|
---|
33 | # row_i = 0
|
---|
34 | # num_header_cols = worksheet.row_len(row_i)
|
---|
35 |
|
---|
36 | # 1. get header-map
|
---|
37 | header_names_mapping = xlsutil.getHeaderMappingToColNum(worksheet)
|
---|
38 |
|
---|
39 | # found_header = False
|
---|
40 | # for col_j in range(num_header_cols):
|
---|
41 | # header_cell_value = worksheet.cell_value(row_i,col_j)
|
---|
42 | # if header_cell_value == "Report Identifier":
|
---|
43 | # found_header = True
|
---|
44 | #
|
---|
45 | # header_names_mapping[header_cell_value] = col_j
|
---|
46 | #
|
---|
47 | # if not(found_header):
|
---|
48 | if header_names_mapping is None:
|
---|
49 | print "Failed to find \"Report Identifier\" header in spreadsheet (Row 0 or Sheet 0)"
|
---|
50 | exit(1)
|
---|
51 |
|
---|
52 | # Move on to the start of the data values
|
---|
53 | row_i = 1
|
---|
54 |
|
---|
55 | # 2. Write out CSV file ...
|
---|
56 | while row_i<num_rows:
|
---|
57 | # Work through sanitized header names building up row of utf8 values
|
---|
58 | sanitized_utf8_row = []
|
---|
59 |
|
---|
60 | for header_name in xlsutil.sanitized_headers:
|
---|
61 |
|
---|
62 | if header_name in header_names_mapping:
|
---|
63 | header_col_j = header_names_mapping[header_name]
|
---|
64 |
|
---|
65 | cell_value = worksheet.cell_value(row_i,header_col_j)
|
---|
66 |
|
---|
67 |
|
---|
68 |
|
---|
69 | cell_value_utf8 = unicode(cell_value).encode("utf-8")
|
---|
70 |
|
---|
71 | sanitized_utf8_row.append(cell_value_utf8)
|
---|
72 | else:
|
---|
73 | print("Warning: Failed to column mapping in spreadsheet for header name \""+header_name+"\" => skipping")
|
---|
74 |
|
---|
75 | sanitized_csv_wr.writerow(sanitized_utf8_row)
|
---|
76 |
|
---|
77 | row_i = row_i + 1
|
---|
78 |
|
---|
79 | sanitized_csv_ofile.close()
|
---|
80 |
|
---|
81 | if __name__ == "__main__":
|
---|
82 | excel_to_sanitized_csv(os.sys.argv[1])
|
---|
83 |
|
---|