A macro for LibreOffice base for importing a music database from a spreadsheet (which is wrapped as a database)

Importer.vbs

REM  *****  BASIC  *****

Option Explicit
Option VBASupport 1 ' Enables InStrRev()

Sub Main

	' Src database is .odb wrapper around .ods Spreadsheet
	DIM oSrcContext AS OBJECT
	oSrcContext	= createUnoService("com.sun.star.sdb.DatabaseContext")
	DIM oSrcDataSource AS OBJECT
	oSrcDataSource = oSrcContext.getByName("fuucamusic")
	DIM oSrcConnection AS OBJECT
	oSrcConnection = oSrcDataSource.GetConnection("","")

	' Dest database is this one
	DIM oDestDataSource AS OBJECT
	oDestDataSource = ThisDatabaseDocument.DataSource
	DIM oDestConnection AS OBJECT
	oDestConnection = oDestDataSource.GetConnection("","")
	
	
	DIM oSQLStatement AS OBJECT
	DIM stSql AS STRING
	DIM oQueryResult AS OBJECT
	
	
	
	' Drop Destination tables
	oSQLStatement = oDestConnection.createStatement()
	stSql = "DROP TABLE Artifact if exists"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "DROP TABLE Person if exists"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "DROP TABLE Work if exists"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "DROP TABLE Voicing if exists"
	oSQLStatement.execute(stSql)
	
	
	' Create Destination tables
	oSQLStatement = oDestConnection.createStatement()
	stSql = "CREATE TABLE Voicing (" & _
		"id int generated by default as identity (start with 1), " & _
		"frequency int not null, " & _
		"vname varchar(60) not null)"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "CREATE TABLE Work (id int generated by default as identity (start with 1), " & _
		"title varchar(200) not null, " & _
		"composer varchar(200) null, " & _
		"arranger varchar(200) null, " & _
		"publisher varchar(200) null, " & _
		"catalog_no varchar(200) null, " & _
		"song_type varchar(200) null, " & _
		"solo_required varchar(200) null, " & _
		"voicing_id int null, " & _
		"instrumentation varchar(200) null, " & _
		"text varchar(200) null, " & _
		"language varchar(200) null, " & _
		"theme varchar(200) null, " & _
		"date_performed varchar(200) null, " & _
		"major_work boolean default false not null, " & _
		"full_score boolean default false not null, " & _
		"current_season boolean default false not null, " & _
		"location varchar(200) null, " & _
		"constraint fk_voicing_id foreign key (voicing_id) references Voicing (id))"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "CREATE TABLE Person (" & _
		"id int generated by default as identity (start with 1), " & _
		"first_name varchar(60) not null, " & _
		"last_name varchar(60) null, " & _
		"email varchar(60) null, " & _
		"phone varchar(60) null, " & _
		"member boolean default true not null)"
	oSQLStatement.execute(stSql)
	
	oSQLStatement = oDestConnection.createStatement()
	stSql = "CREATE TABLE Artifact (" & _
		"work_id int not null, " & _
		"copy_id int not null, " & _
		"checked_out boolean default false not null, " & _
		"person_id int null, " & _
		"constraint fk_work_id foreign key (work_id) references Work (id), " & _
		"constraint fk_person_id foreign key (person_id) references Person (id), " & _
		"constraint pk_work_id_copy_id primary key (work_id,copy_id))"
	oSQLStatement.execute(stSql)
	
	
	
	' Read the source spreadsheet
	oSQLStatement = oSrcConnection.createStatement()
	stSql = "SELECT COMPOSER,TITLE,ARRANGER,""# COPIES"",""PUBLISHER / #"",VOICING,TEXT,THEME,LANGUAGE,INSTRUMENT,NOTES,""DATE PERFM"",""MAJOR WK"" FROM Sheet1"
	oQueryResult = oSQLStatement.executeQuery(stSql)
	
	DIM sComposer AS STRING
	DIM sTitle AS STRING
	DIM sArranger AS STRING
	DIM sNumCopies AS STRING
	DIM sPublisher AS STRING
	DIM sVoicing AS STRING
	DIM sText AS STRING
	DIM sTheme AS STRING
	DIM sLanguage AS STRING
	DIM sInstrumentation AS STRING
	DIM sNotes AS STRING
	DIM sDatesPerformed AS STRING
	DIM sMajorWork AS STRING
	
	DIM sPublisherShort AS STRING
	DIM sCatalogNo AS STRING
	DIM iNumCopies AS INTEGER
	DIM oInnerQueryResult AS OBJECT
	DIM iLastInsertedId AS INTEGER
	DIM bIsMajorWork AS BOOLEAN
	DIM iPos AS INTEGER
	Dim i, j AS INTEGER
	DIM sVoicingId AS STRING
	DIM iVoicingId, iVoicingFrequency AS INTEGER
	
	i = 0
	IF NOT ISNULL(oQueryResult) THEN
		WHILE oQueryResult.Next 'AND i < 12
			sComposer = Replace(oQueryResult.getString(1),"'","''")
			sTitle = Replace(oQueryResult.getString(2),"'","''")
			sArranger = Replace(oQueryResult.getString(3),"'","''")
			sNumCopies = oQueryResult.getString(4)
			sPublisher = Replace(oQueryResult.getString(5),"'","''")
			sVoicing = Replace(oQueryResult.getString(6),"'","''")
			sText = Replace(oQueryResult.getString(7),"'","''")
			sTheme = Replace(oQueryResult.getString(8),"'","''")
			sLanguage = Replace(oQueryResult.getString(9),"'","''")
			sInstrumentation = Replace(oQueryResult.getString(10),"'","''")
			sNotes = Replace(oQueryResult.getString(11),"'","''")
			sDatesPerformed = Replace(oQueryResult.getString(12),"'","''")
			sMajorWork = oQueryResult.getString(13)
			
			bIsMajorWork = (Len(sMajorWork) > 0)
			
			' Split "Publisher:CatNo" or "Publisher/CatNo" or "Pub/lisher:CatNo"
			sPublisherShort = sPublisher
			sCatalogNo = ""
			iPos = InStrRev(sPublisher,":")
			IF iPos > 0 THEN
				sPublisherShort = Trim(Mid(sPublisher,1,iPos-1))
				sCatalogNo = Trim(Mid(sPublisher,iPos+1))
			ELSE
				iPos = InStrRev(sPublisher,"/")
				IF iPos > 0 THEN
					sPublisherShort = Trim(Mid(sPublisher,1,iPos-1))
					sCatalogNo = Trim(Mid(sPublisher,iPos+1))
				END IF
			END IF
			
			' Build the Voicing lookup table
			sVoicingId = "NULL"
			sVoicing = Trim(sVoicing)
			IF Len(sVoicing) > 0 THEN
				oSQLStatement = oDestConnection.createStatement()
				' For some reason identifiers have to be ALL CAPS here but nowhere else???
				stSql = "select ID from VOICING where VNAME='" & sVoicing & "'"
				oInnerQueryResult = oSQLStatement.executeQuery(stSql)
				IF oInnerQueryResult.Next THEN
					iVoicingId = oInnerQueryResult.getInt(1)
					
					' UPDATE
					oSQLStatement = oDestConnection.createStatement()
					stSql = "update Voicing set frequency=frequency+1 where id=" & iVoicingId
					oSQLStatement.execute(stSql)
				ELSE
					' INSERT
					oSQLStatement = oDestConnection.createStatement()
					stSql = "insert into Voicing (frequency,vname) values (1,'" & sVoicing & "')"
					oSQLStatement.execute(stSql)
					
					' SELECT for iVoicingId
					oSQLStatement = oDestConnection.createStatement()
					stSql = "call identity()"
					oInnerQueryResult = oSQLStatement.executeQuery(stSql)
					oInnerQueryResult.Next
					iVoicingId = oInnerQueryResult.getInt(1)
				END IF
				sVoicingId = iVoicingId
			END IF
				
			oSQLStatement = oDestConnection.createStatement()
			stSql = "insert into Work (" & _
				"composer," & _
				"title," & _
				"arranger," & _
				"publisher," & _
				"catalog_no," & _
				"voicing_id," & _
				"instrumentation," & _
				"text," & _
				"language," & _
				"theme," & _
				"date_performed," & _
				"major_work" & _
				") values (" & _
				"'" & sComposer & "'," & _
				"'" & sTitle & "'," & _
				"'" & sArranger & "'," & _
				"'" & sPublisherShort & "'," & _
				"'" & sCatalogNo & "'," & _
				"" & sVoicingId & "," & _
				"'" & sInstrumentation & "'," & _
				"'" & sText & "'," & _
				"'" & sLanguage & "'," & _
				"'" & sTheme & "'," & _
				"'" & sDatesPerformed & "'," & _
				"" & bIsMajorWork & ")"
			oSQLStatement.execute(stSql)		
			
			' Need the last-inserted ID.  In hindsight, this could just be an integer variable. 
			' skip the query, but this is more 'correct' I guess
			oSQLStatement = oDestConnection.createStatement()
			stSql = "call identity()"
			oInnerQueryResult = oSQLStatement.executeQuery(stSql)
			oInnerQueryResult.Next
			iLastInsertedId = oInnerQueryResult.getInt(1)
	
			' Eep!  CInt has the old atoi behavior, where CInt("5 Dogs") == 5.  Oh, well, roll with it...
			iNumCopies = CInt(sNumCopies)
			FOR j = 1 To iNumCopies
				oSQLStatement = oDestConnection.createStatement()
				stSql = "insert into Artifact (work_id,copy_id) values (" & iLastInsertedId & "," & j & ")"
				oSQLStatement.execute(stSql)
			NEXT
			
			'Msgbox(iNumCopies)
			i = i + 1
		WEND
	END IF	
End Sub