Scripting Help

[arusanah]

I have a user Login data coming every month in excel File . Since the #of Logins vary every month, the data gets spread out in multiple Sheets accordingly.  Concatenate in load script doesn't work since sheet count varies every month. I tried using following code but it doesn't work -- it gets into endless loop . Can anybody help me figure out missing piece of code

set errormode = 0; 
for i <=7

LOAD [User ID]as [PEFAC User ID],
     [County Code] as [PEFAC County Code],
       trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],
     MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,
     [Login Date]
FROM
[$(qvwLocation)\Source\PEFAC_Login*.xlsx]
(ooxml, embedded labels , table is Sheet $(i));

exit script;

[sunny_talwar]

Try this:

set errormode = 0; 
FOR i = 1 to 7

LOAD [User ID]as [PEFAC User ID],
     [County Code] as [PEFAC County Code],
       trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],
     MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,
     [Login Date]
FROM
[$(qvwLocation)\Source\PEFAC_Login*.xlsx]
(ooxml, embedded labels , table is Sheet$(i));

NEXT i

exit script;

[its_anandrjs]

How you know there is 7 sheets on the excel file.

Regards

Anand

[jpenuliar]

try something similar to this :

Load data from multiple worksheets in multiple Excel workbooks

[its_anandrjs]

One way you can try is last number for the for loop entered maximum number say you have max 20 sheets on the excel file.

set errormode = 0; 

FOR i = 1 to 20

LOAD [User ID]as [PEFAC User ID],

     [County Code] as [PEFAC County Code],

       trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],

     MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,

     [Login Date]

FROM

[$(qvwLocation)\Source\PEFAC_Login*.xlsx]

(ooxml, embedded labels , table is Sheet $(i));

NEXT i

Regards

Anand