Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to load data from a URL that results in a excel file with multiple sheets
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.
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
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;