Convert CSV or JSON File to Google Charts!

[cyb]

Hi,

i get different data from a csv or json file. and this files must pe parsed that i can use it with google charts..

so what is the best way to do this ?

First the CSV:

i get different CSV files they look like this:

date,New York,San Francisco,Austin

20111001,63.4,62.7,72.2

20111002,58.0,59.9,67.7

20111003,53.3,59.1,69.4

20111004,70.0,80.0,90.0

i have used the following javascript  library to convert my csv to an Array that can google charts understand: https://code.google.com/p/jquery-csv/

but the problem is, this library did not work on mobile devices, i have tested it on an ipad.

So what is the easiest an best solution for parsing a csv to the google format ? the solution must run on mobile devices.

Or are there any Frameworks that i can use on server side (java) to convert a csv to a json object that google understands !? Then i can send the json file to my website that google charts can read it ! 

Best regards cyb

[Jeremy Faller]

We have functions to do this, but they're not exported. I'll file a bug to export them for the next release (not v38, which is due out any day now).

In the meantime, if you can implement a change server side, you can have your data conform to the DataSource protocol (https://developers.google.com/chart/interactive/docs/dev/implementing_data_source), and Google Charts will handle csv very well.

[cyb]

What did you mean with functions ? Are these client side parser-functions for csv data ?

But google charts can at the moment not parse csv data directly on the client side ?

On Server-Side is use Spring MVC.. On my Website someone can upload a csv as a MultiPart Upload to my server or directly to my website.

on the Server-Side there is a Rest-Service that gets the Multipart csv file:

@RequestMapping(value="/upload", method=RequestMethod.POST)

    @ResponseStatus(value=HttpStatus.NO_CONTENT)

    public @ResponseBody String handleFileUpload(@RequestParam(value="name", required=false) String name, 

            @RequestParam(value="file1") MultipartFile file){

             //Reads the CSV File

            InputStream inputStream = file.getInputStream();

            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));

}

Is it now possible that i can convert the PartFile with the csv to a DataTable ?

On the Client Side i have the same csv part file.. i can read it with a FileReader();

Best regards

[Jeremy Faller]

inlined responses:

On Monday, March 24, 2014 2:17:51 PM UTC-4, cyb wrote:

What did you mean with functions ? Are these client side parser-functions for csv data ?

Within the code for Google charts, there exists code to parse CSV, but it's not currently exported and visible to users yet. As such, it's there you just can't access it yet.

 

But google charts can at the moment not parse csv data directly on the client side ?

Correct, not yet. I'll file a bug to expose these functions to users for future releases. (Again, this will not be in the v38 release that's going out any day now, expect it in v39.)

 

On Server-Side is use Spring MVC.. On my Website someone can upload a csv as a MultiPart Upload to my server or directly to my website.

on the Server-Side there is a Rest-Service that gets the Multipart csv file:

@RequestMapping(value="/upload", method=RequestMethod.POST)

    @ResponseStatus(value=HttpStatus.NO_CONTENT)

    public @ResponseBody String handleFileUpload(@RequestParam(value="name", required=false) String name, 

            @RequestParam(value="file1") MultipartFile file){

             //Reads the CSV File

            InputStream inputStream = file.getInputStream();

            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));

}

Is it now possible that i can convert the PartFile with the csv to a DataTable ?

 

On the Client Side i have the same csv part file.. i can read it with a FileReader();

Okay, you have a couple of options, all of which require client side, or server side code that you need to write.

1. You can have your data export the user data in the DataSourceFormat (https://developers.google.com/chart/interactive/docs/dev/implementing_data_source). This would require you parse the data server side, either when the user uploads it, or when the user downloads it for display. With this format, GViz can directly display the data.
   
   (Basically, GViz will connect directly to your server, download the data, and display it in the div.)
   
   Again, you'd need to parse the data server side, and have it sent back to your client side JS in the above format.
   
   
2. Your second option is to parse the data client side, and generate a data table there. That's what you're currently doing, but your JS parser doesn't work on mobile devices. Right now, as GViz doesn't export its CSV parser, you could: write your own parser (probably not too hard to get simple cases right), find a different parser (https://code.google.com/p/csv-to-array/ seems to be one to try), or wait for the next gviz release.
   
   

[cyb]

Hi,

i have created a Service that returns me a DataTable but can i do querying with a DataTable or DataView ?
Like "select A, D >100 ORDER BY D" ?? if yes how can i do that with a DataTable/DateView?

Bet regards

[asgallant]

You can't query like that, but you can get a list of rows filtered by some criteria, with the getFilteredRows method (works with both DataTables and DataViews).  You can select which columns to use by creating a DataView and specifying the columns in the setColumns method.

[cyb]

Hi,

i use spring an have created the following service.

@Controller
@RequestMapping("REST")
public class LoadDataSource {

@Autowired
private HttpServletRequest request;

@RequestMapping(value="/DataSource/{Id}",method=RequestMethod.GET, produces=MediaType.APPLICATION_JSON_VALUE)
    public @ResponseBody DataTable createDataSource( @PathVariable Integer Id){

DataTable data = generateMyDataTable2();
   

  return data;
  
       
        }
private DataTable generateMyDataTable2() {
   // Create a data table,
   DataTable data = new DataTable();
   ArrayList<ColumnDescription> cd = new ArrayList<ColumnDescription>();
   cd.add(new ColumnDescription("id1", ValueType.NUMBER, "Date"));
   cd.add(new ColumnDescription("id2", ValueType.NUMBER, "Berlin"));
   cd.add(new ColumnDescription("id3", ValueType.NUMBER, "Offenburg"));
   cd.add(new ColumnDescription("id4", ValueType.NUMBER, "Zunsweier"));
   
   data.addColumns(cd);
   // Fill the data table.
   try {
     data.addRowFromValues(2010, 10, 12, 22);
     data.addRowFromValues(2011, 20, 34, 33);
     data.addRowFromValues(2012, 30, 23, 45);
     data.addRowFromValues(2013, 40, 25, 50);
   } catch (TypeMismatchException e) {
     System.out.println("Invalid type!");
   }
   return data;
 }

if i call this it returns me the following:

{"rows":[{"cells":[{"value":{"value":2010.0,"type":"NUMBER","null":false,"objectToFormat":2010.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":10.0,"type":"NUMBER","null":false,"objectToFormat":10.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":12.0,"type":"NUMBER","null":false,"objectToFormat":12.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":22.0,"type":"NUMBER","null":false,"objectToFormat":22.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false}],"customProperties":{}},{"cells":[{"value":{"value":2011.0,"type":"NUMBER","null":false,"objectToFormat":2011.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":20.0,"type":"NUMBER","null":false,"objectToFormat":20.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":34.0,"type":"NUMBER","null":false,"objectToFormat":34.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":33.0,"type":"NUMBER","null":false,"objectToFormat":33.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false}],"customProperties":{}},{"cells":[{"value":{"value":2012.0,"type":"NUMBER","null":false,"objectToFormat":2012.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":30.0,"type":"NUMBER","null":false,"objectToFormat":30.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":23.0,"type":"NUMBER","null":false,"objectToFormat":23.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":45.0,"type":"NUMBER","null":false,"objectToFormat":45.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false}],"customProperties":{}},{"cells":[{"value":{"value":2013.0,"type":"NUMBER","null":false,"objectToFormat":2013.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":40.0,"type":"NUMBER","null":false,"objectToFormat":40.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":25.0,"type":"NUMBER","null":false,"objectToFormat":25.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false},{"value":{"value":50.0,"type":"NUMBER","null":false,"objectToFormat":50.0},"formattedValue":null,"customProperties":{},"type":"NUMBER","null":false}],"customProperties":{}}],"customProperties":{},"warnings":[],"localeForUserMessages":null,"columnDescriptions":[{"id":"id1","type":"NUMBER","label":"Date","pattern":"","customProperties":{}},{"id":"id2","type":"NUMBER","label":"Berlin","pattern":"","customProperties":{}},{"id":"id3","type":"NUMBER","label":"Offenburg","pattern":"","customProperties":{}},{"id":"id4","type":"NUMBER","label":"Zunsweier","pattern":"","customProperties":{}}],"numberOfColumns":4,"numberOfRows":4} 

But if i add this Directly to "dataView = new google.visualization.DataView({"rows":[{"cells":[{"value":{"value":2010.0,....." i get the following error "TypeError: Object #<Object> has no method 'I'" What is wrong with my code ? and can i load my service directly with the google api ? i want only the dataTable, that i can work with it.

 

[cyb]

Hi,

ok i have found my mistake..

i must use the JsonRenderer "JsonRenderer.renderDataTable(data, true, true, true)" and then i can use on the Client side "var data= new google.visualization.DataTable(json)".

But how can i parse a csv on the server side to a DataTable ? i have the following csv:

date,New York,San Francisco,Austin

20111001,63.4,62.7,72.2

20111002,58.0,59.9,67.7

20111003,53.3,59.1,69.4

20111004,70.0,80.0,90.0

and if the dataTable is created on server side can i change then the date Colum to a Date Object ?

 

Am Montag, 24. März 2014 17:39:44 UTC+1 schrieb cyb:

[asgallant]

To your first question, the problem is that you can't pass the JSON to the DataView constructor, you have to create a DataTable object first, and then pass the DataTable to the DataView constructor:

var data= new google.visualization.DataTable(json);
var dataView = new google.visualization.DataView(data);

To create a date column server-side, you need to follow a similar approach as I outlined in your other thread (split the string into year, month, day, subtract 1 from month to make it compatible with javascript's date objects).  Instead of creating a date object (which are not suported by JSON), you need to create a date string like this:

"Date(year, month, day)"

It is almost exactly the same as what you would do in javascript, except it is a string, and there is no "new" keyword used.  The Visualization API will parse that string as a date if the data type of the column is set to "date".

[cyb]

Hi,

i have a problem with the date format on server side..

i parse my data with the build in csv parser from the google java data source library:

DataTable dataTable =CsvDataSourceHelper.read(reader2, columnDescriptions, true, null);

so now are alle Values Strings or numbers in my DataTable..

how can i change the values for examle from colum 0 and 1 to the Type "Date" and format it to a date object ?

can i use this :

dataTable.setCell(rowIndex, colIndex, cell)
               TableCell tc=new TableCell();


? can you make an example how i can change the column 0 and 1 to the type date and format the String ?

[cyb]

Hi,

ok i think i must chance some things on the java library that it worked for me..

The java library did add on the DataTable object on every Column pattern :""  if this pattern value is set with an empty string can i still use the formatType ? the documentation says that i cannot use pattern and formatType together..

and second questions:

i think it is easier to build a  DataArray on server-side  i have do this and my array looks like this:

{"DataArray":[["Date","New York","Austin"],["Date(1975,8,6)",200,300],["Date(1980,8,6)",20,30]]}

then i can use on client side  var dataTable=new google.visualization.arrayToDataTable(data.DataArray);

this works but the problem is the Date format.. if i use this directly on my client:

data = new google.visualization.arrayToDataTable([
                    ['date','New York','Austin','San Francisco','Country','Popularity'],
                    [new Date("8/6/1975"),2,3,15,'Germany',200],
                    [new Date("8/6/1980"),5,6,19,'Brazil',400],
                    [new Date("8/6/2000"),8,9,23,'United States',300],
                    [new Date("8/6/2010"),11,12,35,'RU',700]
                ]);

this works and it automaticly adds the type Date ans parses it to an Date Object.. this confuse me a little i thought the  arrayToDataTable Method can not use Date Objects? but it seems to work..

But my problem is how can in serialize the Date Object to json so that the arrayToDataTable Method pass this to an Date Object ?

Am Montag, 24. März 2014 17:39:44 UTC+1 schrieb cyb:

[asgallant]

I'm not familiar with the java library for the Visualization API, so I'm not the best person to ask for help with that.

[asgallant]

Well, it appears that the dev team has changed things in the arrayToDataTable method.  If you want to input dates with that, you can do so like this:

var data = google.visualization.arrayToDataTable([
    [{label: 'Date', type: 'date'}, 'Value'],
    ['Date(2013, 2, 3)', 5],
    ['Date(2013, 2, 4)',2],
    ['Date(2013, 2, 5)',6],
    ['Date(2013, 2, 6)',7]
]);

The date string format is the same as the DataTable constructor, you just need to specify in the header row that the column is a "date" type.

[cyb]

Hi,

ok i use now the java DataTable there i can set a Pattern value "pattern: yyyDDmm" for date parsing this works..


but how can i parse this:

New York

63 $

58.3 $

53.59 $

70.2 $

how must the pattern string looks like that this values are parsed to a DataTable? 

Am Montag, 24. März 2014 17:39:44 UTC+1 schrieb cyb:

[Andrew Gallant]

That would be a number format like this: "#.## $"

[cyb]

Hi,

i have tried this in googlee playground with a "Stepped Area Chart" but this will not work:

function drawVisualization() {
  var data = google.visualization.arrayToDataTable([
    ['Director (Year)',  {label: "Rotten", type: "number", pattern: "#.## $"}, 'IMDB'],
    ['Alfred Hitchcock (1935)', 8.4 $,         7.9],
    ['Ralph Thomas (1959)',     6.9 $,         6.5],
    ['Don Sharp (1978)',        6.5 $,         6.4],
    ['James Hawes (2008)',      4.4 $,         6.2]
  ]);

  var options = {
    width: 600, height: 400,
    title: 'The decline of \'The 39 Steps\'',
    vAxis: {title: 'Accumulated Rating'},
    isStacked: true
  };

  var chart = new google.visualization.SteppedAreaChart(document.getElementById('visualization'));
  chart.draw(data, options);
}

http://code.google.com/apis/ajax/playground/#stepped_area_chart

Am Montag, 24. März 2014 17:39:44 UTC+1 schrieb cyb:

[Andrew Gallant]

Setting the "pattern" property of the column does not do anything.  Also, this will throw a syntax error:

['Alfred Hitchcock (1935)', 8.4 $, 7.9]

You need to remove the $.

To format the data, use a NumberFormatter:

var formatter = new google.visualization.NumberFormat({pattern: '#.## $'});

formatter.format(data, 1);

[cyb]

Hi,

hm the java library did set the pattern property in the column, why did he do that if this has no effect ?
If i define something like this in the java Library as number "60.3 $" it deletes the $ and adds the defined pattern property to the column..

[Andrew Gallant]

I don't know why the java library sets the pattern property, all I know is that setting pattern has no effect on anything.