J'ai actuellement une macro permettant l'import d'un fichier csv dans la table de ma base, cependant j'ai un problème avec les caractères accentués qui ne sont pas correctement pris en compte.
Ceci doit-être un problème d'encodage, mais je ne trouve pas la commande à ajouter dans ma macro pour régler ce problème.
Voici la macro :
Code : Tout sélectionner
Dim maConnexion as Object, oConnexion as Object, oForm As Object
Sub AjoutDonnees
Dim DrvMan As Object, maRequete as Object
Dim cheminCSV As String, URLbdcsv As String, instrSQL as String
Dim Infos(3) As New com.sun.star.beans.PropertyValue
ThisDatabaseDocument.CurrentController.connect("","")
maConnexion = ThisDatabasedocument.CurrentController.ActiveConnection
DrvMan = CreateUnoService("com.sun.star.sdbc.DriverManager")
cheminCSV = SelectCSV
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())
instrSQL = "DELETE FROM ""donnees_diag3"""
maRequete = maConnexion.createStatement()
maRequete.executeUpdate(instrSQL)
oForm = thisComponent
GererBarresOutils("f_mise_a_jour")
CopierDonnees
End Sub
Function SelectCSV()
Dim oFP As Object, lesFichiers() As String
oFP = CreateUnoService("com.sun.star.ui.dialogs.OfficeFilePicker")
oFP.appendFilter("Textes", "*.csv")
oFP.Title = "Sélectionnez le fichier CSV"
If oFP.execute = com.sun.star.ui.dialogs.ExecutableDialogResults.OK Then
lesFichiers() = oFP.Files
SelectCSV = lesFichiers(0)
Else
MsgBox("Aucun fichier sélectionné." & chr(13) & "La connexion ne peut être établie",64,"Import CSV"
oFP.dispose
End
End If
oFP.dispose
End Function
Sub CopierDonnees
On Error GoTo CopierDonnees_Err
Dim unRowSet as Object, maRequete as Object, resuQuery as Object, maRequete2, 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 ""Diagnostic_courants_parasites_date2"" ORDER BY ""ID"""
instrSQL2 = "SELECT COUNT(*) as ""nb"" FROM ""Diagnostic_courants_parasites_date2"""
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 = "donnees_diag3"
.Execute
x = 1
avance.start("Veuillez patienter ...", Compte)
Do While resuQuery.Next
.moveToInsertRow
For i = 1 to 325
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))
Case "DATE"
dteNaiss = CDate(resuQuery.getString(i))
.Columns.getByIndex(i -1).updateDate(DateTodbDate(dteNaiss))
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
ThisDatabaseDocument.FormDocuments.getByName("f_mise_a_jour").Close
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
Function DateTodbDate( aDate as Date )
Dim dbDate as New com.sun.star.util.Date
dbDate.Month = Month( aDate )
dbDate.Day = Day( aDate )
dbDate.Year = Year( aDate )
dateTodbDate = dbDate
End Function
Sub GererBarresOutils(nomdocument)
On Error Resume Next
Dim BarresOutils as Variant
Dim menu as string
BarresOutils = array( _
"private:resource/menubar/menubar", _
"private:resource/statusbar/statusbar", _
"private:resource/toolbar/alignmentbar", _
"private:resource/toolbar/arrowshapes", _
"private:resource/toolbar/basicshapes", _
"private:resource/toolbar/bezierobjectbar", _
"private:resource/toolbar/calloutshapes", _
"private:resource/toolbar/colorbar", _
"private:resource/toolbar/drawbar", _
"private:resource/toolbar/drawingobjectbar", _
"private:resource/toolbar/drawtextobjectbar", _
"private:resource/toolbar/extrusionobjectbar", _
"private:resource/toolbar/flowchartshapes", _
"private:resource/toolbar/fontworkobjectbar", _
"private:resource/toolbar/fontworkshapetype", _
"private:resource/toolbar/formcontrols", _
"private:resource/toolbar/formsnavigationbar", _
"private:resource/toolbar/formdesign", _
"private:resource/toolbar/formsfilterbar", _
"private:resource/toolbar/formtextobjectbar", _
"private:resource/toolbar/frameobjectbar", _
"private:resource/toolbar/fullscreenbar", _
"private:resource/toolbar/graffilterbar", _
"private:resource/toolbar/graphicobjectbar", _
"private:resource/toolbar/insertbar", _
"private:resource/toolbar/insertobjectbar", _
"private:resource/toolbar/mediaobjectbar", _
"private:resource/toolbar/moreformcontrols", _
"private:resource/toolbar/numobjectbar", _
"private:resource/toolbar/oleobjectbar", _
"private:resource/toolbar/optimizetablebar", _
"private:resource/toolbar/previewobjectbar", _
"private:resource/toolbar/standardbar", _
"private:resource/toolbar/starshapes", _
"private:resource/toolbar/symbolshapes", _
"private:resource/toolbar/tableobjectbar", _
"private:resource/toolbar/textobjectbar", _
"private:resource/toolbar/toolbar", _
"private:resource/toolbar/viewerbar")
If thisDatabaseDocument.FormDocuments.hasByName(nomDocument) Then
For each menu In BarresOutils()
thisDatabaseDocument.FormDocuments.getByName(nomDocument).Component.CurrentController.Frame.LayoutManager.hideElement(menu)
Next menu
End If
If nomDocument = "f_mise_a_jour" Then thisDatabaseDocument.FormDocuments.getByName(nomDocument).Component.CurrentController.Frame.Title = "Veuillez patienter"
End Sub