2023-01-05: Example based on this snippet: https://forum.openoffice.org/en/forum/v ... 80#p532780
The following snippet is supposed to be embedded in a database document, triggered by a form button. It presumes the following:
1) Your database document is connected to a database engine supporting text tables. HSQL does support text tables and my code assumes a HSQL connection. Some other database connection may require some modification.
2) Your database contains a view translating the text table data into valid data types of a target table (right data types in right order of columns), so when you manually copy the view onto the target table, your data import with no errors.
The Basic code does the following:
1) Get the text file to be imported from a file picker dialog where you pick some file filtered by a given file name pattern from a given directory.
2) Disconnect the text table from its file.
3) Copy the text file to the specified database directory.
4) Reconnect the text table to the exchanged file.
5) Run an INSERT statement inserting all the view records into the specified target table.
6) Show a message box with the count of imported records.
7) Refresh the form where the calling button belongs to.
Code: Select all
Sub My_Specific_CSV_Import(e)
REM Importing some specific csv files from a bank named "APO Bank"
'calls: pickFile, ImportCSV
REM file URL of download path where file picker starts searching
Const cDownloadPath = "file:///home/user/Downloads"
REM file URL of database path where to copy the text file
REM which is linked to a text table in the database
Const cDatabase_Path = "file:///home/user/Dokuments/hsql/database/"
REM File picker dialog caption:
Const cAPO_Caption = "APO Bank Account Statement"
REM File type label in file picker dialog:
Const cAPO_Label = "APO-BANK CSV"
REM file name pattern to be used as a filter in the file picker dialog:
Const cAPO_Pattern = "*-account_statement.csv"
REM name of text file in the database folder:
Const cAPO_File = "APO_Import.csv"
REM name of the text table
Const cAPO_Import = "APO_Import"
REM name of the view converting the text table into valid table data
Const cAPO_View = "Kontoimport"
REM name of the target table:
Const cDataTable = "tblAccountData"
Dim sURL As String, x As Long
sURL = pickFile(cAPO_Caption, cDownloadPath, cAPO_Label, cAPO_Pattern)
if len(sURL) = 0 then exit sub
x = ImportCSV(sURL:=sURL, sDBPath:=cDatabase_Path, sTextFile:=cAPO_File, sTextTable:=cAPO_Import, sView:=cAPO_View, sDataTable:=cDataTable)
Msgbox x &" records imported."
if x > 0 then
with e.Source.Model.Parent
.reload()
end with
endif
End Sub
REM this function may require some engine specific modifications regarding the SQL statements:
Function ImportCSV(sURL$, sTextFile$,sDBPath$, sTextTable$, sView$, sDataTable$) As Long
conn = ThisDatabaseDocument.CurrentController.ActiveConnection
sqlSET ="SET TABLE """& sTextTable & """ SOURCE "
oStmt1 = conn.prepareStatement(sqlSET & "OFF")
oStmt2 = conn.prepareStatement(sqlSET & "ON")
sqlINSERT = "INSERT INTO """& sDataTable &""" (SELECT """& sView &""".* FROM """& sView &""")"
'print sqlINSERT
oStmt3 = conn.prepareStatement(sqlINSERT)
b = oStmt1.execute()
filecopy sURL, sDBPath & sTextFile
b = oStmt2.execute()
b = oStmt3.executeUpdate()
ImportCSV = b
End Function
Function pickFile(sTitle$, sInit$, sFilterLabel$, sPattern$) As String
REM return a single file URL or ""
REM dialog starts at office default directory if sInit = ""
Dim oPicker, x()
oPicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oPicker.setTitle(sTitle)
oPicker.setDisplayDirectory(sInit)
oPicker.setMultiSelectionMode(False)
oPicker.appendFilter(sFilterLabel, sPattern)
if oPicker.execute() then
x() = oPicker.getFiles()
pickFile = x(0)
endif
End Function