Bulk CSV Import, Cleaning, and Synchronization with Google Sheets - n8n Workflow

Data analysts needing to consolidate multiple local CSV reports.
Marketing operations professionals managing subscription lists across various sources.
Users looking for robust n8n templates for data cleaning and synchronization tasks.
Anyone needing an efficient way to automate file operations using the n8n node system.

Data management often requires merging and cleaning data scattered across multiple files. This efficient n8n workflow is designed to streamline that process. It addresses the common challenge of integrating local CSV data—potentially containing duplicates or inactive records—into a centralized cloud spreadsheet like Google Sheets. By leveraging several key n8n node components, this automation ensures data quality before insertion. It’s an ideal example of how powerful n8n can be for handling file-based data transformations, providing a reliable, automated pipeline for data consolidation, filtration, and sorting before the final upload. This specific n8n workflow template demonstrates high-level data governance within the automation platform.

1. Initiation: The n8n workflow is activated manually using the "When clicking Execute Workflow" n8n trigger.


2. File Retrieval: The Read Binary Files n8n node scans a specified local directory (./.n8n/) for all .csv files and retrieves their binary data.


3. Batch Processing: The Split In Batches n8n node ensures that each CSV file is processed individually, preventing memory issues and allowing sequential data cleaning per source file.


4. Data Parsing: The Read CSV n8n node takes the binary data for the current batch and converts it into structured JSON items, making the data accessible for manipulation.


5. Deduplication: The Remove duplicates n8n node, an item lists operation, cleans the dataset by identifying and removing redundant records based on the username field.


6. Filtering: The Keep only subscribers n8n node filters the remaining records, retaining only items where the subscribed value is TRUE.


7. Sorting: The filtered data is then organized using the Sort by date n8n node, which sorts the remaining items based on the datesubscribed field.


8. Synchronization: Finally, the Upload to spreadsheet n8n node (Google Sheets) appends or updates the cleaned and sorted records into the designated spreadsheet using user_name as the key for matching existing rows. This concludes the execution of this robust n8n workflow.

1. Import: Copy the provided n8n workflow JSON code. Log into your n8n instance and navigate to "Workflows." Click "+ New" or "Import from JSON" and paste the code.


2. Google Sheets Credential Setup: Locate the Upload to spreadsheet n8n node. You must connect or create an OAuth2 credential for Google Sheets (named "Google Sheets account" in this n8n template). Ensure this credential has read/write access to your Google Drive and Sheets.


3. File Path Configuration: Review the Read Binary Files n8n node configuration. By default, it looks for CSV files in .n8n/*.csv. Adjust the File Selector to match the exact path where your CSV files are stored relative to your n8n installation directory.


4. Spreadsheet Configuration: In the Upload to spreadsheet n8n node, verify the Document ID and Sheet Name (currently configured for a sheet named n8n-sheet) match your target Google Sheet.


5. Execution: Save and activate the n8n workflow, then click "Execute Workflow" manually to run the process.

When clicking "Execute Workflow" (Manual Trigger n8n node): Function: The starting n8n trigger for the automation. Ideal for testing and scheduled runs, though currently configured for manual start.
Read Binary Files (n8n node): Function: Reads binary file contents from the local file system, targeting multiple files. Key Configuration: File Selector set to =./.n8n/.csv to capture multiple CSV files.
Split In Batches (n8n node): Function: Iterates over the list of found binary files, processing them one by one. Key Configuration: Batch Size set to 1.
Read CSV (SpreadsheetFile n8n node): Function: Parses the binary data of a single CSV file into structured JSON data items required for subsequent data manipulation. Key Configuration: File Format is csv and Raw Data is enabled.
Remove duplicates (Item Lists n8n node): Function: Performs essential data cleaning, removing redundant entries based on the username field.
Keep only subscribers (Filter n8n node): Function: Filters the records to maintain data quality, passing through only items where the subscribed property is strictly TRUE.
Sort by date (Item Lists n8n node): Function: Orders the resulting dataset before uploading. Key Configuration: Sorts by the datesubscribed field.

• Upload to spreadsheet (Google Sheets n8n node): Function: The final action. Takes the cleaned data and uses an appendOrUpdate operation to synchronize records with the target Google Sheet. Key Configuration: Uses user_name for row matching.