Charting a CVS File

4,309 views
Skip to first unread message

Ken Burkhalter

unread,
Nov 29, 2014, 7:07:46 PM11/29/14
to google-visua...@googlegroups.com
I seldom get stumped, but sure am now.

Been trying for two days to figure out how to read a CVS Table that contains Time increments in Col-1 (x-axis) and multiple Temperature Columns-2 to 6 (y-axis) and make it ready to display as a Line Chart in Google Charts.

Any sample code to read a local (CSV) data file and make it ready to Chart (I think I have all the rest would be GREATLY appreciated.

This doesn't seem to work, although I thought it should  (my data file is tempData.csv in the same directory folder as the page HTML code).  All I get is a blank page displayed ....


<html>
  <head>
   <script src="https://www.google.com/jsapi"> </script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
   <script src="jquery.csv-0.71.js"> </script>
  
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
  
 
      function drawChart() {
   // Fetch the CSV
   $.get("tempData.csv", function(csvString) {
      // transform the CSV string into a 2-dimensional array
      var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
      // this new DataTable object holds all the data
      var data = new google.visualization.arrayToDataTable(arrayData);
      // For simplicity let's only look at one data column
      var view = new google.visualization.DataView(data);
      view.setColumns([0,1]);
     // set chart options
     var options = {
        title: "Daily Temps"
        hAxis: {title: data.getColumnLabel(0), minValue: data.getColumnRange(0).min, maxValue: data.getColumnRange(0).max},
        vAxis: {title: data.getColumnLabel(1), minValue: data.getColumnRange(1).min, maxValue: data.getColumnRange(1).max},
        legend: 'none'
     };
     // create the chart object and draw it
     var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

Thanks for any help anyone can offer.

Sergey Grabkovsky

unread,
Dec 1, 2014, 11:48:54 AM12/1/14
to google-visua...@googlegroups.com
Hi Ken,

We actually have undocumented dataTableFromCsv support, which is built into our Query object. Namely, we expose two new options: 'csvColumns' (which should be an array of column types), and 'csvHasHeader' (which determines whether the first row of the CSV should be interpreted as a header row). The file will be interpreted as CSV if you specify the csvColumns option, so it should be fairly straightforward. One thing to keep in mind is that you can only load CSV files that are on the same domain as your chart.

So, if you have a URL to your CSV file stored in a variable, call it csvUrl. That means that we would do something like the following:
var queryOptions = {
  csvColumns: ['number', 'number' /* Or whatever the columns in the CSV file are */],
  csvHasHeader: true /* This should be false if your CSV file doesn't have a header */
}

var query = new google.visualization.Query(csvUrl, queryOptions);

query.send(handleQueryResponse);

function handleQueryResponse(response) {

  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  // Draw your chart with the data table here.
}
--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

Ken Burkhalter

unread,
Dec 1, 2014, 4:14:17 PM12/1/14
to google-visua...@googlegroups.com
Sergey-

Thank you very much for you reply.  I truly appreciate it.

If you could go another step further it would be a GREAT help.

I am just starting to learn JavaScript, so run into brick walls sometimes that are probably "no brainers" to experienced folks.  :-)

My big challenge right now is related to your "...if you have a URL to your CSV file stored in a variable, call it csvUrl..." comment.  I have not been able to figure out how to access a .cvs data file stored in the same folder as the page HTML file.  I've tried to access it as "file:///C:\Users\keb\Desktop\Charting\GoogleCharts/tempData.csv" but that doesn't produce any results.  I've also tried lots of variants of that without success, so I don't know if the file reference method is bad or the follow-on code is the problem.

If you could fill in or correct my "// Fetch the CSV" code segment (see below in my original message) to show how to get the file and use it with your suggested code (included in your reply), that would be a powerful help to get me started.

If I can just get to the point where I can start to display a chart I feel confident I can rapidly move to the final result I seek.

Thanks again for your assistance.
 -ken b   [:-)}
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/cnXYDr411tQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

-- 
-ken burkhalter

Sergey Grabkovsky

unread,
Dec 1, 2014, 4:19:07 PM12/1/14
to google-visua...@googlegroups.com
Hi Ken, you might be running into an issue with the same-origin policy of your browser. If I remember correctly, you're not allowed to load files over the file:// protocol. So you will need to start up a light webserver (I'm partial to the python one, which you can just start with "python -m SimpleHTTPServer") and host both your page and CSV file over that. Unfortunately, I'm not sure I can explain detailed instructions for that over email, so if you don't know about how servers and clients work, you may want to read up on that.

Jon Orwant

unread,
Dec 1, 2014, 4:28:31 PM12/1/14
to google-visua...@googlegroups.com
Elaborating on what Sergey said, another alternative -- and probably what you want to do if your CSV data is unchanging -- is to convert the CSV data "by hand" into a data table, so that everything is inside your JavaScript program. 

For instance, as a first step cut and paste your data like so:

var data = google.visualization.arrayToDataTable([
    ...cut and paste your CSV here...
]);

That won't work as is, but by looking at other sample charts you can identify what tweaks you need to make (e.g., enclosing each line of your CSV file in square brackets).

I would start by cutting and pasting one line from your CSV file and going from there. That will enable you to iterate quickly, confirming what changes you need to make before you deal with the entire file.

Jon

Ken Burkhalter

unread,
Dec 3, 2014, 9:31:04 AM12/3/14
to google-visua...@googlegroups.com
Thanks all for the replies.

I have no problem charting manually entered data [as you had suggest Jon], my issue is in grabbing data from a file on my web server and charting it.

I tried your code suggestions Sergey, but am still coming up with a blank screen.

My code is shown below, hopefully someone can spot what I am doing wrong, rather than trying to give a verbal explanation of what to do. 

Sergey note I was unsure what the values should be in the csvColumns parameter. Since a search didn't reveal the definitions of the cvsColumns parameter, I assumed that 'number' meant there were number values in each column (which is true).  

Does there need to be a 'number' value stated for each column in the data array (a csv file with three [column] entries per line.)  In which case my my parameter should be csvColumns: ['number', 'number', 'number']

Also at the end of the csvColumns line, in your code sample, the code used a "," (coma),  Should that have really been a semi-colon (;) ?

<html>
  <head>
   <script src="https://www.google.com/jsapi"> </script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
   <script src="jquery.csv-0.71.js"> </script>
   
   <script>
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
 /////////////
var queryOptions = {
csvColumns: ['number', 'number' /* Or whatever the columns in the CSV file are */],
csvHasHeader: false /* This should be false if your CSV file doesn't have a header */
}

var query = new google.visualization.Query(csvUrl, queryOptions);

query.send(handleQueryResponse);

function handleQueryResponse(response) {

if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

  var data = response.getDataTable();
 /
    function drawChart() {
  
     // set chart options
     var options = {
        title: "Temperatures",

Jon Orwant

unread,
Dec 3, 2014, 9:38:25 AM12/3/14
to google-visua...@googlegroups.com
Ken, comments inline.

On Wed, Dec 3, 2014 at 9:31 AM, Ken Burkhalter <kenbur...@gmail.com> wrote:
Thanks all for the replies.

I have no problem charting manually entered data [as you had suggest Jon], my issue is in grabbing data from a file on my web server and charting it.

I tried your code suggestions Sergey, but am still coming up with a blank screen.

My code is shown below, hopefully someone can spot what I am doing wrong, rather than trying to give a verbal explanation of what to do. 

Sergey note I was unsure what the values should be in the csvColumns parameter. Since a search didn't reveal the definitions of the cvsColumns parameter, I assumed that 'number' meant there were number values in each column (which is true).  

Does there need to be a 'number' value stated for each column in the data array (a csv file with three [column] entries per line.)  In which case my my parameter should be csvColumns: ['number', 'number', 'number']

Right.
 

Also at the end of the csvColumns line, in your code sample, the code used a "," (coma),  Should that have really been a semi-colon (;) ?

Nope, a comma.
 

<html>
  <head>
   <script src="https://www.google.com/jsapi"> </script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
   <script src="jquery.csv-0.71.js"> </script>
   
   <script>
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
 /////////////
var queryOptions = {
csvColumns: ['number', 'number' /* Or whatever the columns in the CSV file are */],
csvHasHeader: false /* This should be false if your CSV file doesn't have a header */
}

var query = new google.visualization.Query(csvUrl, queryOptions);

query.send(handleQueryResponse);

function handleQueryResponse(response) {

if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

  var data = response.getDataTable();
 /

You have a syntax error here because of the single slash on a line by itself.  That's why you're seeing a blank screen.

(How do I know this?  I cut and pasted your code and opened it up in my browser. You can tell what's going on in your browser by looking at the JavaScript console, which you can open up via your browser's menu.)

Jon
 
    function drawChart() {
  
     // set chart options
     var options = {
        title: "Temperatures",
        legend: 'none'
     };

     // create the chart object and draw it
   var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

Ken Burkhalter

unread,
Dec 3, 2014, 12:20:55 PM12/3/14
to google-visua...@googlegroups.com
Thanks for pointing out the syntax errors.  After your extraordinary hint about the Java Console (which I didn't know about) debugging has speeded up considerably!!! :-)

I found a few more errors (missing braces, undefined objects) and am now to the point where I am at least getting error comments posted to the screen.  :-)

I am now getting a "Data Table not Defined"  ( Uncaught TypeError: Cannot read property 'Query' of undefined) on the following code ...

  var query = new google.visualization.Query(csvURL, queryOptions);

Since your suggested undocumented dataTableFromCsv support code seemed to define the two Query parameters, I'm guessing I might be missing a Library Source reference.

If so, please advise URL, else what might I be missing?

Thanks.  [:-)}

Ken Burkhalter

unread,
Dec 3, 2014, 3:14:12 PM12/3/14
to google-visua...@googlegroups.com

In the event it helps.  Here is the complete HTML page...

 

Ken Burkhalter

unread,
Dec 3, 2014, 3:15:12 PM12/3/14
to google-visua...@googlegroups.com
The error is thrown at Line 22

Sergey Grabkovsky

unread,
Dec 3, 2014, 3:25:31 PM12/3/14
to google-visua...@googlegroups.com
The Query is loaded along with the charts, so the code that uses the Query needs to be in a callback as well. You need to structure your code like so:

google.load(...);
google.setOnLoadCallback(initialize);

function handleQueryResponse(response) {
  if (response.isError()) { ... }
  var data = response.getDataTable();
  var options = { ... };
  var chart = ...;
  chart.draw(data, options);
}

function initialize() {
  var datQuery = new google.visualization.Query(...);
  datQuery.send(handleQueryResponse);
}


--

Ken Burkhalter

unread,
Dec 3, 2014, 4:19:21 PM12/3/14
to google-visua...@googlegroups.com
Gad, this is worse than getting teeth pulled   [:-)}

I made the changes (I think, I've included a view of the current code below) but now I am getting the following error which doesn't seem to jibe with reality ...

"n.I.js:266 Uncaught Error: CSV files on other domains are not supported. Please use sendMethod: 'xhr' or 'auto' and serve your .csv file from the same domain as this page." 

which makes absolutely no sense at all as the page's html document sits in the same directory as all the other web server pages (including index.html). 

Everything is definately on the same domain!

Here


Sergey Grabkovsky

unread,
Dec 3, 2014, 4:22:58 PM12/3/14
to google-visua...@googlegroups.com
You might be accessing your HTML file via the file:// protocol, which would be a different domain than http://. If that's not the problem, then it would be immensely helpful if you could post a screenshot of your browser window (with the address bar and everything) accessing the page with the developer tools open.

Ken Burkhalter

unread,
Dec 3, 2014, 4:32:02 PM12/3/14
to google-visua...@googlegroups.com
Sergey -

Is this what you wanted?
   

Assuming you can view the  code capture I posted in the previous message, you can see that I am NOT using any "file" protocols.  Everything is nice Kosher HTTP tags.
 [:-)}
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/cnXYDr411tQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

-- 
-ken burkhalter

Ken Burkhalter

unread,
Dec 3, 2014, 4:38:19 PM12/3/14
to google-visua...@googlegroups.com

On 12/3/2014 4:32 PM, Ken Burkhalter wrote:
Sergey -

Let me do this over again so you are not mislead.  The first screenshot, I just sent, was a capture from my desktop computer where I have been debugging, but the actual HTML code page runs on my web serverm which is what the capture below reflects ....

-- 
-ken burkhalter

Sergey Grabkovsky

unread,
Dec 3, 2014, 4:46:50 PM12/3/14
to google-visua...@googlegroups.com
Hi Ken, You are indeed using the file:// protocol. You can tell, because the address you are accessing begins with file://, as in, "file:///C:/Users/keb/Desktop/Charting/GoogleCharts/!LineChartCode-test2.html".

 You should be accessing your page as "http://192.168.1.90/!LineChartCode-test2.html". Although, I would recommend against using characters such as "!" in your file name.

On Wed Dec 03 2014 at 4:38:04 PM Ken Burkhalter <kenbur...@gmail.com> wrote:

On 12/3/2014 4:32 PM, Ken Burkhalter wrote:
Sergey -

Let me do this over again so you are not mislead.  The first screenshot, I just sent, was a capture from my desktop computer where I have been debugging, but the actual HTML code page runs on my web serverm which is what the capture below reflects ....

   

Assuming you can view the  code capture I posted in the previous message, you can see that I am NOT using any "file" protocols.  Everything is nice Kosher HTTP tags.
 [:-)}


On 12/3/2014 4:22 PM, 'Sergey Grabkovsky' via Google Visualization API wrote:
You might be accessing your HTML file via the file:// protocol, which would be a different domain than http://. If that's not the problem, then it would be immensely helpful if you could post a screenshot of your browser window (with the address bar and everything) accessing the page with the developer tools open.

On Wed Dec 03 2014 at 4:19:26 PM Ken Burkhalter <kenbur...@gmail.com> wrote:
Gad, this is worse than getting teeth pulled   [:-)}

I made the changes (I think, I've included a view of the current code below) but now I am getting the following error which doesn't seem to jibe with reality ...

"n.I.js:266 Uncaught Error: CSV files on other domains are not supported. Please use sendMethod: 'xhr' or 'auto' and serve your .csv file from the same domain as this page." 

which makes absolutely no sense at all as the page's html document sits in the same directory as all the other web server pages (including index.html). 

Everything is definately on the same domain!

Here


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/cnXYDr411tQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.

-- 
-ken burkhalter

-- 
-ken burkhalter

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.

Ken Burkhalter

unread,
Dec 4, 2014, 10:02:46 AM12/4/14
to google-visua...@googlegroups.com
Sergey-

I can't tell you how much I appreciate your help and patience.

This is my first real attempt to do anything more than superficial in JavaScript, so the learning curve is steep but I'm climbing the hill faster than I thought I could (especially with your help).

Really surprised by the "other domains" issue, as I never would have imagined it applied on a call of a call.  The page's HTML document is in the root directory of my web server along with everything it is interacting with.  I would have never figured that I need to open that document any way other than just double-clicking on it.

Once I changed my approach (including changing the html document name from "!LineChartCode-test2.html" to "Chart.html") I also discovered that the only way I could get the script to run without errors, was if I used "http://127.0.0.1/tempsD1.csv" to access my data file.  A reference of "http://192.168.1.90/tempsD1.csv" does not work, even though that is the web server's actuall IP designation.

As a result of these changes, I can now execute the Graphing javascript file, but nothing at all happens.

I then inserted a console.log() instruction at Line 28 (see attached document capture), but nothing appears on the Java Console (which is probably why I am not getting a chart displayed either).

I can only, thus assume that for some reason the contents of my tempsD1.csv file is not getting read (although there are no errors posted in the Java Console).

Do I have the proper Libraries loaded, and/or are there any obvious mistakes in my attempts to get the data to feed the Charting?

Or, can you suggest some ways to debug the various Functions to see which might be failing?

Thank you for your continued efforts to assist.    [:-)}



Sergey Grabkovsky

unread,
Dec 4, 2014, 10:19:35 AM12/4/14
to google-visua...@googlegroups.com
You are defining your handleQueryResponse function twice, once on line 17, and another time one line 43. JavaScript will only ever execute the last defined function (with the same name), so in this case, it will execute the one that does practically nothing.

As an aside, in the future, could you please attach a raw HTML file, rather than a screenshot? It would be much easier for me to help you if I could just run your code or copy/paste it.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.

Ken Burkhalter

unread,
Dec 4, 2014, 10:42:38 AM12/4/14
to google-visua...@googlegroups.com
THANK YOU. THANK YOU. THANK YOU!

All works now.  :-)

That must have been the issue plaguing me for some time.

As soon as I deleted the redundant function, every thing worked perfectly.

I think I should be in pretty darn good shape from here on out, as once something works at least a little bit, it is then possible to start building and testing on that foundation.

Thanks again, and sorry about the screen image vs raw HTML.  I was thinking that since the issue might have been with my csv file access, and you couldn't access it anyway that the image would work.  Thanks for the suggestion, however, as from now on (if there are any more communications) I will be sure to send the HTML instead.  :-)

Thank you again, for your help.  It was a very exciting moment when my graph appeared on the screen !!!!!
 -ken b
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/cnXYDr411tQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

-- 
-ken burkhalter

Sergey Grabkovsky

unread,
Dec 4, 2014, 10:46:29 AM12/4/14
to google-visua...@googlegroups.com
I'm glad everything works now, Ken!

Hopefully charts will be fairly straightforward for you from this point, but if you have any further questions, please don't hesitate to ask.

Garima Shrivastava

unread,
Aug 8, 2019, 12:01:09 PM8/8/19
to Google Visualization API
Hi all,


Been trying for two days to figure out how to read a CVS Table that contains  Hour clo-1(x-axis) and Temperature col-2(y-axis) and make it ready to display as a scatter Chart in Google Charts.

attaching my sample code to read a local (CSV) data file and make it ready to Chart .
 
This doesn't seem to work, although I thought it should  (my data file is example.csv in the same directory folder as the page HTML code).  All I get is a blank page displayed ....


<html>
  <head>
   <script src="https://www.google.com/jsapi"> </script>
   <script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
   <script src="jquery.csv-0.71.js"> </script>
   
   <script>
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
  /////////////
    function drawChart() {
$.get("example.csv", function(csvString) {
            var arrayData = $.csv.toArrays(csvString, 
                                          {onParseValue: $.csv.hooks.castToScalar}),
                data      = new google.visualization.arrayToDataTable(arrayData),
  
     // set chart options
     var options = {
        title: "Temperatures",
        legend: 'none'
     };

     // create the chart object and draw it
    var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>
Testcsv.html
example.csv.xlsx

Karl Venter

unread,
May 3, 2021, 9:20:24 AM5/3/21
to Google Visualization API
Hi All

I am very very new to Google charts and would like some direction as to how to go about it
I have a file ( I can make it csv) that my solar system generates every 3 minutes or so with a time and date stamp for each of the different pieces of information
I then display the info on a web page depicting the current situation  I would like now to have a graph/google chart of any of the columns of information relative to the datestamp
My issue is getting the file thats been read into the chart with only the specific column I want   or columns I want / chose to display ?

 function drawChart() {
$.get("example.csv", function(csvString) {
            var arrayData = $.csv.toArrays(csvString, 
                                          {onParseValue: $.csv.hooks.castToScalar}),
                data      = new google.visualization.arrayToDataTable(arrayData),

but how to get only specific columns 
X axis time 
Y axis   Kw or Volts or amps or whatever I choose

You direction would be appreciated
Reply all
Reply to author
Forward
0 new messages