How to load xls file into variable

[#1]

HI,

I read a lot of case on this forum to load data into variable.

But my case is not working. I think it is very simple but ... -)

So. I have a XLS file with 3 colums :

Annee	Mois	DateSel
2011	6	20110615

I load this file like this :

SET vAnnee = null();
param : 
LOAD Annee as Annee_sel,
     Mois as Mois_sel,
     DateSel as Date_sel
FROM
H:\Cofidis\JVE\param_EASI.xls
(ooxml, embedded labels, table is Feuil1);
;;

Then , do this

LET vAnnee = num(PEEK('Annee_Sel', 0, 'param'));

And

COMPTA :
LOAD      
           MNGSOCCD,
           MNGPOSCD,
           DCEXPRCD as PERIODE,
           DOCPERCD as MOIS,
           DOCEXECD as ANNEE,
           JRNCODCD as JOURNAL,
           DOCNUMCD as PIECE,
           DOCLINCD as LIGNE,
           DOCDATCD, 
           JRNTYPCD as JOURNAL_TYPE,
             GENACCCD as COMPTE,
           CRLCCUCD - DELCCUCD as MONTANT,
           ANAACCCD,
           FLRREFCD,
             COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD as DESCRIPTION,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD ,
           BU
                ;
SQL SELECT MNGSOCCD,
           MNGPOSCD,
           case when MNGPOSCD = 1 then 'Gestion' else 'Compta' end as BU,
           DCEXPRCD,
           DOCPERCD,
           DOCEXECD,
           JRNCODCD,
           DOCNUMCD,
           DOCLINCD,
           DOCDATCD, 
           JRNTYPCD,
           GENACCCD,
           DELCCUCD ,
           CRLCCUCD,
           ANAACCCD,
           FLRREFCD,
           COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD 
           from 
           CMOUVEDT 
where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)
and DOCEXECD = $(vAnnee) and DOCPERCD = 06
;

on load i have this message

SQL##f - SqlState: 37000, ErrorCode: 156, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntaxe incorrecte vers le mot clé 'and'.
SQL SELECT MNGSOCCD,
           MNGPOSCD,
           case when MNGPOSCD = 1 then 'Gestion' else 'Compta' end as BU,
           DCEXPRCD,
           DOCPERCD,
           DOCEXECD,
           JRNCODCD,
           DOCNUMCD,
           DOCLINCD,
           DOCDATCD, 
           JRNTYPCD,
           GENACCCD,
           DELCCUCD ,
           CRLCCUCD,
           ANAACCCD,
           FLRREFCD,
           COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD 
           from 
           CMOUVEDT 
where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)
and DOCEXECD =  and DOCPERCD = 06

The $(vannee) is NULL.

What's the problem ?

Thx.

[#2 · Accepted solution]

Hi!

Field names are case sensitive. The field you're trying to lookup is 'Annee_sel', but you're doing the Peek on 'Annee_Sel'.

That will cause the Peek to return Null.

Brian

[IAMDV]

Your field name - Annee_sel is in wrong case.

That should work.

Cheers - DV

[#4]

Work great.

Thx.

[#5]

Hello,

in addition to the posts above.

For testing (and shorten waiting-time) you can use the trace - command like this:

TRACE vAnnee: $(vAnnee);

Regards, Roland