VBA - Excel - Coller en texte

Parce que ça fait plus de 2 ans qu'on me dit que c'est trop compliqué de gérer le copier valeur depuis une source externe, j'ai pris 2h de mon temps pour me remettre au VBA après de longues années. Bah c'était pas si compliqué...

Il peut-être utile de forcer le coller dans une feuille Excel pour n'en coller que les valeurs sans autres caractéristiques (mise en forme, format de nombre etc.).

Ex.: Sur une feuille servant d'Interface utilisateur avec des contrôles de format (par Macro).
Sans la captation de l’événement "coller" Excel va également prendre en compte les formats ce qui peut être un inconvénient si vous avez spécifié le format et le contrôle associé de votre cellule (problème principalement sur les chiffres et dates).

Ici nous gérons le cas particulier ou la source de données n'est pas Excel, mais une source Externe telle qu'Oracle (On récupère le contenu du presse Papier).

Vous devez activer la librairie suivante (Il est possible de l'activer par Macro également)
'Tools -> References -> Microsoft Forms 2.0 Object Library"

Dans l'éditeur de Macro (alt+F11), dans la feuille *ThisWorkbook* vous devez copier le code suivant :
'-- Prise en compte de la macro à l'ouverture du classeur
'-- L'action se réalise avec Ctrl+v et Ctrl+V (minuscule, majuscule)

Private Sub Workbook_Open()
    Application.CutCopyMode = False ' Rend le presse-papier disponible et vide

    Application.OnKey "^V", "PasteProc"
    Application.OnKey "^v", "PasteProc"
End Sub

'--Créer un module
Option Explicit

Private module
Public Clipboard As New MSForms.DataObject

'--Coller les procédures qui font le job

'--Il y a un respect du standard des tableaux prenant le caractère tabulation comme séparateur de colonne
'--Il ne reste donc plus qu'à tester si j'ai une source Excel ou Presse Papier
Public Sub PasteProc()
    Select Case Application.CutCopyMode
        Case Is = False
            Call PasteExtern
        Case Is = xlCopy
            Call PasteExcel
        Case Is = xlCut
            Call PasteExcel
    End Select
End Sub

Private Sub writeExcelFromClipboard()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'of you will get a "Compile error: user-defined type not defined"
  Dim Clipboard As New MSForms.DataObject
  Dim S As String
  Dim i, j As Double

  Clipboard.GetFromClipboard
    S = Clipboard.GetText

    Dim start_r, start_c
    Dim tab_line() As String
    Dim tab_tab() As String
    ReDim tab_line(UBound(Split(S, vbCrLf)))

    'start_r = ActiveCell.Row '.Address
    'start_c = ActiveCell.Column
   
    tab_line = Split(S, vbCrLf)
    For i = 0 To UBound(tab_line)
      ReDim tab_tab(UBound(Split(tab_line(i), vbTab)))
      tab_tab = Split(tab_line(i), vbTab)
      For j = 0 To UBound(tab_tab)
        ActiveCell.NumberFormat = "@"
        'ActiveCell.Offset(start_r + i, start_c + j).Value = tab_tab(j)
        ActiveCell.Offset(i, j).Value = tab_tab(j)
      Next j
    Next i

  'readClipboard = S
End Sub

Private Sub PasteExcel()
  'Selection.NumberFormat = "@"
  'ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Private Sub PasteExtern()
  'ActiveCell.Value2 = readClipBoard 'Clipboard.GetFromClipboard
  Call writeExcelFromClipboard
  Clipboard.Clear
End Sub

Share |

Optimisation de requête SQL (Oracle)

Alors comment optimiser ses requêtes pour optimiser les temps de traitement (Oracle) ?

Tout d'abord il faut savoir que le SGBD utilise un plan d'exécution pour effectuer les instructions demandées.

- Comment fait-il ? Il calcul un plan d'exécution optimisé suivant des algorithmes en fonction des informations qu'il possède, notamment de ses statistiques.

L'exécution de la requête se fait de la plus profonde à l'extérieur (la sous sous sous requête avant la requête affichant le résultat).

- Que va t'il regarder ? Tout d'abord il va regarder les différents index existants sur les tables/objets utilisés dans la requête notamment les clés primaires (index unique) ; à partir des méta-données & statistiques (après un analyse "compute statistic" par exemple). Il va alors déterminer son plan d'exécution en fonction de la volumétrie (index ou full scan) etc.

N'hésitez pas à vous faire conseiller de votre DBA (Administrateur de la base de données), qui vous aidera à savoir s'il convient de partitionner, de vous aider à choisir vos tablesspaces suivant les données physiques et volumétrie de vos tables.

Coût de la requête : le "cost"
Le Cost est un indicateur intéressant, il s'affiche lorsque l'on demande au SGBD de nous montrer son plan d'exécution.

Ce qui est le plus coûteux : La jointure

Optimisation :

• Critères sélectif à la fin de la requête (exécutés en premier) 
• Il convient donc de mettre en fin de requête tous les critères de sélection afin de réduire le nombre de ligne à joindre (et non l'inverse).

• select champ1, champ2 from table1 t1, table2 t2 where t1.pk = t2.pk and t1.champ1 in ('toto','titi');

• Il convient également de projeter uniquement les colonnes nécessaires (ramener 10 champs dont 8 ne seront pas utilisés est un volume de données inutile).
• Table la moins volumineuse (référence) à gauche
• Eviter les select... in(select...) utiliser select... exists(select...)
• Utilisez des fonctions analytiques plutôt que des sous select (peut changer exponentiellement le temps d'exécution)
• Lancer l'analyse de la table régulièrement (en full ou % suivant la volumétrie). Cela peut changer le plan d’exécution en fonction des données récupérées
• Créer des index sur les critères de jointure (create [unique] index...)
• Utiliser des identifiants techniques optimise la sélection des relations complexes (table dont la clé primaire et composite et de types hétérogènes (caractère,numérique...))
• Partitionner la table dans la mesure du possible/besoin (la partition est vue comme une table à part et permet donc de réduire fortement la volumétrie) : select... from table1 partition()
• Utilisation de hints : forcer le plan d’exécution (pour les experts : souvent l'analyse / rebuild ou création d'index / refonte de la requête suffit au SGBD de choisir le bon algorithme).

Produit Cartésien : multiplication des lignes de données car aucun lien n'est trouvé entre 2 tables

Quelques références de site :

• Choix dans les requêtes et optimisation physique
• Tuning Sql - Oracle
• Requête analytique - Oracle Analytic Functions

Share |

Particularités Oracle

--renvoi 'PAS NULL'
select case when NULL = NULL then 'NULL' else 'PAS NULL' end from dual;
--renvoi 'NULL'
select case when NULL IS NULL then 'NULL' else 'PAS NULL' end from dual;

--première condition de vérifiée les autres ne sont pas regardées
select
case
when 1 > 0 then 1
when 1 > 0 and 0 = 0 then 0
else null
end X
from dual;
--résultat : 1 et non 0 !

--intervention de null dans une fonction d'agrégation min,max => aucune incidence
select max(a) from
(
select null a from dual
union
select 5 a from dual
union
select 3 a from dual
);
--résultat : 5, c’est ok

--null absorbant sur la somme (opération d'aggrégation)
select sum(null + 1) from dual;
--résultat : null et non 1 !

--transformation des valeurs nulles
select sum(nvl(null,0) + 1) from dual;
--résultat : 1 comme attendu

--ne remonte pas les lignes dont champ est à NULL
select champ from dual where champ <> '1';
select champ from dual where nvl(champ,'x') <> '1'; --ou champ <> '1' or champ is null;

Test universel ?

Pour savoir s'il n'y a pas de donnée, dans tout language de programmation qui se respecte on teste la longueur de la chaîne "trimée"
on teste donc si la chaine est nulle ou vide en même temps !
Mais sou Oracle Null est absorbant...
On testera donc... la nullité de la valeur qu'elle soit à blanc ou à null => non renseigné.


select trim(nvl(null, ' ')) from dual;

select nvl(null, ' ') from dual;

-- Length(null) => NULL !!
select length(null) from dual;

Doc Functions : ss64 - gladir - techonthenet

Share |

Connection à une base de données via JDBC - SqlDeveloper

Pour Toad il faut Oracle client d'installé sur le poste client... qui contient un fichier Tnsname.ora que Toad utilise.

Il est tout à fait possible de se connecter à une base (Oracle, Mysql, SqlServer, Postgre) via jbdc avec le service SID (il vous faudra la JVM). C'est de cette manière que Talend se connecte aux bases.

Utiliser un utilitaire de base de données tel que SqlDevelopper Lite [download]
+) il est complet, graphique et peux tout exécuter
-) tout est dans la même perspective, on ne pas changer simplement une procédure ou des caractéristiques de la table/champs... et là Toad nous manque un peu.

Il existe surement de meilleurs logiciels pour faire la même chose, n'hésitez pas à laisser des commentaires !

Télécharger les drivers correspondant à votre base : Drivers jdbc pour Oracle
[Driver Oracle 9i] [findJar : site recherche de Jar]

• Installer et lancer SqlDevelopper Lite
• Mettre le jar de connection dans le répertoire plugins (
  
• File > open connection > new
• Onglet Oracle
• Entrez les paramètres de connection (vous pouvez facilement récupérer ses informations dans le tnsnames.ora [x:\oracle\ora92\network\ADMIN\]

1. Host:Port => serveur où se trouve la base de données avec son port de connection au service
2. SID : nom de l'instance (nom sur lequel on se connecte habituellement avec Toad et où Toad fait le lien avec la base => schéma)
   
3. User/password : Instance de la base avec mot de passe
   

• Choisir le driver (ex.: ojdbc14-9i.jar) aller dans "Driver Settings" > ADD...
• Vous pouvez maintenant faire joujou avec la base de données ! (ici Oracle)

Pour plus d'information le site de SqlDeveloper est très bien documenté.

[Oracle SQL Developer] Ce logiciel gratuit d'Oracle offre vraiment une très bonne alternative à To@d, la version 2.1, apporte des fonctionnalités vraiment intéressantes en plus de la correction de bugs (notamment sur la gestion du fenêtrage ; en plus aide à la création des requêtes, formatage, glisser/déposer, enregistrement des requêtes exécutées ; entièrement customisable (ajout de fichiers xml) et paramétrable).

Share |

Rachat Sun par Oracle

Dans la famille rachat de mastodonte de l'informatique je voudrais Oracle !
Le 20/04/09 dernier Oracle rachète Sun pour 7,4 Md$ soit 5,6 Md$ après les dettes de Sun épongées.
Sun est un énorme contributeur de logiciel Open-Source et c'est entre autre :

1. OpenOffice (suite bureautique open-source concurrente du pack Office Microsoft)
2. VitualBox (Machine virtuelle Wx, Unix... : utilisée notament sur QUID)
3. Java (language objet utilisée dans beaucoup d'application Open-source (Talend, BIRT))

La communauté est bien sur en attente du devenir des outils open source... Surtout sur la base MySql puisqu'Oracle est un grand acteur de base de données !
La concentration dans le monde informatique est encore d'actualité surtout dans cette période de crise.
Articles : 01 Informatique, ZdNet, concentration BI

Share |