• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

What's the least inelegant way to ingest CSV w/JS?

Community Expert ,
May 17, 2023 May 17, 2023

Copy link to clipboard

Copied

I'm working on a pair of scripts that will allow me to export form field names and descriptions from InDesign, and re-import fresh descriptions after I get them back from the translator. I have already (some years ago) written a similar script for Acrobat, where I've written a tool* that extracts all of the form names and tooltips and stores them in a CSV. Then I can have that CSV translated, which comes back to me as an Excel file. Then I... uh...

 

... here's the embarassing part that I don't want to share with you folks who write Real Code...

 

then I open the CSV in Excel and run a VBA that takes every pair of form-names-and-tooltips in the CSV and makes each one into its own line of ExtendScript code. So a pair like

 

 

"name","Type name here:"

 

 

would be turned into 

 

 

this.getField("name").userName=["Type name here"];

 

 

 

Then I'd copy/paste the whole dang Excel file into the JS console in Acrobat and run it all at once from the console. I liked this because if there was a line that failed, the JS console would tell me which line, and that would correspond to that line in Excel. Very easy to figure out what has gone wrong, that way (almost always there's a character in the description/tooltip that needs to be escaped for that line to run properly). 

 

However, as you can tell by the fact that I'm ashamed to post about it, I can tell that this workflow is idiosyncratic and Not the Done Thing. And now that I'm recreating this tool for use in InDesign, I'd like to it in some way that won't induce people to point and laugh at my code. So I'm here to ask you folks how people who write Real Code go about it. Creating the CSV from InDesign was fairly easy:

 

 

 

var myDoc = app.activeDocument;
var csvName = myDoc.name + ".csv";
var myFields = myDoc.formFields;
var csvDestination = Folder.selectDialog("Choose destination for .csv");
var csvFile = new File(csvDestination + "/" + csvName);

csvFile.encoding = "UTF-8";
csvFile.open();
for (var i = 0; i < myFields.length; i++) {
    csvFile.write(myFields[i].name + "," + myFields[i].description + "\n");
}
csvFile.close();

 

 

But I really am at a loss as to how to get the CSV back in after it's been with the translators. You read the CSV in with JS and... Do you parse the CSV content into variables with regex? Do you use .split to turn the list of pairs into an array, then loop through the myDoc.formFields[i] and write in the new descriptions? Should my script trust that the order of field descriptions in the CSV is unchanged from when I extracted them, or should I hit each .formField, check its .name, then match it up with the corresponding .description? 

 

The young people in my circle are telling me that I should use thus-and-so JS library to convert the CSV to JSON first, which honestly feels like an unnecessary step, but maybe there's something here about handling JSON in ExtendScript that I don't know? 

 

TOPICS
How to , Scripting

Views

548

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

correct answers 2 Correct answers

Community Expert , May 18, 2023 May 18, 2023

which comes back to me as an Excel file.

 

Hi @Joel Cherney , Does the returned, edited Excel file have to be a .csv—could it be saved as an .xls or .xlsx? Splitting the .csv string can be tricky—how do you handle a case where the delimiter character might be in the description text you are trying to get?

 

Getting the content of an .xls as an array, by temporarily placing it as a table came up in this thread, which @Peter Kahrel helped with:

 

https://community.adobe.com/t5/indesign-discussions/how-to-use-xlsx-extendscript-js/m-p/13782669#M526306

...

Votes

Translate

Translate
Guide , May 19, 2023 May 19, 2023

Hi Joel,

 

Just posted a basic CSV parser for ExtendScript:

https://github.com/indiscripts/IdGoodies/blob/master/snip/FileParseCSV.jsx

 

Hope that helps.

 

Best,

Marc

Votes

Translate

Translate
Community Expert ,
May 17, 2023 May 17, 2023

Copy link to clipboard

Copied

If I understand it correctly, your CSV file has two columns. The translator adds a third. If that's correct, then you simply open the CSV file in your InDesign JS script.

 

Do you use .split to turn the list of pairs into an array

 

Yes, probably.

 

Should my script trust that the order of field descriptions in the CSV is unchanged from when I extracted them,

 

I don't think I understand this. The field descriptions are next to the form names, aren't they? Or do the translators get a single-column file with descriptions which they translate, and return a single-column file?

 

> The young people in my circle are telling me that I should use thus-and-so JS library to convert the CSV to JSON first, which honestly feels like an unnecessary step

 

Agreed, completely unnecessary. Your CSV file is easy to parse. (But then I'm not the youngest either. . .)

 

> but maybe there's something here about handling JSON in ExtendScript that I don't know?

 

ExtendScript doesn't have JSON handlers, but JSON objects are really standard JavaScript objects (well, it would, seeing that JSON stands for JavaScript Object Notation) and can be processed in ExtendScript fairly easily. But, again, in your case I would use the CSV file, which is after all a simple text file that can be parsed quite easily.

 

Peter

 

 

 

Votes

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 ,
May 17, 2023 May 17, 2023

Copy link to clipboard

Copied

 

> The young people in my circle are telling me that I should use thus-and-so JS library to convert the CSV to JSON first, which honestly feels like an unnecessary step

 

Agreed, completely unnecessary. Your CSV file is easy to parse. (But then I'm not the youngest either. . .)

 

Thanks for this; I think that the contemporary JS dev solution to pretty much anything is "throw another library at it." 

 

I've found a few attempts at CSV processing for InDesign (one posted here, another inside basil.js) and they do seem to rely on slicing arrays or popping elements off of an array, so I have a few examples of how other people handled it, and your response has closed off a few approaches that turn out to be blind alleys. 

Votes

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 ,
May 18, 2023 May 18, 2023

Copy link to clipboard

Copied

which comes back to me as an Excel file.

 

Hi @Joel Cherney , Does the returned, edited Excel file have to be a .csv—could it be saved as an .xls or .xlsx? Splitting the .csv string can be tricky—how do you handle a case where the delimiter character might be in the description text you are trying to get?

 

Getting the content of an .xls as an array, by temporarily placing it as a table came up in this thread, which @Peter Kahrel helped with:

 

https://community.adobe.com/t5/indesign-discussions/how-to-use-xlsx-extendscript-js/m-p/13782669#M52...

 

Using Peter‘s shortcut a function could be:

 

 

 

 

var xlsf = File.openDialog("Please Choose an Excel file");

alert("Contents of row 2 cell 3: \r" + getExcelArray(xlsf)[1][2]);

/**
* Converts an Excel table into an array 
* @ param the Excel file path 
* @ return a nested array for the table 
*/

function getExcelArray(xf){
    var doc = app.activeDocument;	
    var temp = doc.pages[0].textFrames.add ({geometricBounds: ["0pts","0pts","1000pts","1000pts"], textFramePreferences: {ignoreWrap: true,}});
    try {
        temp.place(xf);
        var xta = temp.parentStory.tables[0].rows.everyItem().contents; 
    }catch(e) {
        temp.remove()
        alert(e)
    }  
    temp.remove();
    return xta;
}

 

 

 

 

 

Run:

Screen Shot 21.png

Votes

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 ,
May 18, 2023 May 18, 2023

Copy link to clipboard

Copied

> Splitting the .csv string can be tricky—how do you handle a case where the delimiter character might be in the description text you are trying to get?

 

True. Cells that contain quotation marks and/or the character used as the separator are wrapped in quotation marks, which indeed can get messy sometimes. I thought that Excel could export to a tab-delimited text file, which avoids that problem.

Votes

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 ,
May 18, 2023 May 18, 2023

Copy link to clipboard

Copied

I haven’t been able to come up with safe way to do it. I don’t have Excel and the Mac’s Numbers app lets me Export a .csv, but it’s comma delimited and I don’t see a way to change that. Not sure there is a way to know how the .csv is fomatted, but if it is tab delimited wouldn’t a tab in the description be a problem?

Votes

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 ,
May 18, 2023 May 18, 2023

Copy link to clipboard

Copied

I don't think that in Excel you can have tabs in the description.

But anyway, the alternative you suggested seems the safest workaround.

Votes

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
Guide ,
May 19, 2023 May 19, 2023

Copy link to clipboard

Copied

LATEST

Hi Joel,

 

Just posted a basic CSV parser for ExtendScript:

https://github.com/indiscripts/IdGoodies/blob/master/snip/FileParseCSV.jsx

 

Hope that helps.

 

Best,

Marc

Votes

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