CSV File Import Automation to Postgres Database - n8n Workflow

• System administrators needing to import recurring data dumps.


• Developers automating local data ingestion processes.


• Data engineers seeking a low-code solution for moving files into SQL databases.


• Users looking for practical n8n templates for database operations.

Manually importing flat files like CSVs into a relational database can be time-consuming and prone to errors. This specialized n8n workflow provides a robust solution by automating the entire pipeline. Starting from a simple manual n8n trigger, the system reads the CSV, converts the binary data into structured data objects, and utilizes the powerful Postgres n8n node to perform a bulk insertion. This n8n workflow ensures data integrity and drastically reduces the time spent on repetitive data migration tasks, making it an essential automation for any server environment requiring data loading.

This automation is initiated by a manual n8n trigger, designed for scheduled or on-demand execution. The flow starts with the 'On clicking 'execute'' n8n trigger.

1. File Retrieval: The 'Read From File' n8n node immediately accesses the specified path (/tmp/t1.csv) on the host machine where n8n is running, retrieving the binary content of the CSV file.


2. Data Conversion: The binary output is passed to the 'Convert To Spreadsheet' n8n node. This essential step transforms the raw CSV binary data into a structured array of JSON items, suitable for database interaction.


3. Database Insertion: The structured data proceeds to the 'Postgres' n8n node. This n8n node is configured to target the table t1 within the public schema. It uses automatic column mapping to ensure the input data fields correctly align with the database columns (id and name), completing the data import process. This entire process defines a highly efficient data loading n8n workflow.

To deploy this n8n template, follow these steps:

1. Import the JSON: Copy the provided JSON data and paste it directly into your n8n instance using the 'New' > 'Import from JSON' feature.


2. Set up Credentials:

1. Database Schema:

1. Execution: Use the 'Execute Workflow' button to test the functionality of this n8n workflow. Note that the initial n8n trigger is manual.

On clicking 'execute' (Manual Trigger n8n trigger):
Function: Starts the n8n workflow execution manually.
Key Configuration: Simple manual execution setup, ideal for testing or scheduled runs where an external event is not needed.
Read From File (Read Binary File n8n node):
Function: Retrieves the content of the specified local CSV file as binary data.
Key Configuration: File Path is set to /tmp/t1.csv.
Convert To Spreadsheet (Spreadsheet File n8n node):
Function: Converts the incoming binary CSV data into usable, structured JSON data items. This is crucial for enabling easy database mapping.
Key Configuration: Default conversion settings are used to handle standard CSV formats.
Postgres (Postgres n8n node):
Function: Connects to the PostgreSQL database and performs an upsert or insertion of the converted structured data.
Key Configuration:
Table: t1 (in schema public).
Mapping: Uses autoMapInputData to match input data fields to database columns defined as id (number) and name (string).
* Credentials: Requires configured Postgres credentials.