Data Synchronization between Sheets/Excel and Postgres Database - n8n Workflow

• Data Analysts who need to push spreadsheet data into a structured SQL environment.


• Organizations transitioning from heavy spreadsheet use to database-driven dashboards.


• n8n users seeking robust n8n templates for data migration and synchronization.


• Developers needing a reliable n8n workflow to handle complex data type conversions (like Excel date formats).

Maintaining consistency between frequently updated spreadsheets and a centralized database can be challenging. This powerful n8n workflow solves this by providing a reliable synchronization pipeline. It allows users to read tables from Microsoft Excel (or Google Sheets, using similar connectivity) and perform an intelligent upsert (update or insert) operation into a PostgreSQL database. A critical feature of this n8n template is the inclusion of a custom Code n8n node designed specifically to handle tricky Excel date serial numbers, ensuring data integrity before insertion. By utilizing multiple n8n trigger options, this n8n workflow offers flexibility for manual, scheduled, or execution-based synchronization, making it a highly adaptable solution for any data-driven operation.

This n8n workflow is initiated by one of three separate n8n trigger nodes, providing maximum deployment flexibility:

1. Trigger Initiation: The synchronization can be started manually using the Click Sync Excel -> DB n8n trigger (Manual Trigger), executed on a recurring basis via the Schedule Sync Excel -> DB n8n trigger (Schedule Trigger), or called as a sub-workflow using the Exec Sync Excel -> DB n8n trigger (Execute Workflow Trigger).


2. Data Retrieval: Regardless of the n8n trigger used, the flow proceeds to the Get Table Microsoft Excel n8n node. This n8n node connects to the specified workbook and worksheet (configured to 'CLIENTS' in this template) and retrieves up to 200 rows of data.


3. Data Sanitization: The data then flows into the Sanitize Date Code n8n node. This custom script is essential because Excel often stores dates as serial numbers (counting days since December 30, 1899). The n8n code node detects if the date field is a number or a string, converts it into a standard JavaScript Date object, and then formats it uniformly as MM/DD/YYYY.


4. Database Upsert: Finally, the cleaned data reaches the Upsert Table Postgres n8n node. This n8n node performs an 'upsert' operation on the target 'clients' table. It uses the no column as the matching identifier (primary key). If a row with a matching no exists, it updates the row; otherwise, it inserts a new row. This ensures that the Postgres database remains a perfectly synchronized mirror of the Excel sheet, thanks to this robust n8n workflow.

To deploy this comprehensive n8n workflow, follow these steps:

1. Import the n8n workflow: Copy the provided JSON and import it directly into your n8n instance via the 'New' menu and 'Import from JSON' option.


2. Configure Microsoft Excel Credentials: Locate the Get Table Microsoft Excel n8n node. You must set up or select OAuth2 credentials to grant n8n access to your Excel Online or OneDrive files. Ensure the correct Workbook and Worksheet names are selected.


3. Configure Postgres Credentials: Open the Upsert Table Postgres n8n node. You will need to create or select a credential set providing the necessary connection details (host, database name, user, password) for your PostgreSQL instance.


4. Table Mapping: Verify that the input column names from the Excel n8n node exactly match the target column names in the PostgreSQL table (clients). The Postgres n8n node is configured for 'autoMapInputData' for convenience.


5. Testing: Select one of the n8n trigger nodes (e.g., Click Sync Excel -> DB) and execute the n8n workflow to test the synchronization.

Click Sync Excel -> DB (Manual Trigger): Serves as an immediate, manual starting point for the n8n workflow, allowing on-demand data synchronization.
Schedule Sync Excel -> DB (Schedule Trigger): This powerful n8n trigger allows the automation to run automatically at predetermined intervals (e.g., hourly, daily) to ensure continuous synchronization.
Get Table (Microsoft Excel n8n node): Connects to Microsoft Excel/OneDrive to fetch data. It is configured to pull all rows (up to the defined limit) from the 'CLIENTS' worksheet, initiating the data flow of this n8n template.
Sanitize Date (Code n8n node): A critical custom Code n8n node. Its function is to convert complex date formats, specifically handling Excel's numerical date serialization (days since 1899) and various date string formats, normalizing them to MM/DD/YYYY for consistent database storage.

• Upsert Table (Postgres n8n node): The final action node. It executes an 'upsert' operation, relying on the no column to match records. This ensures that the n8n workflow efficiently updates existing database entries instead of creating duplicates, vital for robust data synchronization.