Lecture fichier texte

[DTL]

Bonjour

Je souhaite remettre sous forme d'une table avec des champs A,B,C,D un fichier texte qui est structuré comme indiqué ci-dessous:

NomChampA

NomChampB

NomChampC

NomChampD

Valeur A1

Valeur B1

Valeur C1

Valeur D1

Valeur A2

Valeur B2

Valeur C2

Valeur D2

Valeur A3

etc...

Quelqu'un aurait-il une solution générique ou la valeur de "pas" (<=>nb de champs) pourrait être n'importe quel entier entre 2 et N ?

Merci d'avance

[Daniel_Castella · Accepted solution]

Hi  @DTL 

I'm not sure if there is a generic way to do it, but if you know the number of fields and how many values there are for each, you can use the following code to obtain a table with the field names and its values:

LET N = 4; //number of fields

LET M = 2; //values per field

A:

LOAD Field

INLINE [

Field

NomChampA

NomChampB

NomChampC

NomChampD

Valeur A1

Valeur B1

Valeur C1

Valeur D1

Valeur A2

Valeur B2

Valeur C2

Valeur D2

];

FOR i=0 to $(N)-1

LET vfieldname = peek('Field',i,'A');

FOR j= 0 to $(M)-1

LET vfieldvalue = peek('Field',i+(j+1)*$(N),'A');

B_$(i): 

LOAD '$(vfieldvalue)' as $(vfieldname),

'$(j)' as Link

AutoGenerate(1);

NEXT j;

NEXT i;

FOR i=1 to $(N)-1

INNER JOIN(B_0)

LOAD *

RESIDENT B_$(i);

DROP TABLE B_$(i);

NEXT i;

DROP TABLE A;

DROP FIELD Link;

I hope this helps you.

Kind Regards

Daniel

[marcus_sommer]

Beside the provided loop-approach you could match the belonging values to a dedicated record, aggregating the data on this level and then distributing them to fields, for example per:

t1: load YourField, ceil(recno() / 4) as Record, recno() as RecNo
     from X;

t2: load concat(YourField, '|', RecNo) as FieldString
     resident t1 where Record > 1 group by Record;

t3: load subfield(FieldString, '|', 1) as Field1,
             subfield(FieldString, '|', 2) as Field2,
             ...
resident t2;

If the data contain an unknown number of fields and even more worse different numbers of fields per record the detection-logic might be need some interrecord-functions, for example:

t1: load YourField,
             if(mid(YourField, n, n) = mid(previous(YourField), n, n),
                rangesum(peek('Record'), 1), 1) as Record
     from X;

whereby the mid() is just a placeholder for a valid differentiating-pattern between values from the same record and the next ones. With real data it's probably a bit more complex - just take it as starting point ...

[MarcoWedel]

Salut DTL,

another solution could be 

SET vNbChamp = 4;

tabFichierTexte:
LOAD @1 as Ligne Inline [
NomChampA
NomChampB
NomChampC
NomChampD
Valeur A1
Valeur B1
Valeur C1
Valeur D1
Valeur A2
Valeur B2
Valeur C2
Valeur D2
Valeur A3
Valeur B3
Valeur C3
Valeur D3
Valeur A4
Valeur B4
Valeur C4
Valeur D4
] (no labels);

mapNomChamp:
Mapping
LOAD 'Champ'&RecNo(),
     Ligne
Resident tabFichierTexte
Where RecNo() <= $(vNbChamp);

tabResult:
Generic
LOAD Div(RecNo()-1,$(vNbChamp)) as IdEnreg,
     'Champ'&(Mod(RecNo()-1,$(vNbChamp))+1),
     Ligne
Resident tabFichierTexte
Where RecNo() > $(vNbChamp);     

DROP Table tabFichierTexte;
RENAME Fields using mapNomChamp;

optionally combining the resulting tables into one like this:

tabResultComb:
LOAD '' as TempField AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabResult.*') THEN
    JOIN (tabResultComb) LOAD * RESIDENT [$(vTable)];
    DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Field TempField;

hope this helps

Marco