J'ai actuellement une macro qui importe un fichier csv dans une table, tout deux ayant exactement la même structure. La clé primaire est donc la même (DateEDE).
Le problème étant que mon fichier csv contiendra des enregistrements déjà présent dans ma table donc avec la même DateEDE (et donc impossible d'importer des doublons).
Mon idée est de rajouter un champ ID auto incrément qui sera ma clé primaire de manière à ajouter les enregistrements doublons (pour ensuite les supprimer) . Mais j'ai un message d'erreur : Type de données incohérent.
Voici ma macro :
Code : Tout sélectionner
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, CharSet as String
Dim Infos(4) As New com.sun.star.beans.PropertyValue
ThisDatabaseDocument.CurrentController.connect("","")
maConnexion = ThisDatabasedocument.CurrentController.ActiveConnection
DrvMan = CreateUnoService("com.sun.star.sdbc.DriverManager")
Coding = CreateUnoService("com.sun.star.sdbc.FLATConnectionProperties")
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"
Infos(4).Name = "CharSet"
Infos(4).Value = "UTF-8"
oConnexion = DrvMan.getConnectionWithInfo(URLbdcsv, Infos())
'instrSQL = "DELETE FROM ""donnees_diag4"""
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_dateEDE"" GROUP BY DateEDE"
instrSQL2 = "SELECT COUNT(*) as ""nb"" FROM ""Diagnostic_courants_parasites_dateEDE"""
'instrSQL3 = "DELETE FROM donnees_diag3 AS T WHERE EXISTS (SELECT * FROM (SELECT * FROM donnees_diag3) AS T2 WHERE T.ID <> T2.ID AND T.Numéro EDE = T2.Numéro EDE) "
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_diag5"
.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 80
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