1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
| Dim maConnexion as Object, oConnexion as Object, oForm As Object
Sub AjoutDonnees
Dim DrvMan As Object, maRequete as Object, Coding as Object
Dim cheminCSV As String, URLbdcsv As String, instrSQL as String
Dim Infos(3) As New com.sun.star.beans.PropertyValue
Dim x As Long '***
Dim sNomFichier As String '***
Dim lsNomFichier As Integer '***
ThisDatabaseDocument.CurrentController.connect("","")
maConnexion = ThisDatabasedocument.CurrentController.ActiveConnection
DrvMan = CreateUnoService("com.sun.star.sdbc.DriverManager")
Coding = CreateUnoService("com.sun.star.sdbc.FLATConnectionProperties")
cheminCSV = "/home/elfouste/Documents/Python/Svg_Csv/DnExif.csv"
' extraction du nom de fichier sélectionné '***
sNomFichier = cheminCSV
lsNomFichier = len(sNomFichier)
x = lsNomFichier
while mid(sNomFichier,x,1) <> "/"
x = x-1
wend
sNomFichier = mid(sNomFichier,x + 1,lsNomFichier-x-4)
URLbdcsv = "sdbc:flat:" & cheminCSV
Infos(0).Name = "HeaderLine"
Infos(0).Value = True
Infos(1).Name = "FieldDelimiter"
Infos(1).Value = chr(44)
Infos(2).Name = "StringDelimiter"
Infos(2).Value = ","
Infos(3).Name = "Extension"
Infos(3).Value = "csv"
oConnexion = DrvMan.getConnectionWithInfo(URLbdcsv, Infos())
maRequete = maConnexion.createStatement()
oForm = thisComponent
instrSQL = "DELETE FROM ""Tp_Pht"""
maRequete.executeUpdate(instrSQL)
CopierDonnees(sNomFichier)
End Sub
'========================================================================================================
Sub CopierDonnees(NomFichier)
On Error GoTo CopierDonnees_Err
Dim unRowSet as Object, maRequete as Object, resuQuery as Object, maRequete2 as object, Resultat as Object
Dim Fenetre as Object, FenetreForm as Object, avance as Object
Dim instrSQL as String, instrSQL2 as String, i as Integer, dteNaiss as Date, Compte as Integer, x as Integer
Fenetre = ThisDatabaseDocument.CurrentController.Frame.ContainerWindow
FenetreForm = oForm.currentcontroller.Frame.ContainerWindow
Fenetre.Enable = False
FenetreForm.Enable = False
avance = oForm.CurrentController.StatusIndicator
unRowSet = createUnoService("com.sun.star.sdb.RowSet")
instrSQL = "SELECT * FROM " & NomFichier
instrSQL2 = "SELECT COUNT(*) as ""nb"" FROM " & NomFichier
maRequete = oConnexion.createStatement()
maRequete2 = oConnexion.createStatement()
Resultat = maRequete2.executeQuery(instrSQL2)
Resultat.Next
Compte = resultat.getInt(1)
resuQuery = maRequete.executeQuery(instrSQL)
With unRowSet
.ActiveConnection = maConnexion
.CommandType = com.sun.star.sdb.CommandType.TABLE
.Command = "Tp_Pht"
.Execute
x = 1
avance.start("Veuillez patienter ...", Compte)
Do While resuQuery.Next
.moveToInsertRow
For i = 1 to 17
Select Case .Columns.getByIndex(i -1).TypeName
Case "INTEGER"
.Columns.getByIndex(i -1).updateInt(resuQuery.getInt(i))
Case "VARCHAR"
.Columns.getByIndex(i -1).updateString(resuQuery.getString(i))
Case "NUMERIC"
.Columns.getByIndex(i -1).updateInt(resuQuery.getInt(i))
End Select
If i = 1 Then
.insertRow
Else
.UpdateRow
End If
Next i
avance.Value = x
avance.Text = "Ligne " & x & " recopiée"
x = x + 1
Loop
avance.Text = "Terminé " & Compte & " lignes recopiées"
End With
oConnexion.Dispose
unRowSet.Dispose
Wait 800
avance.End
FenetreForm.Enable = True
Fenetre.Enable = True
CopierDonnees_Exit:
On Error GoTo 0
Exit Sub
CopierDonnees_Err:
MsgBox(Error, 16)
FenetreForm.Enable = True
Fenetre.Enable = True
oConnexion.Dispose
unRowSet.Dispose
Resume CopierDonnees_Exit
End Sub |
Partager