Load from URL (results excell with multiple sheets)

[#1]

Hi,

I need to load data from a URL that results in a excel file with multiple sheets

https://www.keysurvey.com/servlet/ExportResults?SurveyID=682896&ReportID=623016&pw=29f37d09&exportTo...

Each row holds the answer of a person. The code currently has only brings the information from the first sheet, but I'm missing the responses of sheets 2, 3, and 4.

CUESTIONARIO_RESPUESTAS_TMP: //:: Se extraen las respuestas de cada archivo fuente

  CrossTable(SUBPREGUNTAS, RESPUESTAS, 4)

  LOAD '$(vMODULO)' AS MODULO, '$(vURL)' as URL, '$(vIdCatalogo)' as ID_CATALOGO, *

  FROM [$(vURL)]

  (biff, embedded labels, header is 2 lines, table is [Results$], filters(

  Remove(Col, Pos(Top, 12)), Remove(Col, Pos(Top, 11)), Remove(Col, Pos(Top, 10)), Remove(Col, Pos(Top, 9)),

  Remove(Col, Pos(Top, 8)),  Remove(Col, Pos(Top, 7)), Remove(Col, Pos(Top, 6)), Remove(Col, Pos(Top, 5)),

  Remove(Col, Pos(Top, 4)), Remove(Col, Pos(Top, 3)),  Remove(Col, Pos(Top, 2)), Remove(Col, Pos(Top, 1)),

  Transpose(), Replace(1, top, StrCnd(contain, 'COMENTARIOS', case)),

  Replace(1, top, StrCnd(contain, 'COMENTÁRIOS', case)), Replace(1, top, StrCnd(null)),

  Replace(2, left, StrCnd(null)),

  Transpose(), Remove(Row, Pos(Top, 1)) //:: Transformaciones de la fuente para extraer con una estructura predeterminada

  ));

Greetings.

[marcus_sommer]

For a normal filename you could use a wildcard to loop through severals files but this didn't worked with sheets inside a xls. Therefore you need some loop-logic for the sheets - have a look on these example: Load Multiple excel sheets using For loop

- Marcus

[noman212]

For each vtxtPath in filelist ('*.txt')

TextFile:

LOAD Dept.,

     [User ID],

         Name,

     [Enroll ID] ,

     [Device ID],

     Place,

     [Clock Time],

     [Att. Type],

     Remark

FROM

$(vtxtPath)

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Next vtxtPath;