Charts and CSV

[R22MPM]

Hi All,
I am a newbie to Coogle charts and have been running through the following tutorial regarding connecting CSV files into charts.

http://code.google.com/apis/chart/interactive/docs/dev/dsl_get_started.html#webapp

I have a question which im hoping is pretty simple (i have limited JS knowledge)

I would like to use Gauge and Bar charts with the CSV data but as i understand it the columns will always be flagged as string, so my question is how and where do i convert the value column to a number?

Thanks
Matt

[Jinji]

Not all columns will be of type string, you choose the type. See the Java code snippet in the page you mentioned (http://code.google.com/apis/chart/interactive/docs/dev/dsl_get_started.html#introducing). Some columns are of type ValueType.TEXT, some are of type ValueType.NUMBER, and so on.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/LzWBFC1GzaEJ.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[R22MPM]

That page is using hardcoded values though isnt it?

The issue i have is that i need to change the JSON column format which is always string when using a CSV file?

On Sunday, March 4, 2012 1:59:57 PM UTC, Yuval Ronen wrote:

Not all columns will be of type string, you choose the type. See the Java code snippet in the page you mentioned (http://code.google.com/apis/chart/interactive/docs/dev/dsl_get_started.html#introducing). Some columns are of type ValueType.TEXT, some are of type ValueType.NUMBER, and so on.

On Fri, Mar 2, 2012 at 6:07 PM, R22MPM <r22...@googlemail.com> wrote:

Hi All,
I am a newbie to Coogle charts and have been running through the following tutorial regarding connecting CSV files into charts.

http://code.google.com/apis/chart/interactive/docs/dev/dsl_get_started.html#webapp

I have a question which im hoping is pretty simple (i have limited JS knowledge)

I would like to use Gauge and Bar charts with the CSV data but as i understand it the columns will always be flagged as string, so my question is how and where do i convert the value column to a number?

Thanks
Matt

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/LzWBFC1GzaEJ.

To post to this group, send email to google-visualization-api@googlegroups.com.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

[R22MPM]

I think i can do this by using the following function setColumnProperty, does anyone know the name of the porperty i need to change? i have tried ValueType but no joy

Thanks

[asgallant]

I don't work with the java applets, but if they implemented proper CSV parsing, then data types should be determined correctly, as CSV's usually have a syntax for handling strings and numbers differently (most often, they use quotes around strings, but there is no universal standard for CSV's, so YMMV).  If your CSV's cells are all getting parsed as strings when they should be parsed as numbers, I would look at it's formatting first.

[R22MPM]

Hi, thanks for the reply

I am using the example here : http://code.google.com/apis/chart/interactive/docs/dev/dsl_csv.html to parse the CSV.

I mentions in the commenting about always assuming the column is string, so i just need to work out how to change this

Matt

[asgallant]

Ok, I see it now.  You can override the column types by passing a list to the second parameter of the read call, see the description of the read method:  http://code.google.com/apis/chart/interactive/docs/dev/dsl_javadocs/com/google/visualization/datasource/util/CsvDataSourceHelper.html 

[R22MPM]

Would i need to do this in the Java Class file rather than in the JavaScript on the page?

If so it should be fairly easy to change but how do i edit the JAR or Class file? opening in something like Programmers Notepad give a load of rubbish text

Thanks

[asgallant]

That change would need to be made in the java source code for the server applet.  I think JAR files are compressed files, you should be able to open them with something like 7-zip (winzip or winrar should be able to do it too), and extract the source code within.

If you want to do it in javascript, you can accomplish the same task with a bit of a workaround, by using a DataView with calculated columns.  For example, if you had a DataTable with two columns, both of type string, and you want the first column to stay a string and you want the second to be evaluated as an integer, then you could do this:

​var view = new google.visualization.DataView(data);
view.setColumns([0, {
    type: 'number',
    label: data.getColumnLabel(1),
    calc: function (dt, row) {
        return parseInt(dt.getValue(row, 1));
    }
}]);

chart.draw(view, options); 

[R22MPM]

Fantastic, thanks for that would you be able to give me an example of how i would implement that into this code?

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
 
  <script type="text/javascript">
  google.load('visualization', '1', {'packages':['gauge,table,piechart,orgchart,barchart']});
 
    // Set a callback to run when the API is loaded.
  google.setOnLoadCallback(init);
 
    // Send the queries to the data sources.
  function init() {
                                query = new google.visualization.Query('csv?url=http://localhost:8084/Dashboards/Data/LicenseCount.csv');
                             query.send(handleCsvLicenseCount);
                   }
  
    function handleCsvLicenseCount(response) {
    if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    }

    var data = response.getDataTable();
   
    }]);
    //var options = {
    //      width: 400, height: 120,
    //      redFrom: 90, redTo: 100,
    //      yellowFrom:75, yellowTo: 90,
    //      minorTicks: 5
    //    };
    var chart = new google.visualization.gauge(document.getElementById('csv_div'));
    chart.draw(data, options);
   
    }
   
    </script>

[asgallant]

It would look like this:

google.load('visualization', '1', {'packages': ['gauge','table','piechart','orgchart','barchart']}); 

// Set a callback to run when the API is loaded.
google.setOnLoadCallback(init);

// Send the queries to the data sources.
function init() {
    query = new google.visualization.Query('csv?url=http://localhost:8084/Dashboards/Data/LicenseCount.csv');
    query.send(handleCsvLicenseCount);
}

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

    var data = response.getDataTable();

    var view = new google.visualization.DataView(data);
    view.setColumns([0, {
        type: 'number',
        label: data.getColumnLabel(1),
        calc: function (dt, row) {
            return parseInt(dt.getValue(row, 1));
        }
    }]);

    //var options = {
    //      width: 400, height: 120,
    //      redFrom: 90, redTo: 100,
    //      yellowFrom:75, yellowTo: 90,
    //      minorTicks: 5
    //    };
    var chart = new google.visualization.gauge(document.getElementById('csv_div'));

    chart.draw(view, options);
}​ 

Note that you have to load each package as it's own entry in the 'packages' array, not as a comma-separated string.

If you want the most up-to-date versions of the charts, load the 'corecharts' package, rather than the individual chart packages (which are no longer being updated):

google.load('visualization', '1', {'packages': ['corechart']}); 

[R22MPM]

Hmm i have just pasted that into my code with no joy, no chart is displayed. this is my complete code, can you spot anything wrong?

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="generator" content="CoffeeCup HTML Editor (www.coffeecup.com)">
    <meta name="created" content="Fri, 02 Mar 2012 12:28:28 GMT">
    <meta name="description" content="">
    <meta name="keywords" content="">
    <title>FT Support Dashboard</title>
      <!--Load the AJAX API-->

  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
 
  <script type="text/javascript">
  google.load('visualization', '1', {'packages':['gauge,table,piechart,orgchart,barchart']});
 
    // Set a callback to run when the API is loaded.
  google.setOnLoadCallback(init);
 
    // Send the queries to the data sources.
  function init() {
                                query = new google.visualization.Query('csv?url=http://localhost:8084/Dashboards/Data/LicenseCount.csv');
                             query.send(handleCsvLicenseCount);
                   }
  
    function handleCsvLicenseCount(response) {
    if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    }

    var data = response.getDataTable();
   

    var view = new google.visualization.DataView(data);

    view.setColumns([1, {

    type: 'number',
    label: data.getColumnLabel(1),
    calc: function (dt, row) {
        return parseInt(dt.getValue(row, 1));
    }
    }]);
   

    //window.alert(view.getcolumntype(1))

   
    //var options = {
    //      width: 400, height: 120,
    //      redFrom: 90, redTo: 100,
    //      yellowFrom:75, yellowTo: 90,
    //      minorTicks: 5
    //    };
    var chart = new google.visualization.gauge(document.getElementById('csv_div'));
    chart.draw(view, options);
   

    //var data1 response.getDataTable();
   
    //    var chart = new google.visualization.Table(document.getElementById('csv_div1'));
    //    chart.draw(data, null);
  }
   
    </script>
     <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
  </head>
  <body>
  SAMPLE TEXT
          <div id="csv_div" style="width: 400px"></div>
        <div id="csv_div1" style="width: 400px"></div>
  </body>
</html>

On Friday, March 2, 2012 4:07:10 PM UTC, R22MPM wrote:

On Friday, March 2, 2012 4:07:10 PM UTC, R22MPM wrote:

[asgallant]

You're google.load call is formatted incorrectly, as I mentioned before, it should be:

google.load('visualization', '1', {'packages': ['gauge','table','piechart','orgchart','barchart']});

[R22MPM]

Thanks for that, i have tried but unfortunately the gauge is not showing, neither is the data in a table? is there any debugging i can put into this to step through what the code is doing?

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
 
  <script type="text/javascript">

    google.load('visualization', '1', {'packages': ['gauge','table','piechart','orgchart','barchart']});

    // Set a callback to run when the API is loaded.
    google.setOnLoadCallback(init);

    // Send the queries to the data sources.
    function init() {
    query = new google.visualization.Query('csv?url=http://localhost:8084/Dashboards/Data/LicenseCount.csv');
    query.send(handleCsvLicenseCount);
    }

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

    var data = response.getDataTable();

    var view = new google.visualization.DataView(data);

    view.setColumns([0, {

        type: 'number',
        label: data.getColumnLabel(1),
        calc: function (dt, row) {
            return parseInt(dt.getValue(row, 1));
        }
    }]);

    var options = {
          width: 400, height: 120,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
          minorTicks: 5

        };
    var chart = new google.visualization.gauge(document.getElementById('csv_div'));
    chart.draw(view, options);
    }


       

        </script>
         <!--[if IE]>
        <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
        <![endif]-->
      </head>
      <body>
      SAMPLE TEXT
            <div id="csv_div" style="width: 400px"></div>
            <div id="csv_div1" style="width: 400px"></div>
      </body>

On Friday, March 2, 2012 4:07:10 PM UTC, R22MPM wrote:

[asgallant]

Hmm...

You can use Chrome's developer console to get information on any errors that may be occurring, and you can view the output from console.log(<variable>) there (use on your DataTable object to see what it contains). 

If there is no data in the DataTable or the handleCsvLicenseCount function is never called, then I suspect the problem is with the query.  Is the java data source API located in the same directory as your HTML file?  If not, you need to include the path to the java data source API in the query.  I am also uncertain of the format the API expects for the URL; using 'localhost' in the URL would normally point to your own system, but in this case it probably refers to the server itself, so you should make sure the CSV is in the right place.  Someone who is familiar with the java data source API would be able to help you more than I can here.

[R22MPM]

Ill check in Google Chrome this evening, i definately know there is data in the datatable because i can populate a table with the data. i am also navigate to the csv which returns the data in JSON format

Thanks
Matt

[R22MPM]

Thanks for your help, i have now sorted it and the charts are displaying correctly

for reference this is what i used

   var data = response.getDataTable();
   
    var view = new google.visualization.DataView(data);

    view.setColumns([0, {calc:converttonumber, type:'number', label: "TEST"}]);
        function converttonumber(data, rowNum){
        return Math.floor(data.getValue(rowNum, 1) / 1);

[asgallant]

Glad to hear you got it working.  You can use the parseInt() (or parseFloat(), if you ever need floating point numbers) function to make your conversion function a bit cleaner:

function convertonumber(data, rowNum) {

    return parseInt(data.getValue(rowNum, 1));

[Marco Alves]

Hi all,

 

I al having some problems with the use of the sample code of this topic. When I try to acess the file, the following error appears:

 

SCRIPT1014: Invalid character

teste.csv&tqx=reqId%3A0, line 1 character 1

 

Can somenone explain what is the resaon for his error and how to solve it?

 

Best regards,

Marco

[asgallant]

Can you post the code you are using?  I'll take a look.

[Marco Alves]

Yes, the code is this one:

 

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="generator" content="CoffeeCup HTML Editor (www.coffeecup.com)">
    <meta name="created" content="Fri, 02 Mar 2012 12:28:28 GMT">
    <meta name="description" content="">
    <meta name="keywords" content="">
    <title>FT Support Dashboard</title>
      <!--Load the AJAX API-->

  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
 
  <script type="text/javascript">

    google.load('visualization', '1', {'packages': ['gauge','table','piechart','orgchart','barchart']});
                    
    // Set a callback to run when the API is loaded.

    google.setOnLoadCallback(initialize);            

              
    // Send the queries to the data sources.

    function initialize() {
    query = new google.visualization.Query('csv?url=http://pedit.no-ip.org:81/logs/PR/teste.csv');
    query.send(handleCsvLicenseCount);
    }

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

   var data = response.getDataTable();
   
    var view = new google.visualization.DataView(data);

    view.setColumns([0, {type:'date', label: "TEST"}]);
    view.setColumns([1, {calc:converttonumber, type:'number', label: "TEST"}]);
   
function convertonumber(data, rowNum) {
    return parseFloat(data.getValue(rowNum, 1)); }

    var options = {
          width: 400, height: 120,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
          minorTicks: 5

        };
    var chart = new google.visualization.gauge(document.getElementById('csv_div'));
    chart.draw(view, options);
    }

       

        </script>
         <!--[if IE]>
        <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
        <![endif]-->
      </head>
      <body>
      SAMPLE TEXT
            <div id="csv_div" style="width: 400px"></div>
            <div id="csv_div1" style="width: 400px"></div>
      </body>

 

[asgallant]

Ok, I suspect that you can't use quite the same method as the previous user without some modifications to your server-side code.  The query url "csv?url=http://pedit.no-ip.org:81/logs/PR/teste.csv" is looking for a local resource called "csv" and passing it a url parameter.  The original poster had a java applet that transformed the CSV into the Visualization API format - are you using the same thing?

[Marco Alves]

No, that link corresponds to a csv file...

 

How it is used that java applet that transforms the csv file into the visualization API?

[asgallant]

I've never used the java applet, so I can't help you with that.  The easier thing to do is to load the CSV via an AJAX call, and then parse the contents into a DataTable.  There's a thread here with an example of using jQuery (along with some jQuery plugins) to handle the hard work for you.