NodeJS遍历目录CSV文件并批量插入MySQL的实现求助

Hey there! Nice work getting the filenames and MySQL connection sorted out—let's build on that to read the file contents and insert the data along with the filenames. Here's a step-by-step approach using modern Node.js features and common libraries:

Step 1: Install Required Dependencies #

First, if you haven't already, install mysql2 (for promise-based MySQL interactions, way easier for async code) and csv-parser (lightweight CSV parsing):

npm install mysql2 csv-parser

Step 2: Full Implementation Code #

Here's a complete example that ties everything together. I'll break it down after the code:

const fs = require('fs').promises;
const path = require('path');
const csv = require('csv-parser');
const mysql = require('mysql2/promise');

// MySQL connection configuration (use your existing config here)
const dbConfig = {
  host: 'your-host',
  user: 'your-user',
  password: 'your-password',
  database: 'your-database'
};

async function processCsvFiles() {
  const csvDir = './your-csv-directory'; // Replace with your actual directory path
  let connection;

  try {
    // 1. Connect to MySQL
    connection = await mysql.createConnection(dbConfig);
    console.log('Connected to MySQL successfully');

    // 2. Get all CSV filenames in the directory
    const files = await fs.readdir(csvDir);
    const csvFiles = files.filter(file => path.extname(file).toLowerCase() === '.csv');

    // 3. Process each CSV file one by one
    for (const filename of csvFiles) {
      const filePath = path.join(csvDir, filename);
      console.log(`Processing file: ${filename}`);

      // Store filename in a variable (we'll attach this to each database row)
      const currentFilename = filename;

      // 4. Read and parse the CSV file
      const results = [];
      await new Promise((resolve, reject) => {
        fs.createReadStream(filePath)
          .pipe(csv())
          .on('data', (data) => results.push(data))
          .on('end', resolve)
          .on('error', reject);
      });

      // 5. Prepare insert queries (batch insert for better performance)
      if (results.length === 0) {
        console.log(`No data found in ${filename}, skipping`);
        continue;
      }

      // Assume your table has columns matching CSV headers + a 'filename' column
      const columns = Object.keys(results[0]).concat('filename');
      const values = results.map(row => [...Object.values(row), currentFilename]);

      const placeholders = values.map(() => `(${columns.map(() => '?').join(',')})`).join(',');
      const query = `INSERT INTO your-table-name (${columns.join(',')}) VALUES ${placeholders}`;

      // Execute the insert
      await connection.execute(query, values.flat());
      console.log(`Successfully inserted ${results.length} rows from ${filename}`);
    }

  } catch (error) {
    console.error('Error during processing:', error);
  } finally {
    // Close the database connection when done
    if (connection) {
      await connection.end();
      console.log('MySQL connection closed');
    }
  }
}

// Run the function
processCsvFiles();

Key Notes & Explanations #

• Async/Await & for...of: We use for...of instead of forEach because it waits for each async operation (file read, database insert) to finish before moving to the next file—this avoids race conditions that can mess up your data.
• CSV Parsing: csv-parser streams the CSV file, which is efficient even for large files. It converts each row into a plain object where keys match the CSV headers.
• Batch Insert: Instead of inserting one row at a time, we do a single batch insert per file. This is way faster and reduces unnecessary database calls.
• Filename Association: We attach the current filename to every row in the CSV, so you can always track which file each record came from in your database.
• Error Handling: The try/catch/finally block ensures we handle errors gracefully and always close the database connection, even if something goes wrong mid-process.

Things to Adjust for Your Setup #

• Replace your-host, your-user, etc., with your actual MySQL credentials.
• Update ./your-csv-directory to the path where your CSV files are stored.
• Change your-table-name to the name of your MySQL table, and make sure the CSV headers match your table's column names (plus add a filename column to your table if it doesn't exist already).

If you run into specific issues—like weird CSV formatting glitches or database constraint errors—feel free to share more details, and we can troubleshoot further!

内容的提问来源于stack exchange，提问作者Marcello Pato