Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

It's is possible to read an excel ?

Hello,

My question is: It's is possible to read an excel ?

I have an excel with one column with somes names and I want to make a loop and use this like a variable.

Each loop, my excel have to matches with a list from my FTP:

excel to.png

Ex:

FOR EACH name IN file('name.xlsx')  <- This excel is in local on my computer - It's just an example, I want to know if a function allow to read an excel exists

    LOAD *

     FROM [ftp://AAA:VVV@11.111.11.111/DV/$(name)]

     (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes, table is Sheet1);

     STORE name INTO $(name).qvd(qvd);

NEXT

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

If you used Filelist from above mentioned help-example you didn't need your Name.xlsx. To load your files in a two step logic you need a normal for-loop:

NAME:

LOAD A as name FROM (ooxml, no labels, table is Feuil1);

for i = 1 to fieldvaluecount('name')

     let vName = fieldvalue('name', $(i));

     YourTable:

     LOAD * FROM [ftp://AAA:VVV@11.111.11.111/DV/$(vName)]

     (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes, table is Sheet1);

next

- Marcus

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Yes this is possible. It would be require two steps - at first load the name.xlsx and then loop through the field-values. But in your case it's easier to use Filelist to loop through your folder. Have a look within the help - there is a good example for this.

- Marcus

Highlighted
Partner
Partner

Yes, you can do.

Define Excel as an ODBC connect.

Then you can read the columns from your table by a standard syntax

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Exclefile.xlsx];

SQLCOLUMNS;

Then look to the field column_name, all fields from your excel-sheet are listed

Regards

Highlighted
Not applicable

Thanks for your help!

I tried with a FileList, but when I run the script nothing appear as my QV doesn't read my whole script.

You said Fieldvalues you mean:

For each name in FieldValues('NAME') <- My first load name.xlxs

NAME:

LOAD A as name

FROM

(ooxml, no labels, table is Feuil1);

Regards

Highlighted
Not applicable

Thanks for your help!

I don't really understand how to use what you come to say, but I did this:

NAME:

LOAD A as name

FROM

(ooxml, no labels, table is Feuil1);

FOR Each name in FileList('name.xlsx')

  ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Kenny.Hua\Documents\name.xlsx];

  SQLCOLUMNS;

  LOAD *

  FROM [ftp://AAA:VVV@11.111.11.111/DV/$(name)]

  (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes, table is Sheet1);

NEXT name

Highlighted
MVP & Luminary
MVP & Luminary

If you used Filelist from above mentioned help-example you didn't need your Name.xlsx. To load your files in a two step logic you need a normal for-loop:

NAME:

LOAD A as name FROM (ooxml, no labels, table is Feuil1);

for i = 1 to fieldvaluecount('name')

     let vName = fieldvalue('name', $(i));

     YourTable:

     LOAD * FROM [ftp://AAA:VVV@11.111.11.111/DV/$(vName)]

     (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes, table is Sheet1);

next

- Marcus

View solution in original post