Read and write data to Excel using javascript..!

Engaged ,
Feb 23, 2016 Feb 23, 2016

Copy link to clipboard

Copied

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;

            }

        }

    }

}

}

TOPICS
Actions and scripting

Views

5.5K

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Employee ,
Feb 23, 2016 Feb 23, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Feb 23, 2016 Feb 23, 2016

Copy link to clipboard

Copied

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;

                } 

            } 

        } 

    } 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 01, 2022 Aug 01, 2022

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 02, 2022 Aug 02, 2022

Copy link to clipboard

Copied

LATEST

ok. thanks.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 24, 2016 Feb 24, 2016

Copy link to clipboard

Copied

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();

}

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines