Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try this:
MyTable:
LOAD
A,
B,
C
FROM
<FilePath\File.xlsx>
(ooxml, no labels, table is Page1)
Where upper(B) = 'ADMIN';
You can't use select command for excel data[flat files].
Load *
resident id_table
where id exists(id,id)
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
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
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;
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
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
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));
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);