read and write excel file

Post Reply
smckinney
Newbie
Posts: 9
Joined: Wed Oct 28, 2020 3:36 pm

read and write excel file

Post by smckinney »

I have an excel file that I need to read from to write to a new excel file which would be output in switch. The script I have works and does exactly what I need it to when I run it in Visual Studio Code. When I bring it into Switch Scripter I keep getting this error:

Code: Select all

Cannot find module '/Users/switchserver/Library/Application Support/Enfocus/SwitchProcessorService/cache/e70949d77e9b490bdf68243cd0705bfe9cfa42ead1d769669b7ce7fa7b7a80ad/1629987785/node_modules/xlsx' Require stack: - /Applications/Enfocus/Enfocus Switch/ScriptExecutor/NodeScriptExecutor.js

Code: Select all

var xlsx = require("xlsx");

// read excel file
var wb = xlsx.readFile("EFI_Salesperson Commissions Report.xlsx");

// read sheet in excel file
var ws = wb.Sheets["EFI_Salesperson Commissions Rep"];

// convert data to json array
var data = xlsx.utils.sheet_to_json(ws);

async function jobArrived( s, flowElement, job ){
    //get data from job
    let jobName = await job.getName(includeExtension=false);
}

//rearranges columns in excel to the end of the file 
// 2nd record is simply to rename to the original column name
//deletes columns no longer necessary in the file

var newData = data.map(function(record){
    record.nSalesRep = record.Query_Grouping;
    delete record.Query_Grouping
    record.SalesRep = record.nSalesRep;
    delete record.nSalesRep;

    record.nAccount = record["Query_Account Name"];
    delete record["Query_Account Name"];
    record.Account = record.nAccount;
    delete record.nAccount;

    record.nQuery_InvoiceNumber = record.Query_InvoiceNumber;
    delete record.Query_InvoiceNumber;
    record.InvoiceNumber = record.nQuery_InvoiceNumber;
    delete record.nQuery_InvoiceNumber;

    record.nSubtotal = record.Query_Subtotal;
    delete record.Query_Subtotal;
    record.Subtotal = record.nSubtotal;
    delete record.nSubtotal;

    record.nShipping = record.Query_Cat19;
    delete record.Query_Cat19;
    record.Shipping = record.nShipping;
    delete record.nShipping;

    record.nPostage = record.Query_Cat17;
    delete record.Query_Cat17;
    record.Postage = record.nPostage;
    delete record.nPostage;

    record.nTotalInvoice = record.Subtotal - record.Shipping - record.Postage;
    delete record.InvoiceTotalRep;
    record.TotalInvoice = record.nTotalInvoice;
    delete record.nTotalInvoice;
    
// deletes unused columns 
    delete record.Title;
    delete record.DateRange;
    delete record.PrintDate;
    delete record.Query_CatName17;
    delete record.Query_PickupDate;
    delete record.Query_Cat18;
    delete record.SubtotalRep;
    delete record.Cat18Rep;
    delete record.Cat19Rep;
    delete record.Cat17Rep;
    delete record["Subtotal-postage"];


        return record; 

});
//writes data to a new sheet in a new workbook

var newWB = xlsx.utils.book_new();
var newWS = xlsx.utils.json_to_sheet(newData);
xlsx.utils.book_append_sheet(newWB,newWS,"Revised Data");

xlsx.writeFile(newWB,"new data file.xlsx");
I realize I'm probably missing a output switch tag in here somewhere. Is the thing I'm even trying to do possible. Any and all help is greatly appreciated.
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: read and write excel file

Post by freddyp »

A first obvious mistake is that everything your script does (except from the importing/requiring of packages) must be embedded in jobArrived (or timerFired). That does not mean you can only have one function. You can have as many as you like, but jobArrived is your entry point. That is where your script starts and nothing should happen before it. That is also where the scripts end and nothing should happen after it. In between you can jump all over the place, although for your example having everything in jobArrived will do nicely.

Your script also do not follow the logic of taking the input job that Switch provides to the script (you use a fixed path) and outputting it to the next folder in the flow (again you use a fixed path). To do the latter there are specific functions and techniques you have to use. Have a look at our webinars on starting with scripting: https://www.enfocus.com/en/learn/switch#scripting and check out the sample scripts on Github: https://github.com/EnfocusSW
Post Reply