Writing Data to Microsoft Excel Tables

Introduction

An Excel table is more than a block of cells styled to look like a table. Excel tables have a header row with filter buttons, styles, and structured references for formulas. They also grow automatically when rows are added.

Starting in FME 2025.2, the Microsoft Excel writer can create new tables and insert, update, or delete rows in existing tables. Before FME 2025.2, the writer could not change the size of a table. Data written into a table in a template file did not extend the table, so a named range in a template file was the usual workaround.

In this article, you will write a CSV customer list to a new Excel table. Then you will keep the table current by updating customers whose occupation changed and deleting customers who closed their accounts. If you need to fill charts or pivot tables in a template file, please see Using a Template File when Writing Excel Data.

The source data and workspace template for this tutorial can be downloaded from the Files section.

Requirements

• FME Form 2025.2 or later. 

Step-by-Step Instructions

This article has three parts:

• Part 1: Create a new table from a CSV file (Customers.csv) and add four new customers using NewCustomers.csv to grow the table
• Part 2: Update rows in the table using OccupationUpdates.csv
• Part 3: Delete rows from the table using ClosedAccounts.csv

The completed workspace in the Files section has three bookmarks, one for each part. Follow the part that matches the task, or open the workspace to see how each example is set up.

Part 1: Write Data to a New Excel Table

In Part 1, you will read a CSV file of customers and write the records to a new table called CustomerTable on a sheet called Customers. Each customer has a CustomerID, Name, and Occupation. You do not need a template file. If the table does not exist, the writer creates it.

1. Add a CSV Reader

Open FME Workbench and start a new workspace. On the top menu, click Add Reader. In the Add Reader dialog:

• Format: CSV (Comma Separated Value)
• Dataset: Customers.csv

2. Add a Microsoft Excel Writer

Click the Writer button from the Toolbar. In the Add Writer dialog:

• Format: Microsoft Excel
• Dataset: CustomerList.xlsx
• Sheet Definition: Automatic
• Parameters:
  • Overwrite Existing File: Yes

Leave Template File empty. You do not need a template to write to a table. Click OK.

3. Set the Table Name and Style

In the Feature Type dialog, set the sheet name so that it points to a table instead of a sheet:

• Sheet Name: Customers:CustomerTable
• Sheet Settings:
  • Table Style: Choose any style from the list
  • Output Field Names: Yes
• (Optional) Start Position:
  • Named Range/Table Start Column: 1
  • Named Range/Table Start Row: 1

The colon tells the writer to write to a table called CustomerTable on the Customers sheet. If the table does not exist, the writer creates it at the start column and start row. If the table already exists, the writer uses the existing table and ignores the start column and start row.

The writer uses the feature type name to decide where to write:
  • Customers writes to a sheet.
  • Customers:CustomerTable writes to a table in the Customers sheet.
  • Customers/CustomerRange writes to a named range in the Customers sheet.
 

Use a table when people will sort, filter, or add rows in Excel. Use a named range when a template file has charts or pivot tables that refer to a fixed block of cells.

Click OK, then connect the writer feature type to the CSV reader feature type.

4. Run the Workspace and View the Output

Save and run the workspace. Open CustomerList.xlsx in Microsoft Excel. The Customers sheet contains a table called CustomerTable with 10 customers. The header row has filter buttons, and the Table Design tab shows the table name and style.

Next, we add new customers to the existing table. NewCustomers.csv contains four customers who are not in the table yet. You can reuse the same workspace and writer. The writer stays in Insert mode, so it adds the new rows to the end of the table, and the table grows to include them.

5. Change the Source Dataset and Overwrite Setting

In the Navigator, expand the CSV reader and double-click Source CSV File(s). Browse to NewCustomers.csv and click OK.

• Source CSV: NewCustomers.csv

Next, expand the Microsoft Excel writer in the Navigator and set the following:

• Overwrite Existing File: No

If Overwrite Existing File is set to Yes, the writer replaces the whole file, and the table contains only the four new customers.

6. Run the Workspace and View the Expanded Table

Save and run the workspace. Open CustomerList.xlsx in Microsoft Excel. The table now has 14 customers. Customers 1011 to 1014 are added below the original 10 rows, and the table border, style, and filter buttons expand to include them. The header row is not written again.

Part 2: Update Rows in an Existing Table

The customer list changes over time. Three customers have a new occupation, listed in OccupationUpdates.csv. In Part 2, you will match those customers to their rows in the table and update the Occupation value. The writer changes each row in place, so the table keeps its name, style, and size.

1. Add a Microsoft Excel Reader

Add a Reader and in the Add Reader dialog, set:

• Format: Microsoft Excel
• Dataset: CustomerList.xlsx
• Parameters:
  • Select Feature Types (Sheet): Customers:CustomerTable

Click OK. 

The reader feature type is named Customers:CustomerTable. Each feature has an xlsx_row_id attribute that stores its row number. The writer uses this value to find the row to change. 

To expose the xlsx_row_id attribute, double-click the Excel reader. In the dialog:

• Format Attributes: 
  • xlsx_row_id: Enabled

2. Add a CSV Reader for the Occupation Updates

Add a CSV reader and set the following parameters in the Add Reader dialog:

• Format: CSV (Comma Separated Value)
• Dataset: OccupationUpdates.csv

Click OK. This file has two attributes: CustomerID and the new Occupation.

3. Add a FeatureJoiner

Add a FeatureJoiner to the canvas. Connect the Customers:CustomerTable reader feature type to the FeatureJoiner Left input port. Connect the OccupationUpdates reader feature type to the Right input port. Double-click the FeatureJoiner and set the following:

• Join Mode: Inner
• Attribute Conflict Resolution: Prefer Right
• Join On:
  • Left: CustomerID
  • Right: CustomerID

Click OK.

4. Add a Microsoft Excel Writer in Update Mode

Add a Writer and in the Add Writer dialog, set:

• Format: Microsoft Excel
• Dataset: CustomerList.xlsx
• Sheet Definition: Automatic
• Parameters:
  • Overwrite Existing File: No

Click OK. In the Feature Type dialog:

• Sheet Name: Customers:CustomerTable
• Sheet Settings:
  • Writer Mode: Update
  • Row Number Attribute: xlsx_row_id

In Update mode, the writer uses the row number in xlsx_row_id to find each row. It then replaces the cell values with the values on the feature. If an attribute is missing from the feature, the existing cell value is kept. 

Click OK, then connect the writer feature type to the FeatureJoiner Joined output port.

5. Run the Workspace and View the Updated Table

Save and run the workspace. Open CustomerList.xlsx in Microsoft Excel. The table still has 10 rows. Customer 1002 is now an Engineering Manager, customer 1005 is now an Urban Planner, and customer 1009 is now a Judge. All other rows are unchanged.

Part 3: Delete Rows from an Existing Table

Two customers closed their accounts, listed in ClosedAccounts.csv. In Part 3, you will match those customers to their rows in the table and delete them. The steps are the same as in Part 2, but the writer uses Delete mode instead of Update mode.

1. Add a CSV Reader for the Closed Accounts

Close CustomerList.xlsx in Microsoft Excel. Continuing from the Part 2 workspace, add another reader. In the Add Reader dialog, set:

• Format: CSV (Comma Separated Value)
• Dataset: ClosedAccounts.csv

Click OK. This file has one attribute: CustomerID.

2. Add a Second FeatureJoiner

Add another FeatureJoiner to the canvas. Connect the Customers:CustomerTable reader feature type to the Left input port. Connect the ClosedAccounts reader feature type to the Right port. Double-click the FeatureJoiner and set the following:

• Join Mode: Inner
• Join On:
  • Left: CustomerID
  • Right: CustomerID

Click OK. Only the two closed accounts leave the Joined port, each with its xlsx_row_id.

3. Add a Microsoft Excel Writer in Update Mode

Repeat Step 4 from Part 2 of this tutorial, except this time use Delete as the Writer Mode. 

Add a Writer and in the Add Writer dialog, set:

• Format: Microsoft Excel
• Dataset: CustomerList.xlsx
• Sheet Definition: Automatic
• Parameters:
  • Overwrite Existing File: No

Click OK. In the Feature Type dialog:

• Sheet Name: Customers:CustomerTable
• Sheet Settings:
  • Writer Mode: Delete
  • Row Number Attribute: xlsx_row_id

In Delete mode, the writer uses the row number in xlsx_row_id to find each row and deletes it from the table.

Click OK, then connect the Excel writer to the FeatureJoiner Joined output port.

5. Run the Workspace and View the Final Table

Save and run the workspace. Open CustomerList.xlsx in Microsoft Excel. Customers 1004 and 1007 are removed, and the table now has 8 rows. The occupation updates from Part 2 are still in place.

Additional Resources 

Using a Template File when Writing Excel Data [Article]

Microsoft Excel (XLSXW) Writer Parameters [Documentation]