Intégration d'un fichier plat dans la base

[fastlane3000]

Bonjour!

j'ai des fichiers plats de 200 Mo chacun et qui contiennent des champs que j'aimerais intégrer, voilà je suis sous informix v10 avec un OS AIX 5.3.

les champs en questions n'ont pas de délimiteurs particuliers il y a quelques blancs mais seulement entre un ou de plusieurs datas et la séquence se répète jusqu'à la fin du fichier par contre leur position s'incrémente d'une manière régulière avec une longueur fixe (par exemple un champ1 a la position 1-3 51-53 101-103.... etc notez que l'itération est de 50)

ce qui donne quelque chose comme ça

champ1champ2ESPACEchamp3champ4champ5ESPACEchamp6ESPACEESPACEchamp1champ2ESPACEchamp3champ4champ5ESPACEchamp6ESPACEESPACEchamp1...etc

je veux avoir toutes les occurrences de champ1,...champ6 dans une table (chp1,chp2,chp3,chp4,chp5,chp6)
ie occurrences de champ1 dans chp1
occurrences de champ2 dans chp2
......etc

avec conversion de certains champs genre champ2 de char(6) sous forme "aammjj" à date "mm/jj/aaaa" et champ4 de char(12) à décimal(12,2)

j'ai posé la question sur la section "AIX" si je pouvais changer le format de mon fichier afin de créer un fichier
.unl
mais je me demandais s'il y avais un moyen de faire ça directement par SQL et Informix puisque mon but final est une integration vers ma base

Merci d'avance pour vos réponses

[IFA2377]

Bonjour,

Pour te constituer un fichier de type « .unl », il y a deux problèmes :

1. Reconstituer un fichier d’items de 50 caractères à loader dans une table d’un seul champ char(50).

Ces fichiers ne seraient-ils pas issus du monde micro ? Si c’est le cas, il doit être possible de recréer des fichiers constitués d’items de 50 caractères. Le transfert par FTP n’a peut-être pas été réalisé comme il aurait fallu. Sur AIX, la commande « d2x » permet de transformer un fichier « DOS » en fichier « Unix ». Il doit y avoir un problème LF/CR-LF.

2. Exécuter une commande SQL sur cette table pour unloder un fichier avec des séparateurs « pipe ».

Il est aussi possible de traiter d’abord ces fichiers en format « texte » sur PC tout simplement avec Excel.

a. Ouvrir le fichier « texte » et déterminer la largeur des colonnes
b. Ajout d’une colonne « pipe » après chaque colonne
c. Affecter aux cellules de la colonne « numérique » char(12) le format numérique avec deux décimales
d. Sauvegarde en « .txt »
e. Transfert vers l’AIX
f. Traitement du champ date, etc.

[fastlane3000]

Bonjour,

merci pour la réponse

Envoyé par IFA2377

1. Reconstituer un fichier d’items de 50 caractères à loader dans une table d’un seul champ char(50).

c'est justement ce que mon patron m'a conseillé de faire, mais j'ai trouvé mieux

Envoyé par IFA2377

Ces fichiers ne seraient-ils pas issus du monde micro ? Si c’est le cas, il doit être possible de recréer des fichiers constitués d’items de 50 caractères. Le transfert par FTP n’a peut-être pas été réalisé comme il aurait fallu. Sur AIX, la commande « d2x » permet de transformer un fichier « DOS » en fichier « Unix ». Il doit y avoir un problème LF/CR-LF.

en fait c'est des fichiers copiés à partir de sauvegardes sur bande magnétique, je suis sur un projet de mise en place d'un serveur d'archivage et on s'occupe de récupérer ces données afin de pouvoir les stocker sur une base Informix

Envoyé par IFA2377

2. Exécuter une commande SQL sur cette table pour unloder un fichier avec des séparateurs « pipe ».

c'est justement mon but et c'est pour cette raison que je cherche à créer un UNL

Envoyé par IFA2377

Il est aussi possible de traiter d’abord ces fichiers en format « texte » sur PC tout simplement avec Excel.
a. Ouvrir le fichier « texte » et déterminer la largeur des colonnes
b. Ajout d’une colonne « pipe » après chaque colonne
c. Affecter aux cellules de la colonne « numérique » char(12) le format numérique avec deux décimales
d. Sauvegarde en « .txt »
e. Transfert vers l’AIX
f. Traitement du champ date, etc.

en fait c'est comme qu'on fais actuellement au boulot, mais là j'ai des fichiers annuels qui font dans les 200Mo et le traitement avec Excel est vraiment pénible si ce n'est quasiment infaisable car ça fait planté le PC.

En réalité depuis la dernière fois j'ai trouvé une solution d'abord le découpage de la ligne par 50 caracteres avec "dd"

Code :

dd cbs=50 ibs=50 obs=50 IF=fichier_a_traiter ofs=fichier_decoupe conv=unblock

et c'est plutot rapide, il traite un fichier avec un peu moins d'une minute !
Ensuite la transformation de fichier_decoupe en fichier.unl :
là j'ai utilisé awk avec sa sous-fonction "substr" pour inserer les pipes en rajoutant une conversion de type, il me manque encore le traitement des lignes erronées

Code :

awk -f trans_unl.awk fichier_decoupe > fichier.unl

Voilà dès que j'aurai complété mon shell "awk" je pourrais charger ma table avec un "load" ou "dbload"

Merci pour tout IFA2377

[IFA2377]

Bonjour,

Et ton champ date ? Avec l'année sur deux chiffres. Au load, Informix va les considérer "1900". Non ?

Si tu rencontres le problème, j'ai un petit SQL qui rétablit ça. Mais peut-être n'as-tu que des dates "2000".

[fastlane3000]

Envoyé par IFA2377

Bonjour,

Et ton champ date ? Avec l'année sur deux chiffres. Au load, Informix va les considérer "1900". Non ?

Si tu rencontres le problème, j'ai un petit SQL qui rétablit ça. Mais peut-être n'as-tu que des dates "2000".

salut,

en fait dans mon script trans_unl.awk j'ai défini des fonctions
pour la date c'est en gros comme ceci que j'ai proceder
mon champ date est sous la forme aammjj

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
#! /usr/bin/awk
{
.....
an=substr($0,position_an,2)
mois=substr($0,position_mois,2)
jour=substr($0,position_jour,2)
an=conv_an_de_2_a_4(an)
print ......jour"/"mois"/"an.....
...
}
....
FUNCTION conv_an_de_2_a_4(s){ 
IF (s>=70) {s="19"s} # si année après 1970 (j'ai pas de fichiers anterieur à 1970
else {s="20"s} ; # pour les années apres 2000 (c'est vrai que ça peut aller qu'en 2069)
RETURN s}
......
}

voilà mais si t'as des années qui vont jusqu'en 1960 tu choisis de comparer avec 60 enfin c'est selon tes fichiers
je rajoute juste qu'avec awk la concaténation se simplement en mettant deux valeur l'une à côté de l'autre.

sinon si tu veux faire la conversion avec informix il y a la fonction to_date (que je n'ai pas réussi à faire marcher et vu que j'ai trouvé une meilleure solution )

si tu veux il y a le site officiel d'IBM pour les fonctions temporelles:

http://publib.boulder.ibm.com/infoce...c/sqls1108.htm

mais je crois savoir que pour une année sur 2 position c'est %iY au lieu de %Y qui est utilisé pour "aaaa"

[begooden-it]

Bonjour,

un peu tard une fois de plus, mais je viens proposer des solutions un peu plus officielles et simples.

Si tu es en IDS < 11.50xC6, il existe depuis la première version d'Informix un utilitaire qui s'appelle dbload, et qui est complètement fait pour ce que tu veux faire.
dbload permet de traiter des fichiers ASCII
* soit en format fixe: exactement comme tu veux faire: champ1 est représenté par la position de 1 à 15 par exemple, le champ 2 position 18 à 23 etc....
* soit en format délimité, par le caractère que tu lui indiqueras ( | par défaut).

L'utilisation du format fixe est très versatile, avec notamment possibilité d'effectuer des recouvrements de positions ( ex champ1 pos 1à15, champ 2 pos 3à7), et ton script est très facile à maintenir.
L'autre avantage de dbload est la rapidité: il utilise des CURSOR FOR INSERT qui bufferisent les données au niveau du client.
Autre avantage est le contrôle des longues transactions: tu peux limiter de nombre d'INSERTS par commit work, évitant ainsi d' "attraper" une longue transaction au bout de 15mn ou plus.

Si tu es en IDS >=11.50 UC6, utilise les tables externes, fonctionnalité provenant de Informix XPS, serveur de Data WareHouse. Tu crées une "external table"qui mappe ton fichier ascii avec la description que tu lui fourniras, comme tu l'aurais fait avec dbload.
Avantages:
* tu manipule ton fichier avec du sql, c'est devenu une table de ta base.
* la performance: il existe un mode ( express) où les INSERT sont exécutés en direct, sans passer par les BUFFERS de Shared Memory: tu atteins alors la performance d'un data loader pour Datawarehouse. sympa non?
cherche sur le net "informix CREATE EXTERNAL TABLE"

Eric