Introduction
This page was written because Joe asked and later Ale asked a slightly more complex question in the API user-supported group how to use data from a CSV file.
How to import data from a CSV doesn't seem well documented in the API apart from some brief notes at Reading CSV Files.
Stack Overflow has years' old posts involving external libraries such as jquery.csv.js in posts such as How to use Google Chart with data from a csv, Create Google Chart from CSV input, and How to use a csv file with the google chart API?
The Charts API documentation has Using an External Data Store which talks about using CsvDataSourceServlets. Also close is the thread Using CSV file to Populate DataTable in the API user-supported group.
CSV files are notorious for giving errors when importing into various programs so they can be processed. This happens because although they are simple comma separated value text files, a comma in the wrong place, mismatched data type, a missing or empty column will stop them being imported. There are plain text files around that claim to be CSV but really are not.
Importing a CSV File
Joe wanted to know how to read a CSV and get all the data into a chart, the method is similar to the query used to get data from a Google Sheet that I use frequently.
Here a graph using data imported from a CSV file:
A div with the id of "joe-csv-div" was created in the HTML
This is the CSV file, which can also be found in the data folder:
Day,Status A,Status B,Status C
Monday,10,5,3
Tuesday,8,2,6
Wednesday,6,4,10
Thursday,12,8,4
Friday,4,12,2
Saturday,6,4,8
Sunday,10,6,4
This is the JavaScript that queries the CSV file and draws the chart:
<script type="text/javaScript">
// Load the Charts and the corechart package.
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawJoeChart);
function drawJoeChart() {
var queryOptions = {
// Define the CSV data columns
csvColumns: ['string', 'number', 'number', 'number'],
// This should be false if your CSV file doesn't have a header
csvHasHeader: true
}
// Create the query giving the path and name of the CSV file
var query = new google.visualization.Query('data/joe.csv', queryOptions);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var joedata = response.getDataTable();
var joechart = new google.visualization.ColumnChart(document.getElementById('joe-csv-div'));
joechart.draw(joedata);
}
</script>CSV Query
Ale wanted to know how to query a CSV file and only return certain columns and rows from it for a table. I thought a variation of the code I use to extract data from a Google Sheet would work, which is also outlined on Setting the Query from JavaScript, so I tried:
var queryOptions = {
// Define the CSV data columns
csvColumns: ['string', 'string', 'string', 'string', 'string'],
csvHasHeader: true,
sendMethod: 'auto'
}
// Create the query giving the path and name of the CSV file
var query = new google.visualization.Query('data/ale.csv', queryOptions);
query.setQuery("select reason, import where category='Fun' order by reason, import");
query.send(handleQueryAleQueryResponse);
. . .
. . .
This did not give an error, but produced the entire table. Reading through Data Queries in the API documentation I could see why:
If the Datasource does not support the query language, it will ignore the SQL query string, but still return a DataTable
Setting the Query in the Data Source URL says that the query can be added to the data URL if it is encoded, so I also tried the line:
var query = new google.visualization.Query("data/ale.csv/gviz/tq?tq=select%20reason%2C%20import%20where%20category%3D'Fun'%20order%20by%20reason%2C%20import", queryOptions);
Which gave the error message "Error in query: [404]". I tried several variations of the query, but could not get it to work at all. I know the query should work because I used Ale's data and the query in a Google Sheet (cell G1)
A Solution
The entire CSV table (ale.csv) can be imported, and a view can be created from that.
The entire CSV datatable:
The dataview created from the datatable:
This is the JavaScript that imports the CSV file and draws the above charts:
<script type="text/javaScript">
// Load the Charts and the table package.
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryOptions = {
// Define the CSV data columns
csvColumns: ['number', 'string', 'string', 'string', 'string'],
// This should be false if your CSV file doesn't have a header
csvHasHeader: true
}
// Create the query giving the path and name of the CSV file
var query = new google.visualization.Query('data/ale-b.csv', queryOptions);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var aledata = response.getDataTable();
var alechart = new google.visualization.Table(document.getElementById('ale-table-div'));
alechart.draw(aledata);
// Create a dataview from the datatable and display that
var aleview = new google.visualization.DataView(aledata);
aleview.setRows(aleview.getFilteredRows([{column: 4, value: 'Fun'}]));
aleview.setColumns([2,0,4]);
var alechart = new google.visualization.Table(document.getElementById('ale-view-div'));
alechart.draw(aleview);
}
</script>A Second Solution
Another solution is to import the CSV into Google Sheets and use that as a data source. This is pretty well documented.
This is the JavaScript that reads the Google Sheet and draws the above table:
<script type="text/javaScript">
// Load the Charts and the table package.
google.charts.load('current', {'packages':['table']});
google.charts.setOnLoadCallback(drawAleGSChart);
function drawAleGSChart() {
var queryString = encodeURIComponent('SELECT C,A,E where E="Fun" ORDER BY C,A');
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1tswaWUAbeBijHq4o505w0h7TmbD-qGhR3jBactcbGq0/gviz/tq?gid=1011451446&headers=1&tq=' + queryString);
query.send(handleQueryAleGSResponse);
}
function handleQueryAleGSResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var aleGSData = response.getDataTable();
var aleGSChart = new google.visualization.Table(document.getElementById('ale-GS-div'));
aleGSChart.draw(aleGSData);
}
</script>Dates and Times
The preferred date format in CSV files is yyyy-MM-dd (for example 2023-05-06) and for time is hh:mm:ss. (for example 11:29:32.352.)
If you try to use a CSV file with a date format using for example csvColumns: ['date', 'number'], in the calling query that the API does not understand such as dd-MM-yyyy (for example - 05-06-2023) you will get a warning saying that ".getTimezoneOffset is not a function".
Wrong date/time format error message
One method to correct this is to import the date column as a string using for example csvColumns: ['string', 'number'], but this can cause problems depending on what you want to do with the API because the date is now discrete data instead of a real date series.
I tried some common date formats and found that the CSV will also import the date as a date so long as it is in the order month, day, year. This can be in any accepted date format, month as short text, month as long text, leading zeros in front of short numbers, no leading zeros in front of short numbers, two figure or 4 figure year, and with any separator such as /, - or a space.
The API will also accept the date in the format day, month and year; and year, day, month but only if the month is text, such as sep or september. It will not accept it if the month is a number such as 9 or 09.
For more information on dates and times see the API documentation and on Mozilla Web Docs.