topic Re: It's is possible to read an excel ? in QlikView

It's is possible to read an excel ?

Mon, 13 Apr 2015 13:23:48 GMT

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:

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

Re: It's is possible to read an excel ?

marcus_sommer — Mon, 13 Apr 2015 14:34:51 GMT

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

Re: It's is possible to read an excel ?

martinpohl — Mon, 13 Apr 2015 14:38:42 GMT

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

Re: It's is possible to read an excel ?

Mon, 13 Apr 2015 14:57:09 GMT

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

Re: It's is possible to read an excel ?

Mon, 13 Apr 2015 15:07:15 GMT

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

Re: It's is possible to read an excel ?

marcus_sommer — Mon, 13 Apr 2015 15:29:58 GMT

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);

- Marcus