Output Excel format from Datamapper JSON

I’ve been looking for a way to take data from a PDF or line printer file to generate an Excel file. I have a Datamapper created to output JSON. I also have the following javascript that I can run in NodeJS using External Program.

const XLSX = require('xlsx');

// My JSON data
const data = [
    { Name: "John", Age: 30, City: "New York" },
    { Name: "Jane", Age: 40, City: "Chicago" }

// Create a new workbook
const wb = XLSX.utils.book_new();

// Convert JSON data to worksheet
const ws = XLSX.utils.json_to_sheet(data);

// Append the worksheet to the workbook
XLSX.utils.book_append_sheet(wb, ws, "Commission");

// Write workbook to XLSX
XLSX.writeFile(wb, 'Commission.xlsx'); 

In the script above, it uses a static array for the JSON but it does successfully generate an Excel file from that array within a Workflow. Where I’m getting stuck is how do I pass the JSON data coming from the previous Datamapper step to the script’s “data” variable? I understand that it will come in as a JSON string and I would use JSON.parse just don’t know how to ingest the Datamapper output into the External Program.

You could simply save the JSON file that is returned by the DataMapper to a known location (e.g. C:\Data\DM.json) and then in your NodeJS program, load and parse that file.

You could even make the file name dynamic and pass it as an argument to your NodeJS program via its command line (NodeJS can parse the arguments it receives on the command line with the process.argv method)