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;
}
}
}
}
}
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.
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;
}
}
}
}
}
Copy link to clipboard
Copied
Copy link to clipboard
Copied
You can also write to tab-delimited text which might work better than CSV.
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.
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?
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.
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.
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.
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.
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.
Copy link to clipboard
Copied
Well there are lots of tutorials on Youtube, for both Excel and Photoshop...
Copy link to clipboard
Copied
ok. thanks.
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();
}
Copy link to clipboard
Copied
You can also check this code
<script type="text/javascript">
function UploadProcess() {
//Reference the FileUpload element.
var fileUpload = document.getElementById("fileUpload");
//Validate whether File is valid Excel file.
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
if (regex.test(fileUpload.value.toLowerCase())) {
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
//For Browsers other than IE.
if (reader.readAsBinaryString) {
reader.onload = function (e) {
GetTableFromExcel(e.target.result);
};
reader.readAsBinaryString(fileUpload.files[0]);
} else {
//For IE Browser.
reader.onload = function (e) {
var data = "";
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
GetTableFromExcel(data);
};
reader.readAsArrayBuffer(fileUpload.files[0]);
}
} else {
alert("This browser does not support HTML5.");
}
} else {
alert("Please upload a valid Excel file.");
}
};
function GetTableFromExcel(data) {
//Read the Excel File data in binary
var cfb = XLS.CFB.read(data, {type: 'binary'});
var workbook = XLS.parse_xlscfb(cfb);
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLS.utils.sheet_to_row_object_array(workbook.Sheets[Sheet]);
//Create a HTML Table element.
var myTable = document.createElement("table");
myTable.border = "1";
//Add the header row.
var row = myTable.insertRow(-1);
//Add the header cells.
var headerCell = document.createElement("TH");
headerCell.innerHTML = "Id";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Name";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Country";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Age";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Date";
row.appendChild(headerCell);
headerCell = document.createElement("TH");
headerCell.innerHTML = "Gender";
row.appendChild(headerCell);
//Add the data rows from Excel file.
for (var i = 0; i < excelRows.length; i++) {
//Add the data row.
var row = myTable.insertRow(-1);
//Add the data cells.
var cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Id;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Name;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Country;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Age;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Date;
cell = row.insertCell(-1);
cell.innerHTML = excelRows[i].Gender;
}
var ExcelTable = document.getElementById("ExcelTable");
ExcelTable.innerHTML = "";
ExcelTable.appendChild(myTable);
};
</script>
Reference: https://qawithexperts.com/article/javascript/read-excel-file-using-javascript-xlsx-or-xls/2393