query from excell

[amiroh81]

Hi

I try to write query in SQL and to add condition from XLS file.

for example :

select *

from id_table

where id in (load id from excell)

what is the right syntax?

thanks in advaned

[#2]

Hi,

Try this:

MyTable:

LOAD

     A,

     B,

     C

FROM

<FilePath\File.xlsx>

(ooxml, no labels, table is Page1)

Where upper(B) = 'ADMIN';

[#3]

You can't use select command for excel data[flat files].

Load *

resident id_table

where id exists(id,id)

[its_anandrjs]

No this way is not possible you have to use where exists

Example:-

ExcelTab:

LOAD * Inline [

ID,Amt

A,203

B,304

C,105  ];

Id_Table:

LOAD * Inline  [

ID,Value

A,20

B,30

D,45

E,50  ] Where Exists(ID,ID);

Drop table ExcelTab; //And do not forget to drop this table.

Regards

Anand

[PrashantSangle]

Hi,

Try like

1st load you excell data

then load data from SQL

Like

Load ID

from Excel

Left join

Load * from id_table;

Regards

[simenkg]

First load the ID's you want to load from the excel file.

ValidIDs

Load ID as ValidIDs from Excel.xls [...];

Then you load the SQL

Id_Table:

Load * where Exists(ValidIDs,ID);
SQL Select * from id_table;

Drop table ValidIDs;

[amiroh81]

What I mean is that I want to query in SQL when filtering operations subject which comes from Excel file.

For example, a list of names existed in an Excel file and I would like to be shown to me only those names from the all names.

the problem with the max dreamer and bwisenoSimenKG Suggestion is that i need to run the all table and its very big.

thanks very much

[its_anandrjs]

For your example

ExcelTab:

Load ID From Excel Source;

MainTab:

select *

from id_table Where(ID,id);

Drop Table ExcelTab; //Do not forget to drop.

Regards

Anand

[tombombadil]

Hi,

if there are not many names/ids from the Excel file, you could generate a string to filter the values directly in the WHERE clause of your SQL statement.

Like:

// You need to generate the string dyanmically from the Excel data

SET $FilterString="'ABC','BCD','DEF'";

table:

LOAD *;

SQL SELECT *

FROM id_table

WHERE ID IN ($(FilterString));

[anbu1984]

ID:

LOad Concat(Chr(39) & ID & Chr(39),',') As ID;

LOAD ID

FROM

[File.xlsx]

Let vId=Peek('ID',0,'ID');

Sql Select * from Table where ID in $(vId);