import pyexcel_ods import os import sys # Create the output directory if it doesn't exist output_dir = 'OutputFiles' os.makedirs(output_dir, exist_ok=True) def rename_and_shorten(filename): # Remove existing file extension if present name_without_extension = os.path.splitext(filename)[0] # Shorten the name to 8 characters short_name = name_without_extension[:8] + '.csv' if os.path.isfile(os.path.join(output_dir, short_name)): # Rename the existing file to include "_OLD" before the extension new_name = short_name.rsplit('.', 1)[0] + "_OLD." + short_name.rsplit('.', 1)[1] os.rename(os.path.join(output_dir, short_name), os.path.join(output_dir, new_name)) return short_name def read_ods(filename, sheet_name): # Read data from an ODS file data = pyexcel_ods.get_data(filename) # Assume the third row is the header headers = data[sheet_name][2] return headers, [dict(zip(headers, row)) for row in data[sheet_name][3:] if row] def format_value(value): # Handle different data types for CSV output if value is None or value == "": return "-1" elif isinstance(value, str) and value.strip() == "NA": return "NA" elif isinstance(value, float): return f"{value:.2f}" elif isinstance(value, int): return str(value) return str(value).strip() def hold_to_csv_line(hold, headers): # Generate a CSV line from dictionary data return ','.join(format_value(hold.get(header, "NA")) for header in headers) def create_filtered_csv(headers, holds_data, output_filename, filter_func): # Select only the columns of interest (1, 6, 7, 8, 9) selected_columns = [headers[0], headers[5], headers[6], headers[7], headers[8]] # Handle filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) with open(full_path, 'w') as file: # Write the selected header columns file.write(','.join(selected_columns) + "\n") # Filter and write each row for hold in holds_data: if filter_func(hold): file.write(hold_to_csv_line(hold, selected_columns) + "\n") def create_csv_from_ods(input_filename, output_filename, sheet_name): # Manage filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) # Read the specified sheet from ODS headers, holds_data = read_ods(input_filename, sheet_name) # Select only the columns of interest selected_columns = [headers[0], headers[5], headers[6], headers[7], headers[8]] with open(full_path, 'w') as file: # Write the selected header columns file.write(','.join(selected_columns) + "\n") # Write each row data for hold in holds_data: file.write(hold_to_csv_line(hold, selected_columns) + "\n") return headers, holds_data # Return headers and holds_data for further processing def create_panel_csv_by_wall_type(headers, holds_data, output_filename, wall_type): # Filter function for specific wall type and non-empty hold type def filter_for_panel(hold): return hold.get('WallType') == wall_type and hold.get('HoldType') # Manage filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) # Call the filtered CSV creation function with the specified filter create_filtered_csv(headers, holds_data, output_filename, filter_for_panel) def create_filtered_csv_by_criteria(headers, holds_data, output_filename, criteria_key, criteria_value=None): # Function to filter data rows based on given criteria def filter_func(hold): condition = False # Adjusting comparison for integer values in "Panel" column if criteria_key == 'Panel' and criteria_value is not None: # Ensure comparison is between integers panel_value = int(hold.get(criteria_key, -1)) # Default to -1 if not found criteria_value_int = int(criteria_value) # Convert criteria_value to integer condition = panel_value == criteria_value_int elif criteria_value is None: condition = not hold.get(criteria_key) elif criteria_value == "ANY": condition = hold.get(criteria_key) is not None and hold.get(criteria_key) != "" else: # This path is for non-integer comparisons, keep as string comparison condition = hold.get(criteria_key) == criteria_value # Additional condition for OCC files if 'OCC' in output_filename: condition = condition and hold.get('HoldType', '').strip() != "" return condition # Manage filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) # Call the existing 'create_filtered_csv' function with the new filter create_filtered_csv(headers, holds_data, output_filename, filter_func) def create_holdtype_csv(headers, holds_data, output_filename, hold_type_presence): # Filter function for specified HoldType presence or absence def filter_for_holdtype(hold): if hold_type_presence == "present": return hold.get('HoldType', "").strip() != "" else: # Assuming hold_type_presence == "absent" return hold.get('HoldType', "").strip() == "" # Manage filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) # Call the existing 'create_filtered_csv' function with the new filter create_filtered_csv(headers, holds_data, output_filename, filter_for_holdtype) def create_led_data_csv(headers, holds_data, output_filename): # Define the columns to include in the LEDData.csv, if needed adjust to actual LED column indices # Assuming LED data columns are the 6th, 7th, 8th, and 9th columns (as index 5, 6, 7, 8) led_columns_indices = [5, 6, 7, 8] led_columns = [headers[i] for i in led_columns_indices] # Adjust indices according to actual data # Manage filename conflicts output_filename = rename_and_shorten(output_filename) # Create full path for file output full_path = os.path.join(output_dir, output_filename) with open(full_path, 'w') as file: # Write the header for the selected columns file.write(','.join(led_columns) + "\n") # Iterate over each row in holds_data for hold in holds_data: # Select only the values for the specified columns, handling missing columns with defaults row_values = [format_value(hold.get(col, "NA")) for col in led_columns] # Write the selected values to the file file.write(','.join(row_values) + "\n") if __name__ == "__main__": if len(sys.argv) < 2: print("Usage: python script_name.py ") sys.exit(1) ods_file_name = sys.argv[1] headers, holds_data = create_csv_from_ods(ods_file_name, 'MetaData.csv', 'HoldMetaData') # Create CSV files using the defined functions create_led_data_csv(headers, holds_data, 'LEDData.csv') # Now correctly defined and called # Example loop to generate CSV files for different panels for panel_num in range(1, 25): # Loop from Panel 1 to 24 create_filtered_csv_by_criteria(headers, holds_data, f'Pan{panel_num:02d}All.csv', 'Panel', str(panel_num)) create_filtered_csv_by_criteria(headers, holds_data, f'Pan{panel_num:02d}OCC.csv', 'Panel', str(panel_num)) # Updated calls for panel-specific CSVs using the new generalized function create_panel_csv_by_wall_type(headers, holds_data, 'PanelReg.csv', 'regular') create_panel_csv_by_wall_type(headers, holds_data, 'PanelOve.csv', 'overhang') create_panel_csv_by_wall_type(headers, holds_data, 'PanelCei.csv', 'ceiling') # Calls for material-specific CSVs using the generalized function create_filtered_csv_by_criteria(headers, holds_data, 'MaPlywoo.csv', 'Material', 'plywood') create_filtered_csv_by_criteria(headers, holds_data, 'MaTree.csv', 'Material', 'tree') create_filtered_csv_by_criteria(headers, holds_data, 'MaResin.csv', 'Material', 'resin') create_filtered_csv_by_criteria(headers, holds_data, 'MaStone.csv', 'Material', 'stone') # Calls for the new filters create_filtered_csv_by_criteria(headers, holds_data, 'HoldOpen.csv', 'HoldType', None) create_filtered_csv_by_criteria(headers, holds_data, 'HoldHold.csv', 'HoldType', "ANY") create_filtered_csv_by_criteria(headers, holds_data, 'Hangers.csv', 'HoldType', 'hanger') # Calls for the new filters based on "Hold Category" and "Hold Type" create_filtered_csv_by_criteria(headers, holds_data, 'HoFoot.csv', 'HoldCategory', 'foot') create_filtered_csv_by_criteria(headers, holds_data, 'HoHand.csv', 'HoldCategory', 'hand') create_filtered_csv_by_criteria(headers, holds_data, 'HoCrimps.csv', 'HoldType', 'crimp') create_filtered_csv_by_criteria(headers, holds_data, 'HoJug.csv', 'HoldType', 'Jug') create_filtered_csv_by_criteria(headers, holds_data, 'HoSloper.csv', 'HoldType', 'sloper') # Calls for filtering based on "Difficulty" create_filtered_csv_by_criteria(headers, holds_data, 'DiEasy.csv', 'Difficulty', 'easy') create_filtered_csv_by_criteria(headers, holds_data, 'DiMedium.csv', 'Difficulty', 'medium') create_filtered_csv_by_criteria(headers, holds_data, 'DiHard.csv', 'Difficulty', 'hard') create_filtered_csv_by_criteria(headers, holds_data, 'DiEvil.csv', 'Difficulty', 'evil') # Calls for filtering based on "Color" create_filtered_csv_by_criteria(headers, holds_data, 'CoWood.csv', 'Color', 'wood') create_filtered_csv_by_criteria(headers, holds_data, 'CoPink.csv', 'Color', 'pink') create_filtered_csv_by_criteria(headers, holds_data, 'CoRed.csv', 'Color', 'red') create_filtered_csv_by_criteria(headers, holds_data, 'CoPurple.csv', 'Color', 'purple') create_filtered_csv_by_criteria(headers, holds_data, 'CoBlue.csv', 'Color', 'blue') create_filtered_csv_by_criteria(headers, holds_data, 'CoGreen.csv', 'Color', 'green') create_filtered_csv_by_criteria(headers, holds_data, 'CoMarble.csv', 'Color', 'marble') create_filtered_csv_by_criteria(headers, holds_data, 'CoWhite.csv', 'Color', 'white') create_filtered_csv_by_criteria(headers, holds_data, 'CoGray.csv', 'Color', 'gray') # Calls for filtering based on "Size" create_filtered_csv_by_criteria(headers, holds_data, 'SiTiny.csv', 'Size', 'tiny') create_filtered_csv_by_criteria(headers, holds_data, 'SiSmall.csv', 'Size', 'small') create_filtered_csv_by_criteria(headers, holds_data, 'SiMedium.csv', 'Size', 'medium') create_filtered_csv_by_criteria(headers, holds_data, 'SiLarge.csv', 'Size', 'large') # Call for creating LEDData.csv create_led_data_csv(headers, holds_data, 'LEDData.csv')