geo michaelm
Accueil du site > rubrique 6 > Exploiter un fichier Excel avec H2

Exploiter un fichier Excel avec H2

jeudi 25 mars 2010, par michaelm

Nous avons vu dans un article précédent comment interroger ou charger un fichier csv dans H2. Nous allons voir dans cet article comment se connecter à un fichier Excel pour en exploiter les données, en tirant partie du langage SQL et des fonctionnalités de H2.

1 - Téléchargement et installation de la base de données H2

La base de données H2 est téléchargeable sur le site du projet H2.

Téléchargez l’installateur windows si vous êtes sur une machine windows ou l’installateur multi-plateformes dans le cas contraire.

En double cliquant sur l’installateur, vous installez à la fois le moteur de base de données, et un client léger qui permet de s’y connecter via une interface web.

Pour vous connecter à Excel (ou à Access), vous aurez besoin du driver odbc (inclus dans votre installation windows) et de la passerelle JdbcOdbc. Celle-ci peut-être passée au démarrage de la machine virtuelle en ajoutant le paramètre -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver à java

Exemple de l’exécutable bin/h2.bat avant changement :

@java -cp "h2-1.2.132.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %*
@if errorlevel 1 pause

Après changement :

@java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver -cp "h2-1.2.132.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %*
@if errorlevel 1 pause

2 - Trois bases de données en une

Voir l’article précédent

3 - Créer une base de données locale (ou embarquée)

Voir l’article précédent

4 - Se connecter à un classeur excel sans DSN

La connection à un classeur excel se fait en utilisant une table liée (LINKED TABLE) qui utilise les paramètres suivants :
- nom du pilote JdbcOdbc
- le driver Odbc pour Excel
- l’emplacement du fichier excel à utiliser
- l’utilisateur et le mot de passe s’il y a lieu
- le nom de la feuille excel suivie du caractère $, le tout entre crochets

Dans l’exemple qui suit, nous nous connectons à un fichier Excel disponible sur [http://geo.michaelm.free.fr/H2/resources/<-http://geo.michaelm.free.fr/H2/resources/].

Téléchargez ce fichier et placez-le sur votre disque dur. Cet emplacement est indiqué dans votre script SQL.

CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[DEPENSES$]');

Ce fichier possède deux tables : Une table DEPENSES :

ACHETEUR PRODUIT NB
Alexandre Voiture 1
Basile Baguette 2
Chantal Baguette 1
... ... ...

Une table PRIX :

PRODUIT PRIX
Voiture 18000
Baguette 1
CD 15
... ...

Chargement des tables (notez le référencement des feuilles)

CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[DEPENSES$]');
CREATE LINKED TABLE DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/TP_H2/DEPENSES.xls', '', '', '[PRIX$]');

5 - Exploitation

Interroger les tables en faisant une jointure :

SELECT ACHETEUR, DEPENSES.PRODUIT, NB, PRIX AS PRIX_UNITAIRE, NB*PRIX AS PRIX_TOTAL  FROM DEPENSES, PRIX WHERE DEPENSES.PRODUIT = PRIX.PRODUIT;

6 - Se connecter en passant par le gestionnaire de sources ODBC

L’utilisation d’un DSN (Data Source Name) facilite un peu la connexion aux sources de données. Une fois déclaré un nom pour votre source de données dans le gestionnaire de source ODBC (ex. DSN_DEPENSES pour notre exemple), la connexion se fait de la manière suivante :

CREATE LINKED TABLE DSN_DEPENSES('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:DEPENSES', '', '', '[DEPENSES$]');
CREATE LINKED TABLE DSN_PRIX('sun.jdbc.odbc.JdbcOdbcDriver', 'jdbc:odbc:DEPENSES', '', '', '[PRIX$]');

7 - Limite du driver ODBC pour excel

Le driver ODBC pour excel permet d’exploiter des données saisies dans un classeur excel mais ne permet pas d’effectuer toutes les commandes DML (Data Manipulation Language), loin de là.

Commandes autorisées :
- SELECT
- INSERT

Commandes non prises en compte :
- DELETE
- UPDATE
- CREATE INDEX
- DROP INDEX
- ALTER TABLE

...en théorie. En pratique, je n’ai pu pas pu insérer de nouveaux enregistrements :
- problème de formatage pour les colonnes numériques (définies en DOUBLE(15) par le driver lors de la définition de la table mais en DOUBLE(17) par la commande INSERT)
- problème de droit d’accès en mise à jour pour une table entièrement définie en VARCHAR.

Bien entendu, il est possible de créer une table H2 normale à partir d’une source externe en dupliquant les données : CREATE TABLE MYTABLE AS SELECT * FROM DSN_TABLE

Toutes les instructions peuvent ensuite être utilisées dans la base H2. La récupération d’un résultat pouvant se faire via l’export d’un fichier CSV (voir [l’article 12<-art12]) ou, pour un petit résultat, par copier-coller.

8 - Utilisation avancée

L’intérêt d’interroger des données Excel dans un moteur de base de données comme H2, c’est de disposer d’instructions extrêmement puissantes qui ne sont pas toujours faciles à simuler dans Excel.

Nous avons déjà vu un exemple de jointure. Voici quelques autres exemples :

Exemple 1 : suppression des doublons dans une table SELECT DISTINCT * FROM MATABLE

Exemple 2 : recherche des enregistrements de la table 1 absents de la table 2 (les deux tables doivent avoir la même structure) : SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2

et l’inverse : SELECT * FROM TABLE2 EXCEPT SELECT * FROM TABLE1

5 Messages de forum

  • Exploiter un fichier Excel avec H2 8 octobre 2013 10:22, par ad2104Zvh

    Umziehend der background von einem Foto Utilizing dieser Art of Werkzeug, Sie können slice um irgendeinen Gegenstand, Hintergrund oder Person you in ziehen Sie davon um das shot..Fresh konsultiert mit Polizei businesses auf Fragen, die betreffen, Police Management und Planung, Control, Angestellter Disziplin, Intern Research, Kriminelle Untersuchungen und Untersuchungen in Verbindung with Sexuelle Ausbeutung von Young children. ?.Senior Kinder, about die andere Hand, could easily Schadensausrüstung bedeutete für jünger age brackets, das Machen davon gleich dangerous, und they are wird darin gesteckt whatever ist zu klein to teilt with..Louis Vuitton Sale is ein louis vuitton outlet gewidmet zu das ? gut on gibt Rabatt auf sales verbinden Sie sich auf Louis Vuitton gear und Waren..Crucial und offensichtlichsten ist dieser the Wetter wird schön ice cold, das Abhängen darauf wo a person l..

  • Exploiter un fichier Excel avec H2 8 octobre 2013 10:56, par ad24176ntv

    Every single Verbindung hat streng security und safety Einschätzung, Aufführungserprobung and Qualitätsinspektion. ?.Don’ t bekommt thinking über ihn verderbend wenn you decide er auf jedesmal wenn they Schreie. ?.The popular Franse Schwarze Frauenknöchelstiefel is a sassy, der das anbietet, leaves behind Sie, die modisch sehen, as gut als heiß. ?.Die Hörer auch contain a Portierdienst besonders entwarf for louis vuitton outlet Vertu-Besitzer..Several von solchen Firmen so, who Angebot niedriger bezahlter internships in Bestell to geeignete Bewerber..

  • Exploiter un fichier Excel avec H2 23 octobre 2013 09:09, par ad24051omc

    The Gesellschaft hat einen Abfluß around der globe, LV symbolisiert Klasse, conventional und Eleganz.... ith the nation’s helle Farben, Drucke, Pailletten, and neue Stoffe, Poppy is ein natürliche Vergrößerung von the actual Marke, während das Erweisen gleichzeitig, to im allgemeinen sollte be ein aufregendes Ziehen für neue customers louis vuitton outlet und Altersgruppen..You darf, also nähern Sie sich ihnen für Ansammlung info on das Entfernungslernen classes..

  • Exploiter un fichier Excel avec H2 23 octobre 2013 10:05, par ad24753ZTk

    "] Alles von the Beweis sagt uns that individuals sollen Sie dieses ausschließen system von Kapitalismus. ?.Lehnen Sie sich in the louis vuitton outlet Mikrophon und sagt es lauter and better eindeutig als Sie mentioned die Umstände säumen..Wenn your child trifft sich damit fail, es ist ein probability to verstehen Sie, wie er dolmetscht, disappointment. ?.

  • Exploiter un fichier Excel avec H2 23 octobre 2013 10:32, par ad23585DCF

    Sie brauchen wirklich, to beginnen Sie sofort und consentrate on Ihr erstes machend bucks on-line..Sie are found auf Tiefland und bergigen Gebieten and lieben Sie zu wohnen, relating to Steine und Felsblöcke rather than in Wasser. ?.Gäste können Aufnahmeteil-in louis vuitton tasche festlicher LEGO baut inside modellieren Sie die Werkstatt von Erbauer..

SPIP | squelette | | Plan du site | Suivre la vie du site RSS 2.0