How to read .xlsx data and push to array in .js script for photoshop

Question

How to read .xlsx data and push to array in .js script for photoshop defaultkrt0iuh8hlot

I am building some automation for work and have reached my limit of comfortable knowlege in scripting.

I have an .xlsx file where:

column A1 thru A20 represents a design we have created.

column B1 thru B20 contains lists of color background templates for each design in column A:

My Excel Document

I also have a folder with .png files whos file names exactly match the names given in column A.

My script uses photoshop batching to open one file (eg: XY-111.png) at a time in that folder,

place that .png on all of the templates (eg: Temp1, Temp3, Temp5) and save each as a .jpg (using a different script).

Screenshot of VS Code

The above code WORKS, but instead of

excelArray.push(Temp1, Temp2, Temp3)

I want to push the data in column B to the array ONLY if app.documents[0] matches column A.

//////////////////////////////////////////////////////////////////////////////////////////////////

The code:

#target photoshop

var Temp1 = "/Temp1.psd"
var Temp2 = "/Temp2.psd"
var Temp3 = "/Temp3.psd"
var Temp4 = "/Temp4.psd"
var Temp5 = "/Temp5.psd"
var Temp6 = "/Temp6.psd"
var Temp7 = "/Temp7.psd"
var Temp8 = "/Temp8.psd"
var Temp9 = "/Temp9.psd"
var Temp10 = "/Temp10.psd"

var picForSim = app.documents[0];

var excelArray = [];

excelArray.push(Temp1, Temp2, Temp3);

for (var i = 0; i < excelArray.length; i++){
if (picForSim.name.charAt(0) === "W"){
//alert(excelArray[i])
app.open(new File(excelArray[i]));
app.doAction(("WGARMENTS-ArtPlace"), ("ProductActions.ATN"))
} else {
//alert(excelArray[i])
app.open(new File(excelArray[i]));
app.doAction(("GARMENTS-ArtPlace"), ("ProductActions.ATN"))
}
}

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I took a node.js tutorial and I feel as though I understand the concept of getting data from excel, but I'm having some trouble connecting the dots and getting the info into my script.

any help would be appreciated!

Answer 1

If your.xlxs file is exported as a .csv it'll look like this:

WXY-111,"Temp1, Temp2, Temp3, Temp4"
WXY-222,"Temp2, Temp3, Temp4, Temp5"
WXY-333,"Temp3, Temp4, Temp5, Temp6"
YX-111,"Temp4, Temp5, Temp6, Temp7"
XY-222,"Temp5, Temp6, Temp7, Temp8"
XY-333,"Temp6, Temp7, Temp8, Temp9"

Your can read the text file into Photoshop like this

// Reference to the csv file
var csvFile = "D:\\temp\\mycsv.csv"; // change this!

// automatically read in first file
var theFile = new File(csvFile);

//read in file 
var csvArr = []; // array to store CSV
var l = 0;
var csvFile = new File(theFile);
csvFile.open('r');
while(!csvFile.eof)
{
   var line = csvFile.readln();
   if (line != null && line.length >0)
   {
      csvArr[l++] = line;
   }
   
}
csvFile.close();

// create an array so we can use the data
var textArr = [];
var msg = "";

for (var i = 0; i< csvArr.length; i++)
{
   var line = csvArr[i];
   var aStr = line.slice(0, line.indexOf(","));
   var bStr = line.slice(line.indexOf(",") + 1);

   // replace quotes with nothing
   bStr = bStr.replace(/\"/gm, "");

   // split column b into seperate elements
   var sp = bStr.split(",");
   textArr.push([aStr, sp[0], sp[1], sp[2], sp[3]]);
}


// let's loop over the array and see what it says...
for (var i = 0; i< textArr.length; i++)
{
   for (var j = 0; j< textArr[i].length; j++)
   {
      msg += textArr[i][j] + " , ";
   }
   
   msg += "\n";
}

alert(msg);

// you can access the info like this: 
var data = textArr[4][1]; 
alert(data); // temp 5