sqlite import / export util supports csv, json, html, sql and xml

[thrive4]

description
A 'meat and potato' utility that allows for
- export of data
|sqlite database via sql query or a folder plus filespec
|to csv, json, html(table), sql and xml
- import of data to a sqlite database / table via
|csv, json and xml via conversion to sql.

Additionally it has special support for .mp3 or .jpg
meta information extraction this will extract the tag info
and or create media links in the generated html table,
a checkfile function which verifies if file excists
according to <path and file> in specified field specified
in a sql query.

Other basic features are:
- basic info database and tables
|cmdsqlite.exe [dbname] showtables
|displays table names contained in [dbname]
|cmdsqlite.exe [dbname] showfields [tablename]
|displays fieldnames contained in [tablename]
- minimal configuration / localization via .ini files

From a code perspective the central trick / conceit is
using a 'spindle' aka key / value array a method which
has it's shortcomings but scales to a degree...

Intended use is mostly educational, many rough edges
with regard to validation of data, etc but might
be useful to some as bare bones conversion util.

usage
cmdsqlite.exe [dbname] [query] optional [exporttype]
cmdsqlite.exe [folder] [filespec] [exporttype]
cmdsqlite.exe [dbname] [query] checkfile [field name]
checkfile returns -1 if the file exists, otherwise zero 0
cmdsqlite.exe [dbname] showtables
cmdsqlite.exe [dbname] showfields [tablename]

options
via \conf\conf.ini\
[output]
' extra sets additional parsing per filetype mp3, jpg
' options: default, extra
htmloutput = extra

Tested and compiled on windows 7 / 10 as 32bit app

release (32bit windows)
https://github.com/thrive4/util.fb.cmdsqlite/releases

source
https://github.com/thrive4/util.fb.cmdsqlite

requirements
sqlite.dll 32-bit DLL (x86) for SQLite version 3.43.0.
https://www.sqlite.org/download.html

quick tutorial
https://github.com/thrive4/util.fb.cmds ... torial.txt

special thanks
tips on commandline parsing via:
viewtopic.php?t=31889 code by coderJeff
data set vgsales via:
https://gist.github.com/zhonglism/f146a ... 26451f841e

[thrive4]

Quite a chuncky update added and fixed the following:

update v1.1 02/11/2023
- added export meta info mp3 to other fileformats
| csv, json, sql and xml
- added export meta info mp3 album to html
- added filter table javascript
| courtesy to: https://www.w3schools.com/howto/howto_j ... _table.asp
| partially implemented filter on specific field
- replaced sort routine for tables javascript
| significant improvements in speed and number
| of rows that can be handeled by browser
| many thanks to tofsjonas
| see: https://github.com/tofsjonas/sortable
- modified main.css less cluterd html table and other style tweaks
- fixed json export double first fieldname and value
| with datasource as folder plus filespec
- improved xml export sanitize values ampersand, etc
- improved csv import some what better handling
| of ".." (doubleqoute enclosed) fields
| remove possible trailing comma at end of record
| added rather strict cleaning of header todo needs more work....
- fixed deselect issue audio playing button html table
- fixed log creation (missing message)
- fixed appname made truncation .exe optional
- fixed checkpath did not set correct path after
| function exit when path is correct
- corrected isalphanumeric function
- misc fixes to getmp3cover
- edits to help.ini all languages
- added initial example with wikidata (sparql) as datasouce
|see data/wikidata.nfo (csv | json) xml is... problematic ...

See:
https://github.com/thrive4/util.fb.cmds ... daa524a30e

[thrive4]

Integrated previously posted functionality from:
viewtopic.php?p=296549
'barebone export mp3 cover (jpeg / png) no dll's'
viewtopic.php?p=288025
'mht 2 html'

update v1.2 18/12/2023
- integrated mp3 album cover export
| from https://github.com/thrive4/util.fb.mp3cover
| added filesize and orientation image as field data
| to mp3cover.csv report
- integrated .mht convert (converts .mht to .html and .txt)
| from viewtopic.php?p=288025
- updated wordwrap2file some what better text formatting
- added sticky table header for data tables
| sticky tip courtesy https://codepen.io/stephen-george-west/pen/rNpjXYL
- updated help.ini with new functionality
- partial implementation json data format escaping rules

See: https://github.com/thrive4/util.fb.cmdsqlite

[thrive4]

Once again a quite sizable update:

update v1.3 10/03/2025
- started a baseline sqlite fts5 implementation
import datasources csv, json, folder, xml
and rudimentary text and exif info specific filetypes
issues: fts5
'rank' is a reserved name so the fieldname needs to be renamed
there are issues with hyphen in table names ....
- gradual implementation json data format escaping rules
import pjson (aka pretty json), single and multi line
with basic error handling
- started a rudimentary form of disk catalog format
drive label, folder, folder date, folder size
example: cmdsqlite e:\ catalog
- added basic comment handling in .query comment character is # (hash)
- added a number of sample .query files
- added basic .txt import and encoding support utf16 and utf8 default 'ascii'
list version each line of text is record
- added basic .srt import format
file, subtitlenr, starttime, endtime, text
- added theme field to mp3 html export theme is last folder in path
example: theme = 'classic' from g:\data\mp3\classic\
- added file metrics folder size, drive label, drive storage
- added support database extension .sqlite used by some applications and browsers
- removed ' in xml node names
- regression checkpath (see 1.1) fixed again
- more fixes to errorhandling csv import
- fixed incorrect table definition xml import
- fixed preserve linefeed when converting .sql to .db
- fixed issue with numerical data in json pattern "fieldname":123
becomes "fieldname":"123"
- issue with wstr still open https://github.com/freebasic/fbc/issues/420
temporary hack check locale if not 'en' or 'nl' use wstr
- updated sqlite3.dll v3.42.0 to v3.49.1
- updated help files and readme.md where needed
- slimmed down function explode
- tweaked getmp3tag increase speed parsing mp3 tags
- improved getfilesfromfolder and getfolders in utilfile.bas

! experimental !
notes regarding text import:

generate index per file:
cmdsqlite.exe f:\dev\freebasic\projects\cmdsqlite\data\indextest *.txt json index
generate sql with content text per file:
cmdsqlite.exe f:\dev\freebasic\projects\cmdsqlite\data\indextest *.txt sql exif
generate sql with content text per file fts:
cmdsqlite.exe f:\dev\freebasic\projects\cmdsqlite\data\indextest *.txt sql exif fts

work in progress bom (utf8 and utf16) default = no pragma
cmdsqlite>cmdsqlite.exe f:\dev\freebasic\projects\cmdsqlite\data\indextest *.txt sql exif utf8
pragma encoding = utf8;

cmdsqlite>cmdsqlite.exe f:\dev\freebasic\projects\cmdsqlite\data\indextest *.txt sql exif utf16
pragma encoding = utf8;

See: https://github.com/thrive4/util.fb.cmdsqlite