My wish is to create a database from the data in a csv file.
After the information is entered there should be no connection to the CSV file.
Are there any instructions on how to do it?
Is there an API that makes it possible to, for example, enter the information into Base?
(delete / change / add information to the database using a script or similar)
HSQL makes it easy to edit csv just like an ordinary database table.
As always, the gory details depend on the structure of your csv files.
Text table documentation of embedded HSQL 1.8. Still the same with recent versions:
http://www.hsqldb.org/doc/1.8/guide/ch06.html
Demo databases: https://forum.openoffice.org/en/forum/download/file.php?id=5770
Years ago a I adapted a macro from the german guide for Base to my needs (Link below). The script copies between two databases and the following section copies between Calcvand database. So, yes there is also an API, accesible for BASIC, Python, JavaScript etc.
.
To build a smaller database (once) I would not script this but use a manual copy/paste via Calc. Also I see Base more as a connection to my databases and most engines have their own possibilities to import csv, wich is often more efficient - like the method shown by Villeroy for HSQLDB. If you need to update often scripting can be useful…
PS: Don’t expect anything to be easy here. I found issues with handling Charsets when syncing two utf8-databases, wich I didn’t expect, and the various threads on dates from csv show other lurking problems.
The following Basic function inserts csv data into a HSQL database table. The same (or similar) code should work with any database engine supporting text tables linked to csv files.
The code performs 4 steps:
INSERT INTO "DataTable" (SELECT * FROM "View) where “View” refers to a view (a SELECT statement) which does all the conversion work.The view
The function returns the count of inserted rows.
Arguments:
Function ImportCSV(oConnection, sURL$, sTextFile$, sTextTable$, sView$, sDataTable$) As Long
sqlSET ="SET TABLE """& sTextTable & """ SOURCE "
oStmt1 = oConnection.prepareStatement(sqlSET & "OFF")
oStmt2 = oConnection.prepareStatement(sqlSET & "ON")
sqlINSERT = "INSERT INTO """& sDataTable &""" (SELECT """& sView &""".* FROM """& sView &""")"
'print sqlINSERT
oStmt3 = oConnection.prepareStatement(sqlINSERT)
b = oStmt1.execute()
filecopy sURL, cDatabase_Path & sTextFile
b = oStmt2.execute()
b = oStmt3.executeUpdate()
ImportCSV = b
End Function
A macro calling the above function may look like this:
Sub my_import(ev)
Const cCaption = "Import my csv" 'file picker caption
Const cDownloadPath = "file:///home/villeroy/Downloads/" 'file picker path
Const cDatabase_Path = "file:///home/villeroy/hsql/database/" 'database path
Const cLabel = "Text table" 'label of file picker filter
Const cPattern = "*accounting*.csv" ' file picker filter
Const cCSV = "My_Import.csv" 'name of text file
Const cTextTable = "My_Linked_TextTable"
Const cView = "My_Import_View"
Const cDataTable = "Database Table"
sURL = pickFile(cCaption, cDownloadPath, cLabel, cPattern)'file picker function
REM get connection from calling form control:
frm = ev.Source.Model.Parent
conn = frm.ActiveConnection
x = ImportCSV( _
oConnection:=conn, _
sURL:=sURL, _
sTextFile:=cCSV, _
sTextTable:=cTextTable, _
sView:=cView, _
sDataTable:=cDataTable _
)
Msgbox x &" records imported."
End Sub
P.S. This is how I used to do the same thing without macro code:
P.P.S. A demo: Apache OpenOffice Community Forum - [HSQL, Base, Basic] Macro Driven CSV Import - (View topic)
Download csv_import.odb and import.csv, open the database, the form, click the button. Click the same button a second time. It will not import the same data twice.