Creating a json file for Google charts

[Kevin Regan]

Hi,

I'm having trouble understanding how to generate json files to use in Google charts. 

Basically, I have excel files which I want to convert to json on a monthly basis in order to display the data in Google charts. 

I would like to automate this process. I guess I need to have something parse the excel file according to some defined structure and generate the json file. Can someone help me understand how this could work?

thanks,

K

[asgallant]

Parsing Excel files manually is a painful process.  I'm given to understand that there is an Office service that ties into ASP.net which can handle the hard part of parsing the Excel file, but I don't know what it is or how it works.  The simple thing to do is to save the file as a csv, which makes it easy to read.

What scripting language do you plan to use server-side?

[Kevin Regan]

Hi, I managed to get this working using a script which reads the a csv string. I use an ajax call to return the csv string and pass te string to a javascript function (called csv.toArrays) which 'loads' the csv data into a 2 dimensional array which can then be passed to arrayToDataTable to create a google vis data table. 

It works,as in I can then draw a table. 

However, the issue which arises is that, by using arrayToDataTable, the data types of each column are interpreted automatically from the data given. It appears the number types are wrongly being interpreted as strings. Therefore I can not draw graphs. When I try I receive the error "Data column(s) for axis #0 cannot be of type string". Is there any way to force the data type to be the correct type? Thanks.

the script to convert a csv string into a 2D array is here:

http://code.google.com/p/jquery-csv/

my code to read the csv file is below.

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

    <script type="text/javascript" src="jquery-1.6.2.min.js"></script>

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

    <script type="text/javascript" src="https:/whereeveryousaveitlocally/jquery.csv-0.64.js"></script>

    <script type="text/javascript">

    // Load the Visualization API and the piechart package.

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

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

      

    // Set a callback to run when the Google Visualization API is loaded.

    google.setOnLoadCallback(drawChart);

 function drawChart() {

      

      //looad and prepare data

      var csvString = $.ajax({

            url: "https://....file.csv",

            dataType:"text",

            async: false

            }).responseText;

      

      var csvArray = $.csv.toArrays(csvString)

      // Create our data table out of csv file data loaded

      var data = new google.visualization.arrayToDataTable(csvArray);

        var table = new google.visualization.Table(document.getElementById('table_div'));

        table.draw(data, {showRowNumber: true});  }

cheers

K

[asgallant]

Check the csvArray to make sure it is being parsed correctly.  If your numbers are being parsed as strings, then there is either a problem in your csv file or there is a problem in the toArrays method (more likely the former than the latter, as I've seen the latter work).

If there isn't anything wrong with the csv file (or you can't fix it if there is), then you can force data type conversion by manually parsing the array and reassigning the strings to numbers using the parseInt and/or parseFloat functions.

If you're still having trouble with it, post a sample csv that replicates the problem (preferably something small) and I'll take a look.

[Kevin Regan]

Hi asgallant, thanks for your help. 

I've checked the csv file (attached) and looked at the array reculting from toArrays.

Looping document.write(cvsArray[i]) I see the contents of csvArray. I see the content as:

Element 0 contains: Country,VAL1,VAL2,Percent
Element 1 contains: Austria,1,2,1.11
Element 2 contains: Algeria,252144,253327,99.53

After arrayToDataTable(csvArray), I used data.getColumnType() so see the type of each column created. Each column is of type string. I expect only column 0 to be a string.

The csv file seems ok to me. I don't understand why the columns are strings. Any ideas would be helpful!

Thanks,

K

[asgallant]

What browser are you using?  I can't replicate the problem in Chrome.

[Kevin Regan]

I'm using IE7. 

[Kevin Regan]

I solved this by parsing the number values stored as string to integers using parseInt and saving them to a 2d array. I then create the columns of the datatable one at a time, stating the data type for each. 

Finally, I add the rows one at a time from the 2d array.

Not the most efficient method, but it works.

thanks!