Extracting data from Excel To Illustrator javascript or vbscript

[duhwellhuh]

Hi all-

I was wondering if there was a way to extract data from Excel to be used in Illustrator. I know there is an option of variables and xml, and I don't want that. I've seen and tried out how to read illustrator and write to excel, and I get that.  What I would like to do is pretty much the opposite:

1.Pre-fill in an Excel file(.xls,.csv, doesn't matter) with data such as a filename in column 1 and (Replacement Text) in column 2 and close manually.

2. Run script(VBSCRIPT,Javascript, doesn't matter)

3.For each column in Excel file where cell in first column is not empty, open Illustrator Template with placeholder of "DWG" textframe and replace the frame titled "DWG" with Replacement text from Excel in Column2.

4, Save each to a PDF file and name file with text from Excel Column1(Filename)

In a nutshell, there will be a single illustrator template with a premade textFrame with a name of "DWG". Excel will contain two columns, one for the filename to be named and one for the relative text to replace with the placeholder in AI. I hoped I explained this well enough without causing too much confusion. Thanks in advance.

Filename	Replacement Text
test1.pdf	DWG01
test2.pdf	DWG02
test3.pdf	DWG03
test4.pdf	DWG04

[Muppet Mark]

You will find several topics in this forum where me and others have covered this… Search for CSV or TDT its very easy to do. The discussions will be ExtendScript… Just save your workbook to text file that script can read…

[duhwellhuh]

Thanks Mark! I was able to find this:

#target Illustrator

var csvFile = new File('~/Desktop/ScriptTest/Test.csv');

if (csvFile.exists) {
     var fileArray = readInCSV(csvFile);
     alert('CSV file has ' + fileArray[0].length + ' columns…');
     alert('CSV file has ' + fileArray.length + ' rows…');
}

function readInCSV(fileObj) {
     var fileArray = new Array();
     fileObj.open('r');
     while(!fileObj.eof) {
          var thisLine = fileObj.readln();
          var csvArray = thisLine.split(',');
          fileArray.push(csvArray);
     }
     fileObj.close();
     return fileArray;
}

This is very useful and everything works, but I'm still unsure about using "FOR" statements to make it do what I want or if that kind of statement is needed. Would you mind giving me a little insight into this? I'm just stuck on the syntax for doing something for each line in the row. I can figure out the saveOptions and changing the text, but getting it to do it for each line in the CSV file is the problem.

[Muppet Mark]

It can take a couple of looks at the data to get your head around but it's actually very straight forward once you have… Your workbook to *.csv should look like…

firstText,firstPDF \n or \r

anotherText,anotherPDF \n or \r

lastText,lastPDF \n or \r

The basic function you found is going to read line by line… readln(); and split it to an array split(','); and push each line into the main array giving you…

{ // fileArray

{'firstText','firstPDF'}, // fileArray.[0] is firstText  fileArray.[1] is firstPDF and so on…

{'anotherText','anotherPDF'}, // fileArray.[0] is anotherText

{'lastText','lastPDF'} // fileArray.[0] is lastText

} // fileArray

You traverse the main array fileArray with your for loop… and your excel columns are the items of the sub arrays [0], [1] and so on…

[duhwellhuh]

Are the \n and \r regex or something else?

[Muppet Mark · Accepted solution]

As text… \n is new line character and \r is return character. I can't remember which excel uses but they both equate to a line/paragraph… I very quickly threw together an example for you…

#target Illustrator
textToPDF();
function textToPDF() {
          if ( app.documents.length == 0 ) { return; }
          var doc, csvFile, i, fileArray, opts;
          csvFile = File( '~/Desktop/ScriptTest/Test.csv' );
          if ( !csvFile.exists ) { return; }
          fileArray = readInCSV( csvFile );
          doc = app.activeDocument;
          opts = new PDFSaveOptions();
          opts.pDFPreset = '[Press Quality]';
          // Here we loop the main array
          for ( i = 0; i < fileArray.length; i++ ) {
                    // Here we get the second item of sub array i
                    doc.textFrames.getByName( 'DWG' ).contents = fileArray.[1];
                    // Here we get the first item of sub array i
                    doc.saveAs( File( fileArray.[0] ), opts );
          };
};
function readInCSV( fileObj ) {
          var fileArray, thisLine, csvArray;
          fileArray =[];
          fileObj.open( 'r' );
          while( !fileObj.eof ) {
                    thisLine = fileObj.readln();
                    csvArray = thisLine.split( ',' );
                    fileArray.push( csvArray );
          };
          fileObj.close();
          return fileArray;
};

I haven't tested it but it should be close…?

[duhwellhuh]

Your example is starting to make sense to me within the context of what I asked.I have to tell ya, I'm a serious noob here and you and Carlos have taught me so much through your examples. I appreciate all of your effort and help!

I will check out this script and get back with you. Thanks again!

[Muppet Mark]

I just noticed you only supplied file names and NOT full paths so replace line…

doc.saveAs( File( fileArray.[0] ), opts );

with…

doc.saveAs( File( Folder.desktop + '/' + fileArray.[0] ), opts );

It should then dump the pdfs on ya desktop…

[duhwellhuh]

You're something else Mark, thanks!

[CarlosCanto]

You're something else Mark, thanks!

I agree, I've learned a lot from him

[CarlosCanto]

after you have the file access down with JS...and if you're interested...I can show you how to have Excel talk to Illustrator via VBA.

[duhwellhuh]

That would be great Carlos! I learned how to do the most basic of scripts and since then I've been hooked. I'll let you know when I get this down. Thanks!!

[duhwellhuh]

Whenever your ready Carlos!

[CarlosCanto]

Hi dwh, I started a new thread on how to get started with vba, try to do that, it is very simple, and if it is too easy, try to do your project and post back if you have questions.

[duhwellhuh]

A new thread was a good idea. I'll keep my eyes open for it.

[duhwellhuh]

Mark- Everything worked like it should, however I was showing a syntax error.  I then went back through some of your other posts and was able to debug it.

In these lines you had a period between and [1]. I removed it and it worked just like it should.  Whether or not,it was necessary I don't know. It doesn't matter, you still get max credit for this one. Thank you so much!!

// Here we get the second item of sub array i
              doc.textFrames.getByName( 'Frame1' ).contents = fileArray[1];
 
                    // Here we get the first item of sub array i
            doc.saveAs( File( Folder.desktop + '/' + fileArray[0] ), opts );

[Muppet Mark]

Well spotted & sorted… Of cause Im going to say it was intentional to just make sure you were paying attention… Really just a typo that I didn't spot cause I didn't run the code against any doc… By Monday you won't care about dealing with workbooks that are in the 1000's as it makes no difference… A welcome confidence boost when your new to scripting none the less…

[duhwellhuh]

I'm not going to hold it against ya! You've been a great help. I've been working today creating several scripts and I'm finally starting to grasp it. I modified this one today to ignore the header by changing the 'i = 0' variable to "i=1' in the for statement. This forum, well you and Carlos at least, are a lot more helpful than any book I've ever read, especially since the resources on Extendscript are so limited. Perhaps you should write one!   As I keep debugging, I'm starting to learn the syntax a little better and it's becoming easier to spot the errors. It's kind of crazy that I spent a week trying to get a basic function working until I found this forum. Thanks again! I'm sure I'll have other questions later.