CSV DB Importer

A small Node.js and TypeScript CLI that downloads the newest CSV file from an FTP server and imports missing users into a MySQL usuarios table.

Requirements

• Node.js 20 or newer
• MySQL
• Access to an FTP or FTPS server

Installation

npm install

Environment Configuration

Copy .env.example to .env and fill in the required values.

FTP_HOST=
FTP_PORT=21
FTP_USER=
FTP_PASSWORD=
FTP_SECURE=false
FTP_REMOTE_DIR=/

MYSQL_HOST=
MYSQL_PORT=3306
MYSQL_USER=
MYSQL_PASSWORD=
MYSQL_DATABASE=
MYSQL_CONNECTION_LIMIT=5

Set FTP_SECURE=true only when the server requires explicit FTPS.

Expected CSV Format

The CSV must include these headers:

nombre,email,numero_empleado
Juan Perez,juan@example.com,EMP001
Ana Lopez,ana@example.com,EMP002

Rows with missing nombre, email, or numero_empleado are skipped. Emails are trimmed, converted to lowercase, and validated with a basic email format check.

Expected MySQL Table

CREATE TABLE usuarios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  email VARCHAR(255) NOT NULL,
  numero_empleado VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_usuarios_email (email),
  UNIQUE KEY uq_usuarios_numero_empleado (numero_empleado)
);

Development Execution

npm run dev

Build And Production Execution

npm run build
npm start

Processing Flow

1. Connect to the FTP server.
2. List the configured remote directory.
3. Select the newest .csv file by remote modification date.
4. Read data/state.json.
5. Skip processing when the newest file has already been imported.
6. Download the file into downloads.
7. Parse and validate the CSV.
8. Normalize valid user rows and remove duplicate emails from the same CSV.
9. Query MySQL for existing emails in batches.
10. Insert missing users with INSERT IGNORE.
11. Save data/state.json only after the import completes successfully.

Processing State

Runtime state is stored in data/state.json:

{
  "lastFileName": "usuarios_2026-07-27.csv",
  "lastModifiedAt": "2026-07-27T14:30:00.000Z",
  "processedAt": "2026-07-27T14:35:00.000Z"
}

A remote file is processed when there is no previous state, when its modification date is newer than the stored date, or when the modification date is equal but the file name is different.

The state file is updated only after the CSV is parsed, validated, and imported successfully. If FTP, CSV, or MySQL work fails, the state remains unchanged so the same file can be retried on the next run.

Common Errors

• FTP connection errors: verify host, port, credentials, secure mode, and network access.
• No CSV files found: confirm FTP_REMOTE_DIR and make sure files end with .csv.
• CSV header errors: ensure the file includes nombre, email, and numero_empleado.
• CSV parsing errors: check delimiter consistency, quoting, and file encoding.
• MySQL connection errors: verify host, port, credentials, database name, and user permissions.
• MySQL duplicate errors: the import uses INSERT IGNORE, but unique indexes should still exist to protect the table.