alasql
AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
AlaSQL - JavaScript SQL library
I want to export table data in csv format in my app. I have used Alasql library with XLSX.js. It works fine with all modern browsers(Chrome, Firefox ..) not on Safari.
Source: (StackOverflow)
I'm exporting some tables to excel, using angular, alasql and xlsx. I'm using it as follows:
var options = {
headers: true,
sheetid: 'users',
columns: [{
columnid: 'a',
title: 'Username'
}, {
columnid: 'b',
title: 'First name'
}, {
columnid: 'c',
title: 'Last name'
}]
};
alasql('SELECT * INTO XLSX("test.xlsx", ?) FROM ?', [options, $scope.users]);
I was expecting the columnns option to customize my table headers. But it ain't doing it.
Any clue why?
Source: (StackOverflow)
I am trying to make a simple app to analyze how I spend my time. To do this I want to make a SQL database using AlaSQL with the following schema:
id - some unique value
date - a date field
start_time - a time field
end_time - a time field
task - a string
category - a string that groups tasks
I am not quite sure how to make the date and time fields... can anyone help me with this?
Source: (StackOverflow)
I want to develop an offline application in which the data has to be retrieved from a dump ( .csv or,.xslx ) ..
1.Can i use alasql framework in my cordova application ?
2.if yes, What is the procedure... Will this application work on Android and Ios too?
3.What is the drawback of alasql ?
4.Please provide me an example ..Thanks in advance..
how can i extract file and use only particular cell value in my code?
Source: (StackOverflow)
I am newest in JS and I can not reuse variable from alasql.
when I run my code i have "unidifined"
var dataSource = alasql('SELECT AGENT_NAME, count(*) FROM XLSX("export.xlsx",{headers:true}) GROUP BY AGENT_NAME')
console.log(dataSource)
but when i run
var dataSource = alasql('SELECT AGENT_NAME, count(*) FROM XLSX("export.xlsx",{headers:true}) GROUP BY AGENT_NAME',[],
function (data) { console.log(data)})
everything is fine
Source: (StackOverflow)
The following program uses a static JSON object as its data source. I just want to replace the JSON Object with an excel file. It seems to be possible using SheetJS library but I cannot find a working sample that uses a file link instead of a File upload mechanism. How to replace the JSON object with an excel file as the data source in the following code and pull data using SheetJS library?
google.load('visualization', '1', {'packages':['table']});
var SiteData = SiteInfo();
var map;
function initialize()
{
map = new google.maps.Map(document.getElementById('map_canvas'), {
center: new google.maps.LatLng(55.7200,12.5700),
zoom: 2,
mapTypeControl: false,
streetViewControl: false,
mapTypeId: google.maps.MapTypeId.ROADMAP
});
for(m=0;m<SiteData.length;m++)
{
var image;
if(SiteData[m].Connection=="Sitetype1")
{
image = "http://labs.google.com/ridefinder/images/mm_20_white.png";
}
else if(SiteData[m].Connection=="sitetype2")
{
image = "http://labs.google.com/ridefinder/images/mm_20_black.png";
}
else
{
image = "http://labs.google.com/ridefinder/images/mm_20_purple.png";
}
setmarkers(map,SiteData[m].Latitude,SiteData[m].Longitude,image)
}
}
function setmarkers(map,lat,lon,image)
{
var latlon = new google.maps.LatLng(lat,lon);
var marker = new google.maps.Marker({map:map,position:latlon,icon:image});
}
function Changesite(sc)
{
var mpls = Outage();
for(var i=0,numSite = SiteData.length;i<numSite;i++)
{
if(SiteData[i].Sitecode==sc)
{
var data = new google.visualization.DataTable();
data.addColumn('String', 'sitecode');
data.addColumn('String', SiteData[i].Sitecode);
data.addRows([
['Connection', SiteData[i].Connection],
['Bandwidth', SiteData[i].Bandwidth],
['Address', SiteData[i].Address],
['Country', SiteData[i].Country],
]);
var chart = new google.visualization.Table
(document.getElementById('chart'));
var options = {
'title': SiteData[i].Sitecode+ ' ',
};
chart.draw(data, options);
}
}
}
function SiteInfo()
{
var Siteinfo =
[{"Connection":"Direct","Sitecode":"site1","Address":"Usa","Bandwidth":"6
Mbps","Country":"USA","Latency":"44 ms","Latitude":44,"Longitude":34,"Item
Type":"Item"}];
return Siteinfo;
}
function Outage()
{
var Outage_Data= [{"COUNTRY":"USA ","SITECODE":"site1","Outage
":"Issue1","DATE ":"4/1/2015"}];
return Outage_Data;
}
Source: (StackOverflow)
The plan is to use alaSQL to extract data from an excel spread sheet and use the resultant array as the source for a Google chart. The problem is that I can't get the results of the alaSQL call to properly populate a 2-D array that Google the charts api requires. it does not error out, but produces no results.
Here is the code:
var data_cities = [];
data_cities = new google.visualization.DataTable();
data_cities.addColumn('string', 'City');
data_cities.addColumn('number', 'Population');
data_cities.addRows(3);
var row_Counter = 0;
alasql('select * from xlsx("cities.xlsx", {headers:true, sheetid:"Cities", range:"A1:B4"})', //case senstitive on sheet, column name and value
[], function (xlData) {
$.each(xlData, function (key, val) {
//alert(key + " : " + val);
items.push("<li>City: " + this['City'] + "</li>" + "<li>Pop: " + this['Population'] + "</li>");
data_cities.setCell(row_Counter, 0, val.City);
data_cities.setCell(row_Counter, 1, val.Population);
row_Counter = row_Counter + 1;
});
$('<ul/>', {
html: items.join('')
}).appendTo('div#divgetJSON');
});
var chart_cities = new google.visualization.ColumnChart(document.getElementById('chart_div_cities'));
drawChart_Cities();
function drawChart_Cities() {
// Set chart options
var options_cities = {
'title': 'Populations of Major Cities',
'width': 1800,
'height': 400,
vAxis: { title: "Population", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
hAxis: { title: "City", titleTextStyle: { fontSize: 16, bold: true, italic: false } },
seriesType: "bars",
animation: {
duration: 800,
easing: 'inout',
},
allowHtml: true,
bar: { groupWidth: "65%" },
legend: { position: "bottom" },
is3D: true,
};
chart_cities.draw(data_cities, options_cities);
}
Source: (StackOverflow)
Due to security concerns, I have to port all data from a fusion table to an excel file and still retain the fusion tables functionality which is used as a source for google maps. Now the issue i am facing is i came across alasql. I thought of using this to perform my task. But i am unable to find any documented examples of alasql which for excel and google maps using javascript only.
Can someone point me to an apt example that i can refer or any documentation on alasql with excel and gmaps. The below code is what i have to port from fusion table to excel file given by a link.
google.load('visualization', '1', {'packages':['table']});
function initialize() {
var map = new google.maps.Map(document.getElementById('map_canvas'), {
center: new google.maps.LatLng(55.7200,12.5700),
zoom: 2,
minZoom: 1,
maxZoom: 4,
mapTypeControl: false,
streetViewControl: false,
mapTypeId: google.maps.MapTypeId.ROADMAP
});
var layer = new google.maps.FusionTablesLayer({
query: {
select: 'Address',
from: '1234324235435435' // fusion table id here
},
suppressInfoWindows: true
});
layer.setMap(map);
google.maps.event.addListener(layer, 'click', function(e) {
var data = new google.visualization.DataTable();
data.addColumn('string', 'header1:');
data.addColumn('string', e.value1);
data.addRows([
['header2', e.value2],
['header3', e.value3],
['header4', e.value4],
['header5', e.value5],
['header6', e.value6],
['header7', e.value7],
['header8', e.value8],
['header9', e.value9],
['header10', e.value10],
]);
var chart = new google.visualization.Table(document.getElementById('chart'));
var options = {
'title': e.row['header'].value + ' ',
};
chart.draw(data, options);
});
}
function changeData(Sitecode) {
var whereClause = "";
if(Sitecode) {
whereClause = " WHERE 'Sitecode' = '" + Sitecode + "'"+"order by StartTime DESC limit 3";
}
var queryText = encodeURIComponent("SELECT 'Sitecode', 'IncidentID', 'IncidentReport', 'Resolved', 'StartTime' FROM 12345678" + whereClause);
var query = new google.visualization.Query('http://www.google.com/fusiontables/gvizdata?tq=' + queryText);
query.send(getData);
}
function getData(response) {
var table = new google.visualization.Table(document.getElementById('visualization'));
table.draw(response.getDataTable());
}
function UpgradeData(Sitecode) {
var whereClause = "";
if(Sitecode) {
whereClause = " WHERE 'Sitecode' = '" + Sitecode + "'";
}
var queryText = encodeURIComponent("SELECT 'Sitecode', 'curver' as Current_Version, 'upon' as Upgraded_On, 'upnext' as Next_Upgrade, 'upnotes' as Upgrade_Notes FROM 123456789" + whereClause);
var query = new google.visualization.Query('http://www.google.com/fusiontables/gvizdata?tq=' + queryText);
query.send(getdata);
}
function getdata(response) {
var table = new google.visualization.Table(document.getElementById('visualization1'));
table.draw(response.getDataTable());
}
Source: (StackOverflow)
i am trying to add two functions to my project. One is to export the grid data as its views to an excel (i am using kendo-grid and the grid data has been formatted from the UI side) and the second one is to print the grid data with the grid lines.
i have added both, but i am having two problems.
1. Exporting to excel with the numbers formatted. i used this example http://jsfiddle.net/agershun/00nfeq12/
alasql('SELECT x [No.],Type,Amount INTO XLSX("Type.xlsx",{headers:true}) FROM ?',[ $scope.data.Data]);
};
$scope.data.Data is a result of the api. So it contains the actual data. But in my grid, i have added custom formatting.
- printing with the grid lines.
I am using this example.
Print a div using javascript in angularJS single page aplication
but the print just shows the data, no border lines and these are needed.
How do i solve this please?
thanks
Source: (StackOverflow)
I have constructed an alasql query with a array as the data source..
the columns in my table are( with 1000+ rows):
City, TravelDate, Q1, Q2, Q3, Q4
1, 2015-05-31, 6, 6, 5, 5
My requirement is to find the averages for the questions (under 6) grouped by City and Year and month.
PLUS ONLY FOR A CERTAIN DATE RANGE
[] contains the index number of the columns in array
so far my query :
var res = alasql('SELECT [11] as City, [0], AVG([1]), AVG([2]),AVG([3]),AVG([2]) FROM ?D WHERE [1] <= 5 AND [2]<= 5 AND [3]<= 5 AND [4]<= 5 GROUP BY [11],[0]'[data]);
The query above works however:
It does not work if I place WHERE [0] >= '2014-01-01' AND [0] <= '2015-05-31'
Notes:
I have tried using double quotes "" but still does not work
Also the date column in the data array is constructed using JavaScript date method in a for loop with new Date()
The rows contain date results including any day so there can be multiple rows with the same date for given month and year.
Does anyone have an idea how to contruct my query so that my output is like
City,TravelDate,AvgQ1,AvgQ2,AvgQ3,AvgQ4
So in other words grouped by City and (Year&Month) (rather than City,Year,Month) within my date range requirement
Source: (StackOverflow)
I was trying to implement a project using alasql in an IE8 environment but keep getting the same error. The alasql function isnt executing properly.
I get the following error-
Expected identifier
File: alasql.min.js, Line: 7, Column: 9848
i get this error even when trying to run the most basic code possible which is given below -
var res = alasql('select * from xlsx("xlsxURL")',[],function(res){
console.log(res[1])
});
Is there any way to make it IE8 compliant ?
Source: (StackOverflow)
I am trying to write a select statement that includes a where clause that will only return records from the selected date. My table utilizes the Javascript object version of dates. I've tried things like this without success:
select * from my mydb.events where date = some-date-object;
Any help would be appreciated.
Source: (StackOverflow)
I'm using this function to sort and export data to excel
window.exportData = function () {
data.sort(function(a, b){
return a.destino - b.destino;
});
alasql("SELECT * INTO XLSX('cities.xlsx',{headers:true}) FROM ? ", [data]);
it works well. I need to change the name 'cities.xlsx' for a name like, for example,
var filename = 'cities'+variable+'.xlsx';
and obtain
alasql("SELECT * INTO XLSX("filename",{headers:true}) FROM ? ",[data]);
in order to avoid confusions with saved files in excel.
Thank you in advance.
Source: (StackOverflow)
If I run the "app" in Chrome on my pc as http, everything works as expected with alasql but after the app is built by PhoneGap Build the alasql functions are not available.
All javascript is called after 'deviceready' in the app (but in the $(document).ready() in the Chrome test).
index.html links to these scripts (among others):
<script type="text/javascript" src="cordova.js"></script>
<script src="js/jquery-2.1.4.min.js" type="text/javascript"></script>
<script src="dist/alasql.min.js" type="text/javascript"></script>
...
<script type="text/javascript" src="js/index.js"></script>
And in index.js I have a function that is called from ondeviceready (in index.html):
alasql('CREATE localStorage DATABASE IF NOT EXISTS db'); //THIS IS WERE THE ERROR OCCURS
alasql('ATTACH localStorage DATABASE db');
alasql('USE db');
I've reinstalled the app but no success. Still getting the same error. Everything else works fine. Any ideas?
Source: (StackOverflow)
I'm running this code in an Angular service, immediately upon loading the page. The controller $scope is passed as an argument to the function this extract belong to. The function is a $q promise.
I am not able to figure out how can I let the controller know that scope.req.rows has been updated. If I add scope.$apply() right after it, I run into a running digest phase. If I use the $q resolve function, it returns and no more loop results are returned. scope.$evalAsync() and $timeout seem to have no effect (at least without setting a timeout > 0). Same goes for scope.$watch.
How can I let the controller know that values were updated?
for (var page = 0; page < nbPages; page++) {
(function (pageNum) {
that.get(url,
where,
res.pageSize * page,
res.pageSize)
.then(function Success(data) {
$log.info('Result of page ' + pageNum + ' received');
for (row in data) {
scope.req.rows++;
}
}).catch(function chunkFail(err) {
reject(err);
});
})(page);
Source: (StackOverflow)