Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-05-13· OpenAIAi2 WildChat-4.8M (AI2)

Write Python script to make and save Excel (.xlsx) table from list of dicts like…

  1. User

    Write Python script to make and save Excel (.xlsx) table from list of dicts like [{"column1": "value", "column2": "value2"}, {"column1": "value", "column2": "value2"}]

  2. Assistant

    Certainly! You can use the openpyxl library in Python to create and save an Excel (.xlsx) file from a list of dictionaries. First, you'll need to install the openpyxl library if you haven't already. You can do this using pip:

    pip install openpyxl
    

    Here is the Python script that does what you need:

    import openpyxl
    
    def save_dicts_to_excel(data, filename):
        # Create a new workbook and select the active worksheet
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # Make sure the data is not empty
        if not data:
            print("No data to save")
            return
    
        # Create headers from the keys of the first dictionary
        headers = list(data[0].keys())
        ws.append(headers)
    
        # Append the rows
        for entry in data:
            row = [entry.get(column) for column in headers]
            ws.append(row)
    
        # Save the workbook
        wb.save(filename)
        print(f"Data saved to {filename}")
    
    # Example usage
    data = [
        {"column1": "value1", "column2": "value2"},
        {"column1": "value3", "column2": "value4"}
    ]
    
    save_dicts_to_excel(data, "output.xlsx")
    

    This script creates an Excel file named output.xlsx with the data provided in your list of dictionaries. Here is a step-by-step breakdown of the script:

    1. Import the openpyxl library.
    2. Define the function save_dicts_to_excel(data, filename):
      • Create a new workbook using openpyxl.Workbook().
      • Select the active worksheet to write data into.
      • Check if the data list is empty, and if so, print a message and return.
      • Create headers from the keys of the first dictionary and append them as the first row.
      • Iterate through the list of dictionaries, create rows for each dictionary, and append them to the worksheet.
      • Save the workbook to a file with the provided filename.
    3. Call the function with example data and a filename to save the Excel file as output.xlsx.

    Feel free to modify the data list and filename variable as needed for your specific use case.