Problem merging dates from CSV into graph

[sfitzgeraldTU]

I'm attempting to merge data into an Illustrator file to create a batch of bar graphs based on a group of CSV files. I've followed the tutorial (Merge data to create data-driven graphics through templates and variables in Illustrator) successfully, except that my dates are merging as numbers instead of dates. I've attempted to format the dates as text, save the CSVs with UTF-8, to no avail. a 6/30/16 date merges into Illustrator as 6.00 and a 12/31/16 date merges as 12.00, so every June date and every December date appear the same. This also messes up the graph, leaving the chart with two categories instead of one of the data columns being assigned to the x-axis.

Screenshots below of my files.

Graphs Merge Document.csv

Sample 1 financials.csv

Original graph document:

Merged data and graph document:

[Mylenium]

Using slashes is not valid date formatting. Dates must be delimited with points or hyphens to work in AI.

Mylenium

[sfitzgeraldTU]

Changing the slashes to hyphens or to dots (periods) produces the same result.

And the dates work just fine with slashes when I manually input the graph data (intead of importing):

[jane-e]

sfitzgeraldTU  wrote

I'm attempting to merge data into an Illustrator file to create a batch of bar graphs based on a group of CSV files. I've followed the tutorial (Merge data to create data-driven graphics through templates and variables in Illustrator) successfully, except that my dates are merging as numbers instead of dates. I've attempted to format the dates as text, save the CSVs with UTF-8, to no avail. a 6/30/16 date merges into Illustrator as 6.00 and a 12/31/16 date merges as 12.00, so every June date and every December date appear the same.

Two ideas, and one or the other should work.

First:

Your dates are right aligned, meaning they are still numbers, assuming they have General formatting. You didn't say how you attempted to format the dates as numbers, but try this: type a single quote   '   in front of the date. If it's left aligned, it's text. If you have a lot of dates you might want to put the quote in another column and concatenate the two columns.

Second (if the first doesn't work):

If that doesn't work, use the formula =text(A1,"d/mm/yyyy")

I used two Ds; you'll want one D if you don't want the zero.

And if that still doesn't work, you'll need to copy the dates with the function (as in Column B) and in the next column choose Edit > Paste Special > Values to convert the functions into regular text.

If you are not on a Mac, then Paste Special is on your Home tab on the far left instead of the Edit menu.

You will now have three columns:

A     Dates, which are actually numbers

B     Functions to convert the Date/Numbers to text

C     Text

They will all look the same. When you see which works, you can decide what to do with the other two.

[sfitzgeraldTU]

I have tried all of these, formatting the dates as text does not solve the issue. They continue to import into AI as numbers (6.00 and 12.00) Thus far the only workaround I've found is to format the dates as spelled-out text (ex. June 31, 2017), but this does not meet the standards of our style guide.

[jane-e]

It may be a bug in Illustrator. Can you give the steps you are using? I have time to test it if I get them this afternoon. After that things will be crazy here.

[sfitzgeraldTU]

Thanks jane-e, I appreciate your assistance.

I set up file 1 in Excel. All columns are formatted as text. First column contains apostrophes to indicate text. Save it as a CSV.

Set up file 2 in Excel. All columns are formatted as text. Save it as a CSV.

Open up my pre-designed bar graph in Illustrator. Open the Properties panel and the Variables panel.

In the Variables panel, click Import and select file 2. Select the bar graph in the document. Select Graphs in the Variables panel. Click button to make graph dynamic, linking Graphs variable to graph object. In Data Set drop-down, select Data Set 1.

Interestingly, even if the dates are formatted in the CSV as dates but I choose the spelled-out format, the data merges correctly...

[Silly-V]

Looks like the VariableImporter.jsx script 'triumps' again. Many thanks to Dmitriy Gritsenko who helped with the busy work of parsing the graph data.

Script is located here: Adobe-Illustrator/VariableImporter.jsx at master · Silly-V/Adobe-Illustrator · GitHub

To anyone who would like to support my cause in search for some humble credits, please vote here: Let's add Vasily Hall's name to the Ai credits? – Adobe Illustrator Feedback

[jane-e]

Silly-V  wrote

Script is located here: Adobe-Illustrator/VariableImporter.jsx at master · Silly-V/Adobe-Illustrator · GitHub

To anyone who would like to support my cause in search for some humble credits, please vote here: Let's add Vasily Hall's name to the Ai credits? – Adobe Illustrator Feedback

Voted!

[Silly-V]

You're awesome!

Also, for anyone who wants to read extremely nerdy Illustrator variable data articles,  you may visit my LinkedIn profile where there's around 20 of them.

https://www.linkedin.com/in/vasily-hall-91216618

[sfitzgeraldTU]

Thanks Silly-Vjane-e . Wish there was a native solution or fix, as I have to train a handful of people with varrying degrees of savvy on this, and I'll have to install the script for all of them. But if that's the best solution that currently exists, we'll work with what we've got.

If anyone else comes across an Illustrator-based, no-script-required solution, I'd love to know.

[Silly-V]

The script isn't too bad, in fact it may make some things easier. I am fully confident that you will find it a breeze.

Especially with the presets feature and others which may actually reduce your clicks even more from dealing with the native variables.

For example, you can save your file paths in a preset, which will let you simply put the file names in the data cells vs the entire path.

Additionally, the auto-binding feature will save the time you've got to click the little lego box to make objects dynamic.

Finally, be in control of how your datasets are named with the Dataset Assignment dialog in the Options tab. These are also saved as part of the presets so you can have variable dataset names for different categories of jobs.

[jane-e]

Vasily, I've just been Googling you and your script and I just want to say thank you for making this happen in Illustrator.

SFitzgerald, this is your best solution. Scripts are intimidating to look at and intimidating to write, but easy to run and to use.