Discussion :

[megi06]

Bonjour à tous.

Je travaille avec firebird 2.5. Utilisateur d'une base de donnée de comptabilité, j'essaie de lui donner la possibilité d'importer les relevés bancaires.

Mon soucis est de les importer dans la base. Ces relevés sont téléchargés au format csv.
Ils sont rectifies manuellement : simplification du libelle, ajout de deux paramètres pour la suite du traitement.
Et..... le plus important, enregistrement sous forme d'un fichier txt non délimité avec les fins de lignes cr lf.
Firebird configuré pour accepter les tables externe, full access.
En attendant la rédaction compléte du code, le test se fait par commande sql au clavier :

insert into vu_essai (vuchamp,fin)
select champ,' ' from essai

Une table essai est crée dans la base pour recevoir les deux champs de la table externe, champ (les informations) et fin (les cr lf) avec une commande insert into.
La suite prévue étant de dispatcher les informations contenues dans champ vers les différents champs de la table définitive.

Et les soucis commencent : la table externe est reconnue, les enregistrements extraits mais d'une façon aléatoire. Au premier essai, le premier enregistrement est bien extrait à partir du premier caractère,les suivants se décalent. Si je vide cette table, un nouvel essai n'obtient pas le même résultat, décalages complétement aléatoires.

Je suppose que le soucis vient de la structure du txt, dont les enregistrements sont pourtant bien en varchar et de longueur constante. Peut-être aussi de certains paramètres que j'ai omis.
Idem sur deux machines différentes, autant avec Ibexpert qu' Ibconsole.
Pour info, il n'y a que ce point qui coince, le traitement est écrit et fonctionne.
Merci de votre aide

[megi06]

On avance, essai est lu d'une manière stable,mais à partir du 3 éme caractère et un seul enregistrement est lu alors qu' il en comporte deux.

La base est character set NONE, le fichier txt est UTF-8 (notepad++)

La différence avec les premiers essais est qu'il n'y a plus d'espaces dans les enregistrements de la table externe essai, remplacés par - ou _.

[SQLpro]

L'UTF8 est une vaste merde dont les développeurs ont gobés les miracles invoqué par les anglo-saxons. Il encode les caractères à raison :
d'un octet pour le jeu ASCII 7 bits (les lettres non diacritique et chiffre), deux octets pour les lettres diacritiques et les alphabets indo-européens, 3 octets pour les alphabets idéographique et 4 pour le reste...

Si votre SGBDR ne permet pas l'intégration de ce jeu de caractères particulièrement débile (et fait pour assurer l'hégémonie de la langaue anglaise), alors il va y avoir des décalages du fait de cet encodage à pas multiple...

Transformez votre fichier dans un encodage à pas fixe (genre ASCII ou UNICODE).

A +

[Michel.Priori]

Bonsoir,

La différence avec les premiers essais est qu'il n'y a plus d'espaces dans les enregistrements de la table externe essai, remplacés par - ou _.

Si je comprends bien vous essayez d'intégrer un fichier "à largeur fixe"
Et vous avez remplacé les espaces par des caractères visibles ('-', '_') afin d'être sûr de ne être en face d'autres caractères comme tabulation

Est-ce bien ça ?

[Artemus24]

Salut à tous.

Je pense que vous ne savez pas ce qu'est un champs "varchar" dans un fichier séquentiel.
Ce champs doit être précédé par la longueur de ce qui va suivre, en binaire bien sûr.
La ligne doit être de longueur fixe et se terminer par "cr" et "lf".

Si j'ai bien compris votre problème, ce fichier vient d'Excel et ce formatage est de type ".csv".
Donc, aucun rapport avec ce que je viens de dire précédemment.
D'où le problème que vous rencontrez pour lire et charger vos champs dans les colonnes de la table.

Voici le fichier "import" de mon exemple :

Code :

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

1
2
3
nombre;un;deux;trois;quatre;cinq;six    
couleur;rouge;vert;jaune                
alphabet;alpha;beta;gamma;delta

La longueur est de 40 caractères, et après vous avez "cr"+"lf".

Et voici le script qui permet de charger ce fichier "import" :

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
-- =====================
-- Création table 'test'
-- =====================
 
CREATE TABLE test
( id    smallint    generated by default as identity not null primary key,
  clef  varchar(10) not null,
  ind   smallint    not null,
  elem  varchar(06) not null
);
 
-- ===============================
-- Création table externe 'Import'
-- ===============================
 
CREATE TABLE Import external 'E:\31.FireBird\20.Basic\20.Traitement\04.Table_Externe\Ex_03\01.Import.txt'
(  libelle char(40),
   CRLF    char(02)
);
 
-- =====================
-- Vidage table 'Import'
-- =====================
 
select libelle from Import;
 
LIBELLE
========================================
nombre;un;deux;trois;quatre;cinq;six
couleur;rouge;vert;jaune
alphabet;alpha;beta;gamma;delta
 
 
-- ============================
-- Création Procédure 'remplir'
-- ============================
 
set term $$ ;
 
create procedure remplir
as
  declare clef    varchar(10)  default null;
  declare ind     smallint     default 0;
  declare elem    varchar(06)  default null;
 
  declare newpos  integer      default 0;
  declare oldpos  integer      default 0;
  declare token   char(01)     default ';';
  declare result  char(255)    default null;
  declare input   char(40)     default null;
  declare arret   integer      default 0;
 
  declare tab cursor for (select libelle from Import);
begin
  open tab;
 
  fetch tab into input;
 
  while (row_count > 0) do
  begin
    clef   = null;
    ind    = 1;
    elem   = null;
 
    oldpos = 1;
    newpos = 1;
    arret  = 0;
 
    while (arret=0) do
    begin
      newpos = position(token, input, oldpos);
 
      if (newpos > 0) then
      begin
        result = substring(input from oldpos for newpos - oldpos);
        oldpos = newpos + 1;
      end
      else
      begin
        result = substring(input from oldpos);
        arret = 1;
      end
 
      if (clef is null) then clef = trim(cast(result as varchar(255)));
      else
      begin
        elem = trim(cast(result as varchar(255)));
        insert into test (clef,ind,elem) values (:clef,:ind,:elem);
        ind = ind + 1;
      end
    end
 
    fetch tab into input;
  end
 
  close tab;
end$$
 
set term ; $$
 
-- =============================
-- Exécution Procédure 'remplir'
-- =============================
 
execute procedure remplir;
commit;
 
-- =========
-- Nettoyage
-- =========
 
drop procedure remplir;
drop table Import;
 
-- ===================
-- Vidage Table 'Test'
-- ===================
 
select * from test;
 
     ID CLEF           IND ELEM
======= ========== ======= ======
      1 nombre           1 un
      2 nombre           2 deux
      3 nombre           3 trois
      4 nombre           4 quatre
      5 nombre           5 cinq
      6 nombre           6 six
      7 couleur          1 rouge
      8 couleur          2 vert
      9 couleur          3 jaune
     10 alphabet         1 alpha
     11 alphabet         2 beta
     12 alphabet         3 gamma
     13 alphabet         4 delta
 
 
exit;
 
Appuyez sur une touche pour continuer...

On retrouve cette longueur dans la colonne "libelle" qui est aussi de 40 caractères.
Comme je suis sous windows, j'ai bien en fin de ligne "cr" et "lf" que je dois tenir compte lors de chaque lecture de lignes.
Ma base de données utilise le charset "Win1252", dont chaque caractère à une longueur fixe.

Vous ne pouvez pas faire autrement d'utiliser une procédure pour découper et charger chaque champs dans une colonne de votre table.

Si vous avez la possibilité de créer un fichier import en tenant compte de ce que j'ai dit en premier, le script de chargement de la table devient bien plus simple.

[megi06]

Merci à tous de vos réponses.
Dans l'intervalle, j'ai réglé le soucis, empiriquement. Globalement suivant la démarche que détaille Artémus24.

Mais il reste des interrogations.

L’environnement est le suivant : W8 ou XP (apparemment sans influence), Firebird 2.5, connexion avec Ibconsole, base.ib, character set None.
Pour le fichier externe, csv d'origine, il est préparé avec Hexedit ou Notepad++, ce dernier, plus pratique. Les deux proposent la conversion de l'encodage.
Cependant, pas de différence entre Ansi et Utf8. Mais, si l'on choisi Utf8-BOM, les BOM apparaissent.
Conclusion : Ascii, Ansi, Utf8, il faut rajouter à l'enregistrement deux caractères muets :

EE-------les caractères utiles------crlf ; donc le fichier comprend x caractères utiles + 4 (EE) et CRLF, et la lecture du caractère 1 utile, à la position 1 (dans un substring par exemple) commence au 3 !

J'en déduis que les BOM ne sont pas là, mais que la machine en tient compte quand même et lit à partir de 3

Si l'on converti en Utf8_BOM, les BOM apparaissent et mes EE deviennent inutiles pour que la lecture du 1 commence effectivement au 1 utile.

En attendant, un article TRÈS instructif pour ceux qui manquent de théorie : https://www.joelonsoftware.com/2003/...ts-no-excuses/
Je n'ai pas les connaissances nécessaires pour en faire la critique avancée, mais il me semble une bonne base.

Pour moi, cette affaire est résolue, merci de vos interventions.

[SergioMaster]

Bonjour,

je n'interviens que maintenant car je suis devenu plus dilettante.

Je n'ai jamais eu à faire avec des tables externes ou si peu que je ne m'en souviens plus et ce depuis le mes débuts avec Interbase (5.2) puis Firebird 1.5 -> 5

Par contre, j'aimerais relever certaines choses (hélas commune)

• L'encodage de la base de données NONE a toujours été déconseillée (comme Artemus j'utiliserai "Win1252" pour une comptabilité de PME)
• L'utilisation d'IBconsole, produit pour Interbase, m'interpelle aussi (cela sent une migration d'interbase vers Firebird comme j'ai pu la faire il y a des années) si vous voulez un outil GUI pour Firebird utilisez plutôt un outil adapté. Je suis un fan de Flamerobin, au demeurant gratuit, quant il s'agit de tester des scripts. D'autres GUI plus "lourd" (i.e Beaver) permettent eux d'obtenir plus quant il s'agit de design de BD.
• Enfin, point important, je signale que la version 2.5 n'est plus vraiment maintenue, même si le dernier fix de sécurité n'est pas si vieux (2.5.9 mais je ne suis pas sûr de la date du dernier patch) on en est désormais à la version 5.

Enfin je suis toujours resté frileux sur l'utilisation de tables externes préférant un bon vieux programme permettant de traiter les données, mais ça c'est mon pointe de vue de vieux

[alanglet]

Bonjour,

J'ai aussi utilisé il y a longtemps des tables externes pour éviter de programmer une appli particulière permettant d'intégrer dans une base les données d'un fichier csv.
De mémoire une table externe est une table dont tous les champs d'un enregistrement sont des chaînes de longueur fixe, sans séparateurs, et se terminant par CRLF (au moins sous windows).
Ces champs doivent donc tous être déclarés comme des CHAR(x) où chaque chaîne est complétée par des espaces si nécessaire, sans oublier le CHAR(2) pour le CRLF
Avec un bon tableur il est relativement facile de charger le fichier csv, puis de créer et exporter ce type de fichier.
Travaillant sous Windows en France, il est relativement logique d'utiliser les caractères WIN1252.
Mais je n'aurai pas les emportements de SQLpro contre l'UTF8. Je n'ai pas eu l'occasion de tester, mais il n'est pas impossible que cela fonctionne aussi avec des champs en UTF8, car dans ce cas Firebird alloue toujours 4 octets par caractères. Un champ de type CHAR(x) a toujours une longueur de 4*x, donc on retrouve bien des enregistrements de longueur constante. A tester à l'occasion.

André

[alanglet]

Un petit complément après consultation de la doc: les champs d'un enregistrement d'une table externe, ne sont pas obligatoirement du type CHAR(). Ils peuvent aussi être d'un type de taille fixe comme Integer, BigInt... Mais en général, si les données sont fournies dans un fichier csv, c'est presque toujours des champs CHAR qui sont utilisés.

André

[Artemus24]

Un fichier externe peut gérer n'importe qu'elle structure. Le tout est de bien les déclarer afin de ne pas avoir des problèmes sous FireBird.
Peut-être pour des mises à jour quotidiennes, mais cela va dépendre de la souplesse d'utilisation.
Je ne vois rien de contraignant dans leur usage.

[SergioMaster]

[*]L'encodage de la base de données NONE a toujours été déconseillée (comme Artemus j'utiliserai "Win1252" pour une comptabilité de PME)

Au cas où l'envie vous prendrait, j'ai rédigé un petit billet sur le process que j'ai utilisé pour transformer un vieille base de données (originellement Interbase 5.2 et au moment de la migration FB 2.5 )
https://www.developpez.net/forums/bl...sql-dialect-1/