External .CSV Chart/Table Not Rendering Help.

[paulmo]

Have implemented according to instructions. CSV contains text and
numbers. My page is below, but first, if it helps, this is how
the .csv renders into JSON (but I'm not using JSON in external csv
file example). Thanks in advance for help.
[{"C":"C","Morning Report":"Report for 12\/7\/2011"},{"C":"C","Morning
Report":"Report Generated 12\/07\/2011 13:18:10 GMT"},
{"C":"H","Morning Report":"DESCRIPTION"},{"C":"H","Morning Report":""},
{"C":"G","Morning Report":"Report Type"},{"C":"D","Morning
Report":"Morning Report"},{"C":"H","Morning Report":"DATE","":"TIME"},
{"C":"H","Morning Report":"","":""},{"C":"G","Morning Report":"1.
Report Date"},{"C":"D","Morning Report":"Wednesday, Dec 7
2011","":"08:18:10 EST"},{"C":"H","Morning
Report":"DESCRIPTION","":"VALUE"},{"C":"H","Morning
Report":"","":"(MW)"},{"C":"G","Morning Report":"2. Peak Load"},
{"C":"D","Morning Report":"Tuesday, Dec 6 2011 hour ending
18","":"17755"},{"C":"G","Morning Report":"3. Operable Capacity
Analysis"},{"C":"D","Morning Report":"A. Capacity Supply Obligation
(CSO)","":"30957"},{"C":"D","Morning Report":"B. Capacity Additions
EcoMax Bid > CSO","":"2272"},{"C":"D","Morning Report":"C. Pre-OP4
Dispatchable Loads","":"0"},{"C":"D","Morning Report":"D. Generation
Outages and Reductions","":"4474"},{"C":"D","Morning Report":"E.
Uncommitted Available Generation (non fast start)","":"9070"},
{"C":"G","Morning Report":"F. Capacity Deliveries: Net Purchases = (-)
Net Sales = (+)"},{"C":"D","Morning Report":"NYISO AC Ties","":"167"},
{"C":"D","Morning Report":"NYISO NNC","":"200"},{"C":"D","Morning
Report":"NYISO CSC","":"330"},{"C":"D","Morning Report":"NB","":"222"},
{"C":"D","Morning Report":"Phase 2","":"-1225"},{"C":"D","Morning
Report":"Highgate","":"-218"},{"C":"D","Morning Report":"Net
Deliveries","":"-524"},{"C":"D","Morning Report":"G. Total Available
Capacity (A+B+C-D-E-F)","":"20209"},{"C":"D","Morning Report":"H. Peak
Load Forecast For Hour 18","":"17900"},{"C":"D","Morning Report":"I.
Total Operating Reserve Requirement","":"1863"},{"C":"D","Morning
Report":"J. Capacity Required","":"19763"},{"C":"D","Morning
Report":"K. Surplus = (+) Deficiency = (-) (G - J)","":"446"},
{"C":"D","Morning Report":"L. Replacement Reserve Requirement","":"0"},
{"C":"D","Morning Report":"M. Excess Commitment Surplus = (+)
Deficiency = (-) (K - L)","":"446"},{"C":"G","Morning Report":"4.
Largest First Contingency"},{"C":"D","Morning
Report":"____","":"1245"},{"C":"G","Morning Report":"5. Annual
Maintenance Schedule (A.M.S)"},{"C":"D","Morning Report":"Peak Load
Exposure","":"20393"},{"C":"G","Morning Report":"6. Reserve Summary"},
{"C":"D","Morning Report":"Ten Minute Reserve Requirement:","":"1245"},
{"C":"D","Morning Report":"Ten Minute Reserve Estimate:","":"1245"},
{"C":"D","Morning Report":"Thirty Minute Reserve
Requirement:","":"618"},{"C":"D","Morning Report":"Thirty Minute
Reserve Estimate:","":"1064"},{"C":"D","Morning Report":"Expected ICU
Operation","":"0"},{"C":"D","Morning Report":"Expected Actions of OP
4:","":"0"},{"C":"D","Morning Report":"Additional Capacity Available
from OP 4 Actions:","":"0"},{"C":"H","Morning
Report":"DESCRIPTION","":"SCHEDULED CONTRACT"},{"C":"H","Morning
Report":"(MW)","":"(MW)"},{"C":"G","Morning Report":"7. Interchange
Summary"},{"C":"D","Morning Report":"NYISO AC Ties","":"167"},
{"C":"D","Morning Report":"NYISO NNC","":"200"},{"C":"D","Morning
Report":"NYISO CSC","":"330"},{"C":"D","Morning Report":"NB","":"222"},
{"C":"D","Morning Report":"Phase 2","":"-1225"},{"C":"D","Morning
Report":"Highgate","":"-218"},{"C":"H","Morning
Report":"CITY","":"HIGH TEMP"},{"C":"H","Morning Report":"","":"(F)"},
{"C":"G","Morning Report":"8. Weather Forecast Summary for the Peak
Hour"},{"C":"D","Morning Report":"Boston","":"45"},{"C":"D","Morning
Report":"Hartford","":"46"},{"C":"H","Morning
Report":"DESCRIPTION","":"VALUE"},{"C":"H","Morning Report":"","":""},
{"C":"G","Morning Report":"9. SAR Available"},{"C":"D","Morning
Report":"NYISO","":"Yes"},{"C":"H","Morning
Report":"DESCRIPTION","":"VALUE"},{"C":"H","Morning
Report":"","":"(MW)"},{"C":"G","Morning Report":"10. Capacity of Non-
Commercial Units"},{"C":"D","Morning Report":"Total Available","":"0"},
{"C":"G","Morning Report":"11. Units Committed to Meet Minimum
Operating Reserve and Replacement Reserve Requirements"},
{"C":"D","Morning Report":"0 Units","":"0"},{"C":"H","Morning
Report":"DESCRIPTION","":"VALUE"},{"C":"H","Morning Report":"","":""},
{"C":"G","Morning Report":"12. Solar Magnetic Disturbance Activity"},
{"C":"D","Morning Report":"Forecast:","":""},{"C":"D","Morning
Report":"Alert:","":""},{"C":"D","Morning Report":"Intensity:","":""},
{"C":"D","Morning Report":"Observed Activity:","":""},
{"C":"D","Morning Report":"Actions Taken or Planned by ISO-NE:","":""},
{"C":"D","Morning Report":"Actions Taken or Planned by Other Control
Areas:","":""},{"C":"T","Morning Report":"44 Lines"}]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/
jsapi"></script>
<script type="text/javascript" src="jquery-1.7.1.min.js"></script>
<script type="text/javascript">

// Load the Visualization API and the piechart package.
google.load('visualization', '1', {'packages':['corechart']});

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

query = new google.visualization.Query('csv?url=http://www.iso-ne.com/
sys_ops/csvdocs/morning_report2011-12-07-08-18.csv');

var chart = new
google.visualization.OrgChart(document.getElementById('csv_div'));

</script>
</head>

<body>

<script type="text/javascript">
public class CsvDataSourceServlet extends DataSourceServlet {

/**
* Log.
*/
private static final Log log =
LogFactory.getLog(CsvDataSourceServlet.class.getName());

/**
* The name of the parameter that contains the url of the CSV to
load.
*/
private static final String URL_PARAM_NAME = "http://www.iso-ne.com/
sys_ops/csvdocs/morning_report2011-12-07-08-18.csv";

/**
* Generates the data table.
* This servlet assumes a special parameter that contains the CSV
URL from which to load
* the data.
*/
@Override
public DataTable generateDataTable(Query query, HttpServletRequest
request)
throws DataSourceException {
String url = request.getParameter(URL_PARAM_NAME);
if (StringUtils.isEmpty(url)) {
log.error("url parameter not provided.");
throw new DataSourceException(ReasonType.INVALID_REQUEST, "url
parameter not provided");
}

Reader reader;
try {
reader = new BufferedReader(new InputStreamReader(new
URL(url).openStream()));
} catch (MalformedURLException e) {
log.error("url is malformed: " + url);
throw new DataSourceException(ReasonType.INVALID_REQUEST, "url
is malformed: " + url);
} catch (IOException e) {
log.error("Couldn't read from url: " + url, e);
throw new DataSourceException(ReasonType.INVALID_REQUEST,
"Couldn't read from url: " + url);
}
DataTable dataTable = null;
ULocale requestLocale =
DataSourceHelper.getLocaleFromRequest(request);
try {
// Note: We assume that all the columns in the CSV file are text
columns. In cases where the
// column types are known in advance, this behavior can be
overridden by passing a list of
// ColumnDescription objects specifying the column types. See
CsvDataSourceHelper.read() for
// more details.
dataTable = CsvDataSourceHelper.read(reader, null, true,
requestLocale);
} catch (IOException e) {
log.error("Couldn't read from url: " + url, e);
throw new DataSourceException(ReasonType.INVALID_REQUEST,
"Couldn't read from url: " + url);
}
return dataTable;
}
}

</script>

<!--Div that will hold the pie chart-->
<div id="csv_div"></div>

</body>
</html>

[Roni Biran]

Hi paulmo,

Sadly you did not follow the exact instructions :-(

1. The code you copied is a servlet code (i.e. runs on the server. this is not a client side JavaScript).

2. Your script should hold a function named "drawChart" that will be called after the page load is completed (google.setOnLoadCallback). That function will call another function with the data that will eventually draw the chart.

3. Your CSV in inaccessible here. Can you send a sample from the CSV itself. If I were you, I would use the Google spreadsheet as an example.

After saying all that (and assuming that your CSV works), your script should look something like this:

<script type="text/javascript">

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

google.setOnLoadCallback(drawChart);

function drawChart() {   

var query = new google.visualization.Query('csv?url=http://www.iso-ne.com/sys_ops/csvdocs/morning_report2011-12-07-08-18.csv');

query.setQuery('select dept, sum(salary) group by dept');

query.send(handleQueryResponse);  

}

function handleQueryResponse(response) {

if (response.isError()) {

alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

return;

}

var data = response.getDataTable();

var chart = new google.visualization.OrgChart(document.getElementById('csv_div'));

chart.draw(data);

}

</script>

Best of luck,

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
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.

[Roni Biran]

You are correct, the query was taken from the sample code and is intended for demonstrating how to get the correct data for the chart.

Since you're not using a standard column definition, you'll have to select according to the column, meaning something like "select A,B,C" etc.... although, from looking at your CSV data it looks quite messy.

If I got you correctly in your previous post, you want to create an organization chart. Thus, your data should be arranged in the form of three columns: Name, Manager name, and Tooltip. You can use the dataview model to rearrange your data prior to drawing.

On Thu, Dec 8, 2011 at 5:21 PM, paulmo <paul.m...@gmail.com> wrote:

thanks so much roni for getting me started! here is a sample
from .csv.:

"D","Additional Capacity Available from OP 4 Actions:","0"

"H","DESCRIPTION","TRANSFER LIMIT IN","TRANSFER LIMIT OUT","SCHEDULED
CONTRACT"
"H","(MW)","(MW)","(MW)"
"G","7. Interchange Summary"
"D","NYISO AC Ties",-300,1200,286
"D","NYISO NNC",-200,200,200
"D","NYISO CSC",-346,330,330
"D","NB",-800,550,-119
"D","Phase 2",-2000,1200,-1424
"D","Highgate",-218,0,-218

with your script and correct .csv link, my page is not rendering. I'm
guessing it has to do with this section?:

query.setQuery('select dept, sum(salary) group by dept');

paul

On Dec 8, 3:14 am, Roni Biran <roni.bi...@gmail.com> wrote:
> Hi paulmo,
>
> Sadly you did not follow the exact instructions :-(
>
> 1. The code you copied is a servlet code (i.e. runs on the server. this is
> not a client side JavaScript).
> 2. Your script should hold a function named "drawChart" that will be called
> after the page load is completed (google.setOnLoadCallback). That function
> will call another function with the data that will eventually draw the
> chart.
> 3. Your CSV in inaccessible here. Can you send a sample from the CSV
> itself. If I were you, I would use the Google spreadsheet as an example.
>
> After saying all that (and assuming that your CSV works), your script
> should look something like this:
>
> <script type="text/javascript">
> google.load('visualization', '1', {'packages':['corechart']});
> google.setOnLoadCallback(drawChart);
>
> function drawChart() {

> var query = new google.visualization.Query('csv?url=http://www.iso-ne.com/sys_ops/csvdocs/morning_report2011-12-07-08-18....

> ...
>
> read more »

[paulmo]

thanks so much roni for getting me started! here is a sample
from .csv.:

"D","Additional Capacity Available from OP 4 Actions:","0"
"H","DESCRIPTION","TRANSFER LIMIT IN","TRANSFER LIMIT OUT","SCHEDULED
CONTRACT"
"H","(MW)","(MW)","(MW)"
"G","7. Interchange Summary"
"D","NYISO AC Ties",-300,1200,286
"D","NYISO NNC",-200,200,200
"D","NYISO CSC",-346,330,330
"D","NB",-800,550,-119
"D","Phase 2",-2000,1200,-1424
"D","Highgate",-218,0,-218

I have edited this section of your script to reflect data
(above)...getting blank page.

query.setQuery('select SCHEDULED CONTRACT, sum(MW) group by SCHEDULED
CONTRACT');

thanks in advance for help! paul

On Dec 8, 3:14 am, Roni Biran <roni.bi...@gmail.com> wrote:

> Hi paulmo,
>
> Sadly you did not follow the exact instructions :-(
>
> 1. The code you copied is a servlet code (i.e. runs on the server. this is
> not a client side JavaScript).
> 2. Your script should hold a function named "drawChart" that will be called
> after the page load is completed (google.setOnLoadCallback). That function
> will call another function with the data that will eventually draw the
> chart.
> 3. Your CSV in inaccessible here. Can you send a sample from the CSV
> itself. If I were you, I would use the Google spreadsheet as an example.
>
> After saying all that (and assuming that your CSV works), your script
> should look something like this:
>
> <script type="text/javascript">
> google.load('visualization', '1', {'packages':['corechart']});
> google.setOnLoadCallback(drawChart);
>
> function drawChart() {

> var query = new google.visualization.Query('csv?url=http://www.iso-ne.com/sys_ops/csvdocs/morning_report2011-12-07-08-18....

> ...
>
> read more »

[Roni Biran]

Hi Paul,

Try debugging your application and see what type of element you're getting. It looks to me as though you're not getting the proper data into the Query object. This might cause the empty page.

Maybe you're getting another JavaScript error that kills the entire application.... try debugging it.

If you're using Chrome/Firefox/IE, you can press F12 -> scripts and go on from there.

Roni

[paulmo]

hi Roni, debugging, 404 Not Found error here:
var query = new google.visualization.Query('csv?url=http://localhost/
test.csv');

But this is a correct file path. This is what the .csv looks like when
opened in browser into spreadsheet (gnumeric linux) from file path:
H (MW) (MW) (MW)
G 7. Interchange Summary
D NYISO AC Ties -300 1200 286
D NYISO NNC -200 200 200
D NYISO CSC -346 330 330
D NB -800 550 -119
D Phase 2 -2000 1200 -1424

thanks for suggestions.

query.setQuery('select Phase 2, sum(MW) group by Phase 2');

> ...
>
> read more »

[paulmo]

here is link to .csv data Roni (google spreadsheet). thanks in advance
for helping with query.
https://docs.google.com/spreadsheet/pub?key=0Aha5Qc1venTTdEVOOGhZRUlvMENZNzBxdGNadE9jWXc&output=html

> ...
>
> read more »

[Roni Biran]

Hi Paul,

I will look at it in an hour or so.

[Roni Biran]

Hi Paul and sorry for the delay,

Your script should look like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>
      Google Visualization API Sample
    </title>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>

    <script type="text/javascript">
      google.load('visualization', '1', {packages: ['corechart']});

    </script>
    <script type="text/javascript">
    var visualization;

    function drawVisualization() {

      var query = new google.visualization.Query(

          'https://docs.google.com/spreadsheet/tq?key=0Aha5Qc1venTTdEVOOGhZRUlvMENZNzBxdGNadE9jWXc&headers=0&transpose=0&gid=0&range=E3:E7&pub=1');

      
      query.send(handleQueryResponse);
    }
    
    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }
    
      var data = response.getDataTable();

      visualization = new google.visualization.ColumnChart(document.getElementById('visualization'));
      visualization.draw(data, null);
    }

    google.setOnLoadCallback(drawVisualization);
    </script>
  </head>
  <body style="font-family: Arial;border: 0 none;">
    <div id="visualization" style="height: 400px; width: 400px;"></div>
  </body>
</html>
​

Good luck,

[paulmo]

Thanks Roni, "google.visualization is undefined" here:
var query = new google.visualization.Query('https://docs.google.com/
spreadsheet/pub?
key=0Aha5Qc1venTTdEVOOGhZRUlvMENZNzBxdGNadE9jWXc&output=html');

I put the.csv in Google Spreadsheets to show you the
data...ultimately, I need to retrieve the updated .csv daily,
automatically, through a cron job. The output of the data will be
stored in MySQL. Will all this be possible doing this with
Spreadsheets, as opposed to JSON, etc?

Thanks again for support. Paul

On Dec 8, 2:13 pm, Roni Biran <roni.bi...@gmail.com> wrote:
> Hi Paul and sorry for the delay,
>
> Your script should look like this:
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
> <html xmlns="http://www.w3.org/1999/xhtml">
>   <head>
>     <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
>     <title>
>       Google Visualization API Sample
>     </title>
>     <script type="text/javascript" src="http://www.google.com/jsapi"></
> script>
>     <script type="text/javascript">
>       google.load('visualization', '1', {packages: ['corechart']});
>     </script>
>     <script type="text/javascript">
>     var visualization;
>
>     function drawVisualization() {
>       var query = new google.visualization.Query(

>           'https://docs.google.com/spreadsheet/tq?key=0Aha5Qc1venTTdEVOOGhZRUlvM...

> ');
>
>       query.send(handleQueryResponse);
>     }
>
>     function handleQueryResponse(response) {
>       if (response.isError()) {
>         alert('Error in query: ' + response.getMessage() + ' ' + response.
> getDetailedMessage());
>         return;
>       }
>
>       var data = response.getDataTable();
>       visualization = new google.visualization.ColumnChart(document.
> getElementById('visualization'));
>       visualization.draw(data, null);
>     }
>
>     google.setOnLoadCallback(drawVisualization);
>     </script>
>   </head>
>   <body style="font-family: Arial;border: 0 none;">
>     <div id="visualization" style="height: 400px; width: 400px;"></div>
>   </body>
> </html>
>
> Good luck,
>
>
>
>
>
>
>
> On Thu, Dec 8, 2011 at 6:35 PM, Roni Biran <roni.bi...@gmail.com> wrote:
> > Hi Paul,
>
> > I will look at it in an hour or so.
>

> > On 8 בדצמ 2011, at 18:29, paulmo <paul.moll...@gmail.com> wrote:
>
> > here is link to .csv data Roni (google spreadsheet). thanks in advance
> > for helping with query.
>

> >https://docs.google.com/spreadsheet/pub?key=0Aha5Qc1venTTdEVOOGhZRUlv...

> ...
>
> read more »

[Roni Biran]

Hi Paul,

If you're using a JOB to generate an output file specifically to the chart it's even simpler.

Look at the samples in this URL: http://code.google.com/apis/chart/interactive/docs/php_example.html

Make sure that your output (CSV) file looks like the sampleData.json file. and you'll do great.

Roni

> ...
>
> read more »