Create individual CSV files from multi-value fields in Excel file.

create-csv-files-from-excel-multi-value-fields.ts

import fs from 'fs';
import readXlsxFile from 'read-excel-file/node';
import { Cell, Row } from 'read-excel-file/types';

const config = {
  inputFile: './file.xlsx',
  headers: 'ID,Period,Team',
  multiResponseHeaders: {
    something:
      'Some header'
  }
};

/**
 * @description This is used to create specific CSV files for multiple-response fields.
 */
function parse(path: string) {
  readXlsxFile(path).then((rows: Row[]) => {
    const headers = rows[0];

    config.multiResponseHeaders.forEach((header: string) => {
      processRows(rows, headers.indexOf(config.multiResponseHeaders[header]), header);
    });
  });
}

/**
 * @description Controls the overall row processing.
 */
function processRows(rows: Row[], fieldIndex: number, type: string) {
  const fields = processMultiResponseFields(rows, fieldIndex);
  writeFile(fields, `output-${type}.csv`);
}

/**
 * @description Process an individual row/line and its single or multiple responses.
 */
function processMultiResponseFields(rows: Row[], fieldIndex: number): string {
  const [idHeader, periodHeader, teamHeader] = config.headers.split(',');
  const headers = rows[0];
  const idIndex = headers.indexOf(idHeader);
  const periodIndex = headers.indexOf(periodHeader);
  const teamIndex = headers.indexOf(teamHeader);

  let rowsToWrite = '';

  rows.forEach((cells: Cell[]) => {
    const responses = getListOfResponses(cells[fieldIndex] as string);

    responses.forEach((response: string) => {
      const row = `${cells[idIndex]},${cells[periodIndex]},${getCommalessValue(
        cells[teamIndex] as string
      )},"${response}"\n`;
      rowsToWrite += row;
    });
  });

  return rowsToWrite;
}

/**
 * @description Gets a value without any commas.
 */
function getCommalessValue(value: string) {
  return value.toString().replaceAll(',', '');
}

/**
 * @description Split a comma-separated list of responses into a clean array of responses.
 */
const getListOfResponses = (responses: string) => {
  if (!responses) return [];
  return responses.split(';').filter((response: string) => response);
};

/**
 * @description Write a file to disk.
 */
const writeFile = (data: string, fileName: string) => fs.writeFileSync(fileName, data);

/**
 * @description Reads Excel files and outputs individual CSV files for any multi-response fields.
 */
function main() {
  parse(config.inputFile);
}

main();