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

Coloring map from data sheet like excel

Community Beginner ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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?

TOPICS
Scripting

Views

2.6K

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 1 Correct answer

Valorous Hero , Nov 01, 2016 Nov 01, 2016

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

   

...

Votes

Translate

Translate
Adobe
Community Expert ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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?

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
Valorous Hero ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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

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
People's Champ ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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

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
Valorous Hero ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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

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 ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

ouch. i'll do it for $1200.

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
People's Champ ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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

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 ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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.

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
People's Champ ,
Oct 25, 2016 Oct 25, 2016

Copy link to clipboard

Copied

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

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 Beginner ,
Oct 26, 2016 Oct 26, 2016

Copy link to clipboard

Copied

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

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
Valorous Hero ,
Oct 26, 2016 Oct 26, 2016

Copy link to clipboard

Copied

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?

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 Beginner ,
Oct 31, 2016 Oct 31, 2016

Copy link to clipboard

Copied

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.

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
Valorous Hero ,
Nov 01, 2016 Nov 01, 2016

Copy link to clipboard

Copied

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

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 Beginner ,
Nov 04, 2016 Nov 04, 2016

Copy link to clipboard

Copied

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.

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 Beginner ,
Dec 17, 2018 Dec 17, 2018

Copy link to clipboard

Copied

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...

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
Valorous Hero ,
Jan 05, 2019 Jan 05, 2019

Copy link to clipboard

Copied

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.

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 Beginner ,
Jan 10, 2019 Jan 10, 2019

Copy link to clipboard

Copied

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!! 😄

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
People's Champ ,
Jan 10, 2019 Jan 10, 2019

Copy link to clipboard

Copied

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 ?

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 Beginner ,
Jan 11, 2019 Jan 11, 2019

Copy link to clipboard

Copied

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

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
People's Champ ,
Oct 26, 2016 Oct 26, 2016

Copy link to clipboard

Copied

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.

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 Beginner ,
Nov 04, 2016 Nov 04, 2016

Copy link to clipboard

Copied

By golly it seems to be working!

You're are genius Silly-V.

Check out this vid.


ColorMap.mov - Google Drive

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 ,
Nov 04, 2016 Nov 04, 2016

Copy link to clipboard

Copied

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

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
New Here ,
Apr 10, 2020 Apr 10, 2020

Copy link to clipboard

Copied

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!

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
New Here ,
Mar 23, 2022 Mar 23, 2022

Copy link to clipboard

Copied

LATEST

Same error, but on Line: 35

Error 24: row.split is not a function.

Line: 35

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

 

 

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