Coloring map from data sheet like excel

[jakobt95792637]

Hello

Is there a way to color a map from data sheet like excel? I'm thinking of splitting a country into regions and bind the paths to a variable that can be change by importing a data sheet.

Has anyone here got experience with this?

[Disposition_Dev]

Do you have any example files you can share? The data can be faked if it's sensitive, we just need a look at format.

I'll give you a quick rundown so you can see what's involved.

the excel sheet has to be saved as a CSV, unless you know how to parse all the gibberish out of an excel file.

you'll have a sort of "template" file that you use. that will be your map that is split into regions with each region named something unique or at least put onto it's own (uniquiely named) layer so it's accessible.

using javascript you open and read the CSV file and save the contents to a variable.

split the CSV by \n and loop the resulting arrays to pull out the individual data and spit it out on each region of the map accordingly.

Do you have any scripting experience?

[Silly-V]

Yea. Show us what the CSV and document looks like.

[Loic.Aigon]

EasyCatalog is an alternative also. You can apply object styles based on a dynamic value. Always up to date. No need of scripting nor converting excel to csv.

Loic

[Silly-V]

That looks like it's a $1300 plugin for Indesign.

[Disposition_Dev]

ouch. i'll do it for $1200.

[Loic.Aigon]

From my experience, the ROI on this product is worth ten times the initial investment

[Disposition_Dev]

I don't disagree at all. Unfortunately, in my experience with clients, ROI is an afterthought to initial investment.

Though i think it's important to note that ROI is heavily dependent upon the future needs of this functionality. It is roughly proportional to the frequency with which the user needs the same functionality in the future. If you only need it for a one off project that you don't expect to repeat later, then the ROI could easily be negative.

I don't think Silly was trying to shame you for suggesting that as a solution at all. Just pointing out a very important piece of information necessary to making the decision to use that software.

[Loic.Aigon]

Don't worry. I didn't take any offense and I can't agree more your points. I was mostly pointing an alternative solution

[jakobt95792637]

Here are the for files for download: a csv fil and an illustrator CS6.

They are part of a bigger map. I have added each region to a seperate layer.

Hopefully some of you guys will give it a go at creating a script.

regions_map - Google Drive

[Silly-V]

Your CSV file appears to be semi-colon delimited, and the characters apparently do not make it across into my MS Excel.

Those issues aside, how do the corresponding numbers relate to colors within an Illustrator document?

[jakobt95792637]

Hmm - I think Mac way of handling the data is destroying it. So I have included a couple of extra data sheets. One specific for MS and one for Mac. Hope one of the is usable: regions_map - Google Drive

I haven't specified any particular color swatch for the different values. I have just giving them a number and the idea is that the number has a corresponding swatch by the same name. If that is overly complicated I'll be glad to submit some color values for each name.

[Silly-V · Accepted solution]

Okay, try this script.

It will only work on the kind of CSV files you supply- which are semi-colon delimited.

#target illustrator
function test(){
  function readSemicolonCSV(filePath){
    var f = File(filePath);
    if(!f.exists){
      alert(f + " is not found.");
      return false;
    }
    var str = "";
    f.open("r");
    str = f.read();
    f.close();
    return str;
  }
  function getCells(str){
    var rows = str.split(/[\n\r]/g);
    for(var i=0; i<rows.length; i++){
      rows = rows.split(/;/g);
    };
    return rows;
  }
  function applyColorSettingsRow(regionName, colorName){
    var doc = app.activeDocument, thisRegionShape, thisColor;
    try{
      thisRegionShape = doc.layers.getByName(regionName).pathItems[0];
      thisColor = doc.swatches.getByName(colorName);
      thisRegionShape.fillColor = thisColor.color;
    } catch(e) {
      $.writeln("Region: " + regionName + "\tColor: " + colorName + "\r" + e + "\n\n");
    }
  }
  if(app.documents.length == 0){
    alert("No open documents detected.");
    return;
  }
  var csvFile = File.openDialog("Open CSV File", "*.csv");
  if(!csvFile){
    alert("No file chosen");
    return;
  }
  var data = getCells(readSemicolonCSV(csvFile));
  var doc = app.activeDocument;
  for(var i=1; i<data.length; i++){
    applyColorSettingsRow(data[0], data[1]);
  };
};
test();

[jakobt95792637]

Hi Silly-V

Just wanted you now I appreciate your effort and is looking into the script.

I did a initially test run that did alter some of the color swatches used on specific paths but not all of them. I renaming the paths not using danish letters like Æ Ø and Å and when I have the time I going to give it another go.

[ktewes]

Hei Silly, this is really great. Very, very usefull! But I have one more question: The script works for one sublayer at the moment. What if - e.g. - there is a region "italy" and this region has more than one shape as sublayers (islands...). Tried to get that to work, but unfortunately I failed...

[Silly-V]

That would require a little bit different approach, with some added changes. Do you have a consistent nesting structure for your layers? Maybe there could be a way to use special syntax and 'sneak' some more information into our Illustrator script to make it recognize a layers structure of sorts.. Please provide some screenshots to help understand your art structure and maybe it's not a long-shot.

[ktewes]

Hei Silly-V,

I found a solution myself. Not that tricky 🙂 - just release the compound path (e.g. US: America and Alaska) and loop over the region to get the number of paths of each layer:

var anzPfade = doc.layers.getByName(regionName).pathItems.length;

try{ 

        for (var i=0; i<=anzPfade;i++){

                thisRegionShape = doc.layers.getByName(regionName).pathItems;

                thisColor = doc.swatches.getByName(colorName); 

                thisRegionShape.fillColor = thisColor.color; 

      }

    } catch(e) { 

      $.writeln("Region: " + regionName + "\tColor: " + colorName + "\r" + e + "\n\n"); 

    } 

  }

I found one little mistake in your script: The loop that calls "applyColorSettingsRow" should start with "i=0" - otherwise the first item in the CSV is omitted. The correct call would look like:

for(var i=0; i<data.length; i++){

    applyColorSettingsRow(data[0], data[1]); 

  };

You can find the map, the CSV and the complete Script here: MagentaCLOUD (just ignore the layer "map 1" - only the eight layers above are relavant.

Thanks again for sharing your solution!! 😄

[Loic.Aigon]

ktewes  a écrit

I found one little mistake in your script: The loop that calls "applyColorSettingsRow" should start with "i=0" - otherwise the first item in the CSV is omitted.

Aren't the first row the headers ?

[ktewes]

Yes, you´re right. If you use headers starting with "i=1" is the correct way. In my example CSV I´m working without header. Therefore - no mistake in the code, just a question of using a header or not.

Here once again my version of the code (using header). There´s one small change in the code, that fixes a mistake in the "coloring loop", that caused an error message:

#target illustrator

function test(){

  function readSemicolonCSV(filePath){

    var f = File(filePath);

    if(!f.exists){

      alert(f + " is not found.");

      return false;

    }

    var str = "";

    f.open("r");

    str = f.read();

    f.close();

    return str;

  }

  function getCells(str){

    var rows = str.split(/[\n\r]/g);

    for(var i=0; i<rows.length; i++){

      rows = rows.split(/;/g);

    };

    return rows;

  }

  function applyColorSettingsRow(regionName, colorName){

    var doc = app.activeDocument, thisRegionShape, thisColor;

    var anzPfade = doc.layers.getByName(regionName).pathItems.length;

    try{

    for (var i=0; i<anzPfade;i++){

      thisRegionShape = doc.layers.getByName(regionName).pathItems;

      thisColor = doc.swatches.getByName(colorName);

      thisRegionShape.fillColor = thisColor.color;

      }

    } catch(e) {

      $.writeln("Region: " + regionName + "\tColor: " + colorName + "\r" + e + "\n\n");

    }

  }

  if(app.documents.length == 0){

    alert("No open documents detected.");

    return;

  }

  var csvFile = File.openDialog("Open CSV File", "*.csv");

  if(!csvFile){

    alert("No file chosen");

    return;

  }

  var data = getCells(readSemicolonCSV(csvFile));

  var doc = app.activeDocument;

  for(var i=1; i<data.length; i++){

    applyColorSettingsRow(data[0], data[1]);

  };

};

test();

[Loic.Aigon]

I understand better the previous comments. I was certain to be in the indesign scripting forum and didn't notice it was the illustrator one I still think it's a great tool but indeed dedicated to InDesign.

[jakobt95792637]

By golly it seems to be working!

You're are genius Silly-V.

Check out this vid.

ColorMap.mov - Google Drive

[Disposition_Dev]

awesome! glad it's working out for you. thanks, Silly!

[mdupras]

I attempted to use the solution posted by Silly-V but keep running into an error.

The particulars: I have a map of NY state, with each county an object on a separate sublayer of a layer group; the layer group is a date. I want to color the counties by the number of cases of coronavirus on that date. Eventually there will be about 40 date layer groups, and they'll be imported into Premiere to create a timeline video of the spread in NY.

My CSV file has the name of the county (regionName) and name of a color (colorName) which aligns to a color name in my palette, and the file is delimited by semicolons per the script instructions. I keep getting this error when I run the script:

Error 24: rows.split is not a function.
Line: 18
->  rows = rows.split(/;/g);

Perhaps the issue is because I'm using sublayers? Any advice is appreciated!

[eShirtLabs]

Same error, but on Line: 35

Error 24: row.split is not a function.

Line: 35

->    rows=rows.split(/;/g);