LPR database - Import CSV and sort into tables

[Mark_M]

Hello, I’m familiar with creating a relational database but confused on making this automated and getting started adding existing data.

Database purpose:
Take CSV exports from an LPR CCTV camera and store all of these records. This is installed on my street as part of our neighbourhood watch group.
Current spreadsheet has 117,000 records in 5 months. This will need to auto scale and automatically add newly detected vehicles licence plates.
(LPR - Licence Plate Recognition. Automatically creates a record per vehicle detected.)

…
Problem asking for help:
How do I make a form to import a CSV file exported from the LPR camera and have Base sort this CSV’s rows into the relevant tables/fields?
I’m assuming Macros but do not know where to start.

…
A CSV export from the LPR camera’s format:

[Date Time, Licence Plate, Plate Type, Speed, Direction, Country/Region, Detection area, Plate Color, Vehicle Type, Vehicle Color]
[03/09/2024 00:42:17, ABC123, Visitor, 38km/h, Approach, -, 1, White, Car, Green]

Each row of the CSV is one LPR detection and a vehicle may pass over 500 times. I’m scared on how to sort that right when there is some vehicles with 800 detections.
Colours may vary each detection as this is the camera guessing the colour. Speed radar measurement always adds the unit ‘km/h’ after a 0-999 number.

My attempt so far at creating a relational database:

Multiple values from the export CSV can be omitted.

Any help is much appreciated .
Working in Windows 10, LibreOffice Base 24.8.0.3 64bit.

[Villeroy]

I’d suggest a HSQL database starting in embedded mode.
HSQL supports linked text tables.
It is easy to extract an embedded HSQL draft from the Base document to be used in a productive environment.
Any solution has to deal with actual data types in the target table and how data are represented in the text files to be imported.

@Mark_M

[03/09/2024 00:42:17, ABC123, Visitor, 38km/h, Approach, -, 1, White, Car, Green]

03/09/2024 is not a valid SQL date. It is not even clear if it refers to 3rd of September or 9th of March.
38km/h is not a number
If “Car” refers to a vehicle type, all the possible type names need to be stored in table “vehicle types”.

I have a ready made macro solution to import all kinds of csv into a HSQLDB.
It requires

• a HSQLDB with a text table linked to a distinct text file. Other databases may work as well as long as they support linked text tables. The text table consists mostly of varchar columns such as 1/2/3 dates.
• a view converting the types, skipping unwanted noise records, skipping duplicates and selecting the columns in right order according to the target table. In your attempt, the view may also lookup an integer Type_ID from the string “Car” if you decide in favour of an integer primary key in table “Vehicle Types”.

Having a database connection, the file paths of incoming file and linked file, the names of text table, view and target table, 10 lines of StarBasic code import csv:

Function ImportCSV(oConnection, sSource$, sTarget$, sTextTable$, sView$,  sDataTable$) As Long
' database connection, url or syspath of new csv file, url or syspath of linked csv file to be replaced, 
' name of text table, name of the view converting text data, name of actual INSERT table
	sqlSET ="SET TABLE """& sTextTable & """ SOURCE "
	sqlINSERT = "INSERT INTO """& sDataTable &""" (SELECT """& sView &""".* FROM """& sView &""")"
	oStmt1 = oConnection.prepareStatement(sqlSET & "OFF")
	oStmt2 = oConnection.prepareStatement(sqlSET & "ON")
	oStmt3 = oConnection.prepareStatement(sqlINSERT)
	oStmt1.execute() 'disconnect linked text table
	filecopy sSource, sTarget 'replace linked csv file
	oStmt2.execute() 'reconnect linked text table
	ImportCSV = oStmt3.executeUpdate() 'insert view data into data table, return record count
End Function

http://www.hsqldb.org/doc/1.8/guide/ch06.html

CREATE TEXT TABLE "IMPORT"(TS CHAR(19), PLATE(VARCHAR(12), SPEED VARCHAR(8), DIR(VARCHAR(12),X VARCHAR(3), COLOR VARCHAR(12), VTYPE VARCHAR(20), COLOR2 VARCHAR(12);
SET TABLE "IMPORT" SOURCE "import.csv;ignore_first=true"

creates the text table and links it to import.csv in the database directory (directory of odb document in case of embedded HSQL)