Parse an XER with Javascript

I recently posted an article showcasing an application where you could import and xer and view the activities. If anyone is interested in some of the specific code used, have fun! This is partially generated via chatgpt. I obviously edited a few pieces, but know this works, as its what I am using.

The process basically takes your xer text file (the "data" in the function) and pushes into a nice array. There is a minor bug in that the first column is blank, so that when you actually push this into your database, just avoid the first column.

This function extracts your tasks from the xer file. You can extract the other tables by knowing the other table tables. Example, for your logic table, "TASKPRED" would what you would replace TASK with (make sure to exit your loop when you get to the next table). There is a more generic format where you can extract ALL the tables.

function parseXER_tasks(data) {
  const lines = data.split('\n');
  let inTaskSection = false;
  let columns = [];
  const tasks = [];

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i].trim();

    if (line.startsWith('%T') && line.endsWith('TASK')) {
      inTaskSection = true;
    } else if (inTaskSection && line.startsWith('%F')) {
      columns = line.slice(2).split('\t').map(col => col.trim());
    } else if (inTaskSection && line.startsWith('%R')) {
      const row = line.slice(2).split('\t').map(item => item.trim());
      const task = {};
      columns.forEach((col, index) => {
        task[col] = row[index];
      });
      tasks.push(task);
    } else if (line.startsWith('%T') && !line.endsWith('TASK')) {
      // End of TASK section
      inTaskSection = false;
    }
  }

  return tasks;
}        

To understand the process, you need to think very carefully and understand literally each minor thing. However, its all quite easy. Harder parts are what to do with your data. A key bit is that I don't want to use the xer project ID. Instead I want to have a specific user generate project ID that the user is importing their tasks into. Thus, you need to be careful as people can bundle multiple projects in one xer. A good check is to simply see how many projects are in the xer and if >1, just fail the import.

A few steps are seen here:

1. Import file and call the resulting text as data
2. Perform the above parsing function to your file (extracting the tasks)
3. Call your resulting string taskData
4. Call a function called insertActivities and pass in the taskData string of tasks

fs.readFile(filePath, 'utf8', (err, data) => {
    if (err) throw err;

    // Parse the XER file to extract TASK data
    const taskData = parseXER_tasks(data);

    // Check if the Project ID exists and delete existing data
    checkAndDeleteProjectData(projectId, () => {
      // Insert the new TASK data into the database
      updateProjectName(projectName, projectId);

      // Insert the new TASK data into the database
      insertActivities(taskData, projectId);

      fs.unlink(filePath, err => {
        if (err) throw err;
      });

      res.send('File processed and data inserted into the database');
    });
  });
});        

The specific function to insert the tasks into your database. Note that my database includes a dummy column to capture the first blank value. You could likely parse that out, but I found it easier to just leave it in.

function insertActivities(data, projectId) {
  if (data.length === 0) return;

  const columns = Object.keys(data[0]);
  const placeholders = columns.map(() => '?').join(',');
  const sql = `INSERT INTO tasks (dummy${columns.join(',')}, project_id) VALUES ?`;

  const values = data.map(row => {
    const rowValues = columns.map(col => row[col]);
    rowValues.push(projectId);
    return rowValues;
  });

  db.query(sql, [values], (err, result) => {
    if (err) throw err;
    console.log('Tasks inserted: ', result.affectedRows);
  });
}        

Again, this is only a first step of the process. I'm sure smarter people have more efficient processes; however, that is one of the key things I want to stimulate - Open functions and open discussion on these topics down to specific lines of code and functions. Value is in none of the above. The value people bring is in what we do with this. A hammer is useless unless yielded for a purpose. Lines of code, is just lines of code.