Altair slc native language5 odbc read write text files, summarize and looping with sqlite
Note: None of the process queries are supported by the slc 'proc sql' or sas 'proc sql'
Note: Character strings are defaulting to 1024 bytes, to fix this you need to define
the table attributes explicitly in sqlite. Macro utl_optlenpos will optimize the lengths of all variables.
Too long to post on a list, see github
https://github.com/rogerjdeangelis/utl-altair-slc-native-language5-odbc-read-write-text-files-summarize-and-looping-with-sqlite
Note: Note sqlite has csv read and write commands, however I want to present general txt processing in sql.
Keep in mind that I am demonstrating that the exact sqlite queries work in the 8 languages below.
In addition you can use the exact same queries in any language or operating system that supports odbc.
related repos (see for information of installing odbc and sqlite)
https://github.com/rogerjdeangelis/utl-altair-slc-sqlite-cheat-sheet
https://github.com/rogerjdeangelis/utl-altair-slc-language1-drop-down-to-open-source-spss-and-execute-postgresql-query
https://github.com/rogerjdeangelis/utl-altair-slc-language2-drop-down-to-open-source-matlab-and-execute-sqlite-with-extensions
https://github.com/rogerjdeangelis/utl-altair-slc-language3-proc-r-read-write-csv-files-and-summarize-and-looping-with-sqlite
https://github.com/rogerjdeangelis/utl-altair-slc-language4-proc-python-read-write-csv-files-and-summarize-and-looping-with-sqlite
https://github.com/rogerjdeangelis/utl-altair-slc-native-language5-odbc-read-write-text-files-summarize-and-looping-with-sqlite
PROBLEM
PROCESS
1 slc create sqlite table 'have' with 3 lines (sentences) and column name line
d:/sqlite/mysqlite.db table have
line
This is the 1st line
This is the 2nd line
This is the 3rd line
2 slc summarize sqlite table
select sex avg(age) avg(height) from have group by sex
3 slc convert sqlite table 'have' to text file using sqlite 'writefile' command
d:/txt/have.txt
This is the 1st line
This is the 2nd line
This is the 3rd line
4 slc convert text file 'd:/txt/have.txt' to slc dataframe using sqlite readfile
text
This is the 1st line
This is the 2nd line
This is the 3rd line
5 slc iterate(loop) over numbers 1-10 and output median (5.5)
OBJECTIVE
I am trying to make sql programmers expert programmers in
9 languages using exactly the same sql queries.
Use packages and procedures for analysis and sql for data wrangling and interfacing.
Add very powerfull sql processing to the open source spss
Posgresql has windows extensions.
I hope to add repos with drop downs to sql with windows extensions in many languages
*language 1 open source spss
*language 2 open source matlab
*language 3 r
*language 4 python
*language 5 altair odbc sqlite
language 6 excel
language 7 perl
language 8 powershell
