Update an existing schedule from a modified CSV

[theoryshaw]

Is your feature request related to a problem? Please describe.
It seems, although i might be wrong, that there's no way to update a schedule from an external CSV that has been updated. If an external CSV is updated, you have to 'Create Schedule from CSV', and then delete the old one. So any formatting you did on the previous schedule is lost, and you have to recreate the formatting on the new schedule.

Describe the solution you'd like
Just a simple way to import or link in updated data from an external CSV, without having to create a new schedule in revit.

[jmcouffin]

You are right, not with the actual tool.
I use DiRoots Sheetlink to do such things.
Another way would be to create a schedule template from the original and apply it to the newly created one.

Doing such a tool is no small task, I think, top of my head this implies:

• grab the parameters
• deal with their units
• get the categories and elements they are applied to
• extract all of that as a csv
• import the csv
• check the modified data against the actual schedule
• deal with errors
• set parameter values of elements
• create new elements in Revit if new lines are created (doable for rooms and spaces, but unlikely friendly for other categories as they need a location...)

[jmcouffin]

If you would like to start, I could give a hand, but I think the SheetLink from DiRoots, being free, kind of kills it, unless it adds more water to the well than Sheetlink

[davhoe]

Totally agree with you on DiRoots but they are about to start charging for SheetLink specifically, some of my collegues cannot use it anymore.

I don't have much in the way of coding skills but understand the basics of code and have been using PyRevit specific gpt's to code a bunch of things. Exporting the excel file was easy but the importing has been difficult.

We use excel to update our revisions(in this case sheet parameters in a schedule) on our titleblock.

The closest I've managed to get was this, attempting to use a Sheet Number as a kind of unique identifier with the code below and while it checks and recognises that values have changed it will not update the value inside of the revit schedule.

#pylint: disable=import-error
import xlrd  # For reading Excel files
from Autodesk.Revit.DB import FilteredElementCollector, Transaction, ViewSchedule, SectionType
from pyrevit import forms

# Function to import and update schedule data based on the Excel file
def import_excel_to_revit(file_path, schedule):
    # Open the Excel file
    workbook = xlrd.open_workbook(file_path)
    worksheet = workbook.sheet_by_index(0)  # Assuming the first sheet contains the data
    
    # Get the headers from the Excel file (first row)
    headers = worksheet.row_values(0)
    print("Headers from Excel:", headers)  # Debugging to check the headers
    
    # Make sure 'Sheet Number' is in the headers
    if 'Sheet Number' not in headers:
        forms.alert("Excel file does not contain 'Sheet Number' as the identifier.", exitscript=True)
    
    # Find the index of the 'Sheet Number' column and other data columns
    sheet_number_index = headers.index('Sheet Number')  # Index of the Sheet Number column
    data_field_indices = {field: idx for idx, field in enumerate(headers) if field != 'Sheet Number'}
    
    # Open a transaction in Revit to modify the schedule
    doc = __revit__.ActiveUIDocument.Document
    with Transaction(doc, "Import Schedule Data from Excel") as t:
        t.Start()
        
        # Get the table data of the schedule
        schedule_data = schedule.GetTableData()
        section_data = schedule_data.GetSectionData(SectionType.Body)  # Body section of the schedule
        
        # Find the column index for 'Sheet Number' in the schedule
        visible_fields = schedule.Definition.GetFieldOrder()  # Get indices of visible fields
        sheet_number_col_index = None
        for col, field_id in enumerate(visible_fields):
            field = schedule.Definition.GetField(field_id)
            if field.GetName() == "Sheet Number":  # Look for the 'Sheet Number' column
                sheet_number_col_index = col
                break
        
        if sheet_number_col_index is None:
            forms.alert("Sheet Number column not found in the Revit schedule", exitscript=True)
        
        # Debugging: Print field mappings
        print("Field mappings between Excel and Revit:")
        for col, field_id in enumerate(visible_fields):
            field = schedule.Definition.GetField(field_id)
            print("Revit Column {}: {}".format(col, field.GetName()))  # Debugging
        
        # Loop through each row in the Excel file (starting from row 1, skipping headers)
        for row_idx in range(1, worksheet.nrows):
            row_values = worksheet.row_values(row_idx)
            sheet_number = row_values[sheet_number_index]  # Get the sheet number from the Excel row
            print("Processing Excel row with Sheet Number: {}".format(sheet_number))  # Debugging
            
            # Look for the matching Sheet Number in the schedule
            match_found = False
            for schedule_row in range(1, section_data.NumberOfRows):
                schedule_sheet_number = schedule.GetCellText(SectionType.Body, schedule_row, sheet_number_col_index)
                if schedule_sheet_number == sheet_number:
                    match_found = True
                    print("Matching Sheet Number found in schedule row {}. Updating values...".format(schedule_row))  # Debugging
                    # Update the schedule based on the matching Sheet Number
                    for field_name, col_idx in data_field_indices.items():
                        new_value = row_values[col_idx]  # Get the new value from the Excel file
                        
                        # Debugging: Print the field name and new value from Excel
                        print("Updating field: {}, New value: {}".format(field_name, new_value))
                        
                        # Find the corresponding column in the schedule and update the value
                        for col in range(section_data.NumberOfColumns):
                            column_header = schedule.GetCellText(SectionType.Header, 0, col)
                            if column_header == field_name:
                                # Convert numeric types to string for comparison
                                if isinstance(new_value, (int, float)):
                                    new_value = str(new_value)
                                # Update the cell in the schedule with the new value from Excel
                                schedule.SetCellText(SectionType.Body, schedule_row, col, new_value)
                                print("Updated {} in row {} to {}".format(field_name, schedule_row, new_value))  # Debugging
                    break  # Stop searching once a match is found
            if not match_found:
                print("No matching Sheet Number found for Excel row {}".format(row_idx))  # Debugging
        
        t.Commit()

# Prompt the user to select an Excel file
file_path = forms.pick_file(file_ext='xlsx')
if not file_path:
    forms.alert("No Excel file selected.", exitscript=True)

# Collect all schedules in the project
doc = __revit__.ActiveUIDocument.Document
schedules = FilteredElementCollector(doc).OfClass(ViewSchedule).WhereElementIsNotElementType().ToElements()

# Create UI for schedule selection
schedule_names = {sch.Name: sch for sch in schedules}
selected_schedule_name = forms.SelectFromList.show(schedule_names.keys(), title="Select Schedule", multiselect=False)

if selected_schedule_name:
    selected_schedule = schedule_names[selected_schedule_name]
    
    # Import data from Excel into the selected schedule
    import_excel_to_revit(file_path, selected_schedule)