[Avis] Formulaires d'intégration de fichiers Excel dans Oracle

**habasque** · 08/01/2007, 18h06

Salut à tous,

je travaille actuellement sur le développement d'une base de données Oracle : serveur 10g et forms 6i.
je souhaite mettre à disposition des utilisateurs des formulaires leur permettant de charger eux mêmes leurs fichiers (Excel pour la plupart) dans la base de données. en effet, les fichiers arrivent en masse et ce serait une perte de temps énorme pour l'équipe informatique d'être en charge de cette tâche.
au préalable, nous aurons pris soin de définir avec les utilisateurs des fichiers normés (car hétérogènes pour le moment) conforme à la structure de la base de données.
quelle solution technique puis-je mettre en place sachant que je ne suis pas limité à Oracle Forms 6i ? je peux sans problème développer une interface Java pour répondre à cette demande...

merci d'avance pour vos avis

**taska** · 08/01/2007, 18h53

QUESTION :

Que dois tu restituer aux utilisateurs ?
1. Les fichiers excel conformes aux originaux.
2. Les données de ces fichiers.

CDLT.

**habasque** · 08/01/2007, 19h41

en fait, à partir des fichiers Excel d'origine, nous voulons procéder au stockage des données dans la base sans passer par une resaisie complète de l'ensemble des fichiers. nous souhaitons une intégration automatisée des données.

par la suite, l'extraction des données se fera par extraction de fichiers texte via des formulaires de sélection par critères, qui pourront être croisement, jointure de plusieurs tables de la base.

**taska** · 08/01/2007, 21h09

nous en avons discuté récemment ...
http://www.developpez.net/forums/sho...d.php?t=259171

mon avis :

.. je pense que l'utilisation de SQL Loader ou des tables externes nécessite une opération manuelle qui consiste à exporter le fichier excel au format CSV tous les jours. C'est fastidieux et cela cré une opération qu'il est peut être difficile d'organiser et de plannifier.

La solution que je propose, si la version de la BD l'accepte, c'est un package dans lequel procédures et fonctions pourraient effectuer le traitement. Pour cela, la lecture du fichier Excel peut être effectuée en java à l'aide d'API existantes ( gratuites ) et bien documentées.
http://jakarta.apache.org/poi/index.html
http://www.andykhan.com/jexcelapi/
évidement, vous devez avoir un developpeur java sous la main, mais je pense que c'est la façon la + simple et la + rationnelle de traiter votre demande.

... les utilisateurs ont-ils leur poste configuré pour une utilisation de forms 6i ?

... Différentes solutions sont envisageables reposant sur l'intégration de code java :

1. Une procédure noyau (PL/SQL ) chargée de réaliser l'import des données. Cette procédure fait appel à une classe java chargée d'importer les données du fichier EXCEL. J'essaierais demain de te fournir du code ...

2. Développer une applet intégrant les bibli Excel et JDBC le tout packagé dans un jar. Le PB étant le recencement de différentes VM des utilisateurs.

Autre solution : En forms 6, regarder du côté des package OLE2
http://www.tek-tips.com/viewthread.c...1155177&page=1
... en forms 9, il y a WEB_UTIL mais .... c'est pas terrible et trés lent à moins d'utiliser le port de l'AS qui n'utilise pas le cache !

CDLT.

**habasque** · 08/01/2007, 21h47

je suis du même avis que toi concernant SQL Loader et les tables externes. c'est fastidieux.
en effet, je dois mettre à disposition de 200 personnes (non informaticiens) une interface leur permettant de charger eux mêmes leurs fichiers. il me faut donc une solution simple d'utilisation.
le client Forms 6i sera déployé sur chaque poste utilisateur.

je faisais référence à Java mais d'autres possibilités peuvent être envisagées...
ceci dit, je pense que la première solution que tu proposes peut correspondre à mes attentes...

si j'ai bien compris, il s'agirait d'une procédure PL/SQL que je pourrais lier à un formulaire forms 6i ?
et cela me donnerait la possibilité d'importer des fichiers Excel ou .txt en précisant simplement l'adresse du fichier et la table cible ?

**taska** · 08/01/2007, 22h13

si j'ai bien compris, il s'agirait d'une procédure PL/SQL que je pourrais lier à un formulaire forms 6i ?

Non, ce n'est pas ce que j'envisageais ... J'envisageais de créer une procédure sur le noyau qui aurait scruté un répertoire accessible sur le serveur et qui aurait remonté en base, le contenu des fichiers excels déposés dans ce répertoire par les utilisateurs.

en précisant simplement l'adresse du fichier et la table cible ?

Dans cette optique, le mieux est d'utiliser un composant Java Bean.. Sous Forms 9I, je sais faire facilement mais sous 6I je n'ais pas essayé ....

.txt

Un peu de code PL/SQl et utilisation sour Forms 6 du package TEXT_IO.

Question :
En moyenne, les fichiers comportent combien de lignes et colonnes ?
.. et doit t'on alimenter des tables différentes ?

CDLT.

**habasque** · 08/01/2007, 23h04

oui on pourrait envisager une solution par laquelle les utilisateurs placeraient leurs fichiers sur le serveur. je pensais plutôt à une exportation des fichiers depuis chaque poste utilisateur mais pourquoi pas les entreposer sur le serveur.
pour info, les fichiers peuvent atteindre des centaines de milliers de lignes, des centaines de colonnes et alimentés plusieurs tables...

cdt,

**taska** · 09/01/2007, 11h29

je pensais plutôt à une exportation des fichiers depuis chaque poste utilisateur

Dans ce cas, le bean semble être le plus approprié et le + simple à mettre en oeuvre.
J'ai effectué un petit test, sur un jeu d'essai de 10.000 lignes en utilisant la librairie :http://www.andykhan.com/jexcelapi/
le résultat parrait satisfaisant :

Start : Tue Jan 09 11:08:36 CET 2007
Nombre de lignes :10000
Nombre de cols :5
End :Tue Jan 09 11:08:37 CET 2007

voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
package com.td;
 
import java.io.File;
import java.util.Date;
 
import jxl.Workbook;
import jxl.Sheet;
 
public class JX 
{
  public JX()
  { log ("Start : " + new Date().toString());
    readFile ("d:\\banque.xls","Feuil1",false);
    log ("End   :" + new Date().toString());
  }
 
  public static void main(String[] args)
  {
    JX jX = new JX();
  }
 
  private void readFile ( String filename, String feuille, boolean affiche ){
 
try{  //Ouverture du fichier
      Workbook workbook = Workbook.getWorkbook(new File(filename));
      //Ouverture de la feuille
      Sheet sheet = workbook.getSheet(feuille);
      log ("Nombre de lignes :" + sheet.getRows());
      log ("Nombre de cols   :" + sheet.getColumns());
      //Lecture des cellules
      for(int i = 0; i < sheet.getRows(); i++){
          for(int j = 0; j < sheet.getColumns(); j++){
               String temp = sheet.getCell(j, i).getContents();
               if (affiche ) { log(temp);} 
              }
      }
      //Fermeture du fichier
      workbook.close();
     }
  catch(Exception ex) { log (ex.toString());}
  }
 
 private void log ( String temp){
  System.out.println (temp);
 }
 
 
}

pour l'insertion en base, j'utiliserais :

Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection();
To access the default connection, write:

DriverManager.getConnection("jdbc:default:connection:");

You can also use the Oracle-specific defaultConnection() method of the OracleDriver class which is generally recommended:

OracleDriver ora = new OracleDriver();
Connection conn = ora.defaultConnection();

CDLT.

**plaineR** · 09/01/2007, 12h40

Attention les beans ne fonctionnent que si tu es en mode web.

Par fichier excel, tu entends .xls ou .csv ?

**habasque** · 09/01/2007, 16h09

je ne comprends pas vraiment la remarque de PLaineR concernant le mode WEB.
je peux développer une interface java connectée à ma base Oracle pour charger les fichiers non ?

par fichier Excel, peu importe qu'il soit en .xls ou en .csv...

**taska** · 09/01/2007, 16h33

... ou bien sur d'autant que tu utilises Forms en version 6I donc en mode WEB !

plaineR préconise sans doute l'utilisation de SqlLoader pour des fichiers CSV mais comme je l'indiquais lors d'un précédent post, la conversion XLS en CSV nécessite une opération de + à réaliser pas les utilisateurs. Sans vouloir remettre en cause la formation de ces utilisateurs à cette opération ....

CDLT.

**plaineR** · 09/01/2007, 16h56

Envoyé par taska

... ou bien sur d'autant que tu utilises Forms en version 6I donc en mode WEB !

Forms 6i fonctionne également en C/S d'où ma remarque

Envoyé par taska

plaineR préconise sans doute l'utilisation de SqlLoader pour des fichiers CSV

Non pas du tout, mais comme vous parliez de text_io, cela m'a interpelé. text_io sous forms 6i ne fonctionne que pour des fichiers textes et non binaires.

Envoyé par taska

La conversion XLS en CSV nécessite une opération de + à réaliser pas les utilisateurs. Sans vouloir remettre en cause la formation de ces utilisateurs à cette opération ....

Pas forcément s'il s'agit d'un fichier d'échange entre applications. J'ai régulièrement des applis qui génèrent des fichiers CSV que l'on doit intégrer dans notre base. Et là aucune action supplémentaire de la part des users. Au vu des volumes des fichiers xls, j'ai pensé que l'on était dans ce cas-là...

**plaineR** · 09/01/2007, 16h58

Envoyé par habasque

je peux développer une interface java connectée à ma base Oracle pour charger les fichiers non ?

Oui, mais j'avais compris que tu voulais intégrer cela directement à forms, c'était juste une mise en garde

**remi4444** · 10/01/2007, 15h19

Envoyé par habasque

pour info, les fichiers peuvent atteindre des centaines de milliers de lignes, des centaines de colonnes et alimentés plusieurs tables...

Il me semble que les fichiiers xls sont limités à 65536 lignes non ?

Pour de très gros volume je crois que les api (java ou autres) ne sont pas adaptées. Pour de telles quantités de données, moi j'attaquerais directement les fonctions bas-niveau coté serveur (Ex: conversion csv / pré-parsing par awk / sql*loader)

**habasque** · 12/01/2007, 01h24

moi j'attaquerais directement les fonctions bas-niveau coté serveur (Ex: conversion csv / pré-parsing par awk / sql*loader)

l'idée de tout traiter du côté serveur avec SQL LOADER est bonne mais nécessite l'intervention d'un informaticien.
le processus doit être accessible par près de 300 personnes !

j'étais donc parti sur l'idée d'une interface Java/Swing avec un FileChooser pour sélectionner le fichier. ensuite, de lire les données via un DataInputStream (dans ce cas la je peux obliger de convertir les fichiers Excel en texte) ou par l'API qui permet de lire des fichiers Excel et de créer les ordres SQL du même coup. ensuite, soit je stocke les ordres SQL en mémoire et j'envoie tout d'un bloc à la BD mais suivant le nombre d'ordres ca peut ramer un peu côté Java. ou bien à chaque ligne lue, j'envoie l'ordre d'insertion correspondant à la BD via JDBC bien sûr.

**plaineR** · 12/01/2007, 09h23

Je suis d'accord avec remi4444, je reste persuadé que pour des traitements de gros volume java n'est pas adapté

A mon avis il aurait été plus judicieux de faire un outil qui transfère le fichier sur le serveur et de lancer SQL*Loader.

**remi4444** · 12/01/2007, 09h52

Envoyé par habasque

l'idée de tout traiter du côté serveur avec SQL LOADER est bonne mais nécessite l'intervention d'un informaticien.
le processus doit être accessible par près de 300 personnes !

j'étais donc parti sur l'idée d'une interface Java/Swing avec un FileChooser pour sélectionner le fichier. ensuite, de lire les données via un DataInputStream (dans ce cas la je peux obliger de convertir les fichiers Excel en texte) ou par l'API qui permet de lire des fichiers Excel et de créer les ordres SQL du même coup. ensuite, soit je stocke les ordres SQL en mémoire et j'envoie tout d'un bloc à la BD mais suivant le nombre d'ordres ca peut ramer un peu côté Java. ou bien à chaque ligne lue, j'envoie l'ordre d'insertion correspondant à la BD via JDBC bien sûr.

On peut automatiser le lancement de sql*loader sur le serveur

Le problème risque d'etre un problème de performance, fais très attention à ça car tu risques de passer du temps à développer une jolie solution qui fonctionnera bien pour des petits fichiers et qui sera inutilisable si tes fichiers deviennent aussi gros que ce que tu as dis dans ton précédent message.

**habasque** · 12/01/2007, 15h37

On peut automatiser le lancement de sql*loader sur le serveur

l'utilisateur peut y avoir accès ? je ne vois pas comment ?

Le problème risque d'etre un problème de performance, fais très attention à ça car tu risques de passer du temps à développer une jolie solution qui fonctionnera bien pour des petits fichiers et qui sera inutilisable si tes fichiers deviennent aussi gros que ce que tu as dis dans ton précédent message.

tu parles de la solution Java ou de SQL LOADER ?

**socrate_man** · 14/03/2007, 12h18

Bonjour,

Envoyé par taska

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
package com.td;
 
import java.io.File;
import java.util.Date;
 
import jxl.Workbook;
import jxl.Sheet;
 
public class JX 
{
  public JX()
  { log ("Start : " + new Date().toString());
    readFile ("d:\\banque.xls","Feuil1",false);
    log ("End   :" + new Date().toString());
  }
 
  public static void main(String[] args)
  {
    JX jX = new JX();
  }
 
  private void readFile ( String filename, String feuille, boolean affiche ){
 
try{  //Ouverture du fichier
      Workbook workbook = Workbook.getWorkbook(new File(filename));
      //Ouverture de la feuille
      Sheet sheet = workbook.getSheet(feuille);
      log ("Nombre de lignes :" + sheet.getRows());
      log ("Nombre de cols   :" + sheet.getColumns());
      //Lecture des cellules
      for(int i = 0; i < sheet.getRows(); i++){
          for(int j = 0; j < sheet.getColumns(); j++){
               String temp = sheet.getCell(j, i).getContents();
               if (affiche ) { log(temp);} 
              }
      }
      //Fermeture du fichier
      workbook.close();
     }
  catch(Exception ex) { log (ex.toString());}
  }
 
 private void log ( String temp){
  System.out.println (temp);
 }
 
 
}

J’ai le même problème que vous, et je crois que cette class fera la faire, mais il me reste de convertir cette class a une PJC a fin de l'utilisé dans Oracle forms 10j.

Si c'est possible de m'aider pour faire ce code, merci d'avance.