It's is possible to read an excel ?

[#1]

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

[marcus_sommer]

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

[martinpohl]

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

[#4]

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

[#5]

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

[marcus_sommer · Accepted solution]

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