neoSql
Perform complex SQL queries on .xlsx and .csv documents. Read only. No server side code but Web Server is mandatory to avoid CORS.
Based on:
Sample App:
https://visualneo.com/tutorials/neosql
Description
The neoSql plugin for VisualNEO Web enables applications to perform SQL-like queries on data within .xlsx (Excel) and .csv files, making it easy to manipulate and retrieve data locally without server-side code. However, it requires a web server to avoid CORS issues, as the operations are read-only. Here’s a breakdown of its functionalities and typical applications:
Querying Excel Files: The neoSqlExcel command allows users to execute SQL queries on Excel files, filtering data by specific conditions or retrieving entire sheets.
- Command: neoSqlExcel
- Syntax: neoSqlExcel "fileName" "sheetName" "query" objVar callBackSub
- fileName: Name of the .xlsx file (e.g., data.xlsx).
- sheetName: The specific sheet within the Excel file to query.
- query: SQL-like query string (e.g., "SELECT * FROM ? WHERE age > 20").
- objVar: Variable to store the query results.
- callBackSub: Optional callback subroutine to handle results.
Example:
neoSqlExcel "data.xlsx" "Sheet1" "SELECT * FROM ? WHERE age > 20" "[resultData]" "onQueryComplete"
This command retrieves rows from Sheet1 in data.xlsx where the age is over 20 and stores the data in resultData.
Querying CSV Files: The neoSqlCsv command enables similar SQL-like queries on .csv files, making it easy to work with structured data.
- Command: neoSqlCsv
- Syntax: neoSqlCsv "fileName" "query" "separator" objVar callBackSub
- fileName: Name of the .csv file (e.g., data.csv).
- query: SQL-like query (e.g., "SELECT * FROM ?").
- separator: Character that separates fields (e.g., "," for comma-separated values).
- objVar: Variable to store results.
- callBackSub: Optional callback for result handling.
Example:
neoSqlCsv "data.csv" "SELECT * FROM ? WHERE status = 'active'" "," "[activeData]" "onDataLoaded"
This command retrieves active records from data.csv based on the status field.
Saving Data as Excel or CSV: The plugin also allows data to be saved into .xlsx or .csv formats for download:
- neoSqlSaveExcel: Saves a data object as an Excel file.
- Syntax: neoSqlSaveExcel "fileName" objVar
- neoSqlSaveCsv: Saves data in CSV format.
- Syntax: neoSqlSaveCsv "fileName" "separator" "quote" objVar
Example for Saving CSV:
neoSqlSaveCsv "output.csv" "," "'" "[exportedData]"
This saves exportedData as a CSV file with comma separators and single-quote string delimiters.
Practical Applications
The neoSql plugin is ideal for:
- Data Analysis: Querying structured data files for insights.
- Offline Data Management: Viewing or filtering local data without server access.
- Data Export Tools: Exporting query results to files for reporting or sharing.
By allowing SQL-like operations on local data files, the neoSql plugin greatly enhances the data processing and export capabilities of VisualNEO Web applications.
neoSqlExcel
|
Purpose: |
Perfom a query in an Excel spreadsheet |
|
Category: |
neoSql |
|
Syntax: |
neoSqlExcel "fileName" "sheetName" ""query" objVar callBackSub
fileName File name (ie: mydoc.xlsx) sheetName Sheet name "query Query (ie: ""select * from ? where age > 20"")" objVar Object variable to strore results callBackSub Callback subroutine (optional) |
neoSqlSaveExcel
|
Purpose: |
Save a variable data object as .xlsx file |
|
Category: |
neoSql |
|
Syntax: |
neoSqlSaveExcel "fileName" objVar
fileName File name (ie: mydoc.xlsx) objVar Data object variable |
neoSqlCsv
|
Purpose: |
Perfom a query in a CSV data file |
|
Category: |
neoSql |
|
Syntax: |
neoSqlCsv "fileName" ""query" ""separator" objVar callBackSub
fileName File name (ie: mydoc.xlsx) "query Query (ie: ""select * from ? where age > 20"")" "separator Field separator character (ie: "","")" objVar Object variable to strore results callBackSub Callback subroutine (optional) |
neoSqlSaveCsv
|
Purpose: |
Save a variable data object as .csv file |
|
Category: |
neoSql |
|
Syntax: |
neoSqlSaveCsv "fileName" ""separator" ""quote" objVar
fileName File name (ie: mydoc.csv) "separator Field separator character (ie: "","")" "quote String used to quote strings (ie: "" ' "")" objVar Data object variable |
neoSqlSaveHtml
|
Purpose: |
Save a variable data object into a Container object as HTML |
|
Category: |
neoSql |
|
Syntax: |
neoSqlSaveHtml "containerId" objVar
containerId Select a Container object objVar Data object variable |
neoSqlGSheets
|
Purpose: |
Perfom a query in a Google Sheets published spreadsheet. Web server NOT necessary. IMPORTANT: Sheet must be published to allow neoSql access (File >Publish) |
|
Category: |
neoSql |
|
Syntax: |
neoSqlGSheets "fileURL" ""query" objVar callBackSub
fileURL Google Sheet URL "query Query (ie: ""select * from ? where age > 20"")" objVar Object variable to strore results callBackSub Callback subroutine (optional) |
neoSqlJson
|
Purpose: |
Perfom a query in a JSON data vaiable |
|
Category: |
neoSql |
|
Syntax: |
neoSqlJson jsonvar ""query" objVar callBackSub
jsonvar Variable with JSON data "query Query (ie: ""select * from ? where age > 20"")" objVar Object variable to strore results callBackSub Callback subroutine (optional) |
neoSqlJsonToCsv
|
Purpose: |
Converts JSON data variable into CSV data variable |
|
Category: |
neoSql |
|
Syntax: |
neoSqlJsonToCsv jsonVar csvVar ""separator"
jsonVar JSON data variable csvVar Variable to store result (in CSV format) "separator Field separator character (ie: "","")" |
neoSqlcreateDB
|
Purpose: |
Create a new in-memory database with a specified name |
|
Category: |
neoSql |
|
Syntax: |
neoSqlcreateDB "dbName"
dbName Database name |
neoSqlexecuteSqlInDB
|
Purpose: |
Execute an SQL query on a specified in-memory database |
|
Category: |
neoSql |
|
Syntax: |
neoSqlexecuteSqlInDB " dbName"
dbName Database name sqlQuery |
Created with the Personal Edition of HelpNDoc: Transform Your Word Doc into a Professional-Quality eBook with HelpNDoc