Read and write data to Excel using javascript..!

[natrev]

Hi Everyone!

I have one excel document named as Temp.xls which is contains filename and file path.

Now I need check that Excel file name with active document name, If match found we need to update the current path of active document to existing file path location.

Please suggest me if any other solution much appreciated.

yajiv

My code is :

UpdateExcel();
function UpdateExcel(){        
    var docRef=app.activeDocument;
    var docPath=docRef.path;
    var dname=docRef.name;

    var f = docPath+"/Temp.xls";
    var datafile = new File(f);
    if (datafile.exists) { 
    datafile.open('a') ;
    while (datafile.eof){
        strLineIn = datafile.readln();
        colorArray = strLineIn.split(",");
        if (colorArray[1]!=""){
            if(colorArray[1].match(dname)!=null){
                colorArray[2]=docPath;
            }
        }
    }
}
}

[Tom Ruark]

I would export (Save As) your xls file as CSV (Comma Separated Values/Comma Delemited). Temp.csv

You will need to make sure your data cells do not contain any commas and any special characters are handled correctly.

[natrev]

Hi Tom,

Thanks for your valuable reply.

Already I have use that CSV format, unfortunate I can read data but can't to update the data to CSV.

Please advice, which Mode is  favorable for simultaneously read and write data.

- yajiv

Code:

UpdateExcel();  
function UpdateExcel(){          
    var docRef=app.activeDocument;  
    var docPath=docRef.path;  
    var dname=docRef.name;  

    var f = docPath+"/Temp.csv";  
    var datafile = new File(f);  
    if (datafile.exists) {   
        datafile.open('r');  
        while (!datafile.eof){  
            strLineIn = datafile.readln();  
            colorArray = strLineIn.split(",");  
            if (colorArray[6]!=""){  
                if(colorArray[6].match(dname)!=null){  
                    colorArray[7].value=docPath;  
                    return;
                }  
            }  
        }  
    }  
}  

[defaultkrt0iuh8hlot]

Hey Tom,

What if the contents of each cell in a column are an array?

Is there a way that the code can be written to handle this, or the excel sheet can be formatted to handle it prior to being exported as a .csv?

[Lumigraphics]

You can also write to tab-delimited text which might work better than CSV.

[defaultkrt0iuh8hlot]

I know this is a HUGE ask, but can you take a look at a post I made that outlines my specific issue.

https://community.adobe.com/t5/photoshop-ecosystem-discussions/how-to-read-xlsx-data-and-push-to-arr...

I'm stumped and at this point just looking for a ".CSV file reading resource" or something in JS and all I can find are sporadic forum posts and blogs that don't seem comprehensive... is there a single place where information about this is consolidated?

[Lumigraphics]

What exactly are you trying to match? The filename? I'm not sure why you are pushing your constants into an array, just construct the array, loop through it and compare filename to your spreadsheet item, and skip processing the ones that don't match.

You could also do some work in Excel to make this easier. Don't have four items in column B, split those to B, C, D, and E.

[defaultkrt0iuh8hlot]

My example in that post is a dummy. There will be 800 possible urls when I'm done. also, column B isnt going to be four items every time, it could be any combination of undetermined templates from the 800 possible. Each time a .PNG is processed and exported through the actions, it makes 4 .JPEG images. I don't want to make 3,200 .JPEGs for each .PNG... I just want to make the ones I need at a given time.

I hope that makes sense.

[defaultkrt0iuh8hlot]

I can't just match picForSim to one of the possible .PSD templates, it needs to go on an undetermined selection of them... I will make that selection on an excel sheet and save it (in whatever format is easiest)... I just don't know how to get the information from the excel sheet to excelArray. If I manually write in the template variables (Temp1, Temp2 for example) it works fine... so I know the basis for this works... I just need to get this one thing working (replace the empty array with an array listed in some kind of file) but it HAS to be the array associated with the picForSim (the .PNG in the zeroth index of the photoshop application) or else I wont apply the correct design to the correct selection of templates.

[Lumigraphics]

Well, not really TBH. I'd do the work in Excel as much as possible to refine your listing, then export as a tab-delimited file. You can read one line at a time and put it into an array, then walk the array to get your data.

[defaultkrt0iuh8hlot]

Can you tell me where to find documentation on this... I need to study up on it. I'm sure you can tell I'm in over my head.

[Lumigraphics]

Well there are lots of tutorials on Youtube, for both Excel and Photoshop...

[defaultkrt0iuh8hlot]

ok. thanks.

[Chuck Uebele]

Change the 'r' in line 10 to a 'w' so that you can write to the file. Or if that doesn't work. read the file to a variable, make all your changes to the data in that variable, then write the entire variable back to the file.

//read the file
if (textFile.exists) {
textFile.encoding = "UTF8";
textFile.lineFeed = "unix";
textFile.open("r", "TEXT", "????");
var str = textFile.read();
textFile.close();


//code here to alter the array with your correctons.

//write the file
textFile.encoding = "UTF8";
textFile.open("w", "TEXT", "????");
textFile.write("\uFEFF");
textFile.lineFeed = "unix";
textFile.write(str);
textFile.close();
}