Saving Retrieved CSV Data into a Database

Saving CSV Data from a Third-Party API into a Database

When data retrieved from a third-party API is returned in CSV format, a common and reliable approach is:

1. First, save the CSV file to a local path
2. Then use fs.createReadStream to read the file
3. Finally, parse the CSV content and store it in a local database

Below is an example implementation:

const processConversations = async (dateObj) => {
  const username = 'your_account_name';
  const password = 'password';

  const year = dateObj.format('YYYY');
  const month = dateObj.format('MM');
  const day = dateObj.format('DD');
  const hour = dateObj.format('HH');
  const dateStr = `${year}-${month}-${day}-${hour}`;
  const fileName = `partial_path`;
  const url = `https://api/${fileName}`;
  const outputPath = `/tmp/conversations`;
  const tableName = 'test';

  try {
    // Download CSV
    const response = await axios.get(url, {
      responseType: 'stream',
      headers: {
        Authorization: `Basic ${Buffer.from(`${username}:${password}`).toString('base64')}`
      }
    });

    const writer = fs.createWriteStream(outputPath);
    response.data.pipe(writer);
    await new Promise((resolve, reject) => {
      writer.on('finish', resolve);
      writer.on('error', reject);
    });

    console.log('CSV file downloaded successfully:', fileName);

    const rows = [];

    await new Promise((resolve, reject) => {
      fs.createReadStream(outputPath)
        .pipe(csv())
        .on('data', (row) => {
          rows.push([
            row['DATE_TIME']
              ? dayjs(row['DATE_TIME']).format('YYYY-MM-DD HH:mm:ss')
              : null,
            row['CLID'] || null,
            row['CLID_SOURCE'] || null
          ]);
        })
        .on('end', resolve)
        .on('error', reject);
    });

    if (rows.length === 0) {
      console.warn(`No data found in CSV: ${fileName}`);
      return;
    }

    const insertSQL = `
      INSERT INTO \`${tableName}\` (
        date_time, clid, clid_source
      ) VALUES ?
    `;

    await query(insertSQL, [rows]);
    console.log(`Successfully inserted ${rows.length} records: ${fileName}`);
  } catch (error) {
    console.error(`Error (${fileName}):`, error.message);
  }
};