Script use Excel List to SQL resquest

[#1]

Hello,

I don't if it's possible...

I have an Excel file like this :

     Num_Customer
     0123
     5156
     9656

     ...

1. I want load this file in my script  --> OK

(using : LOAD Num_Customer FROM [path:excel_file.xls] (biff, embedded labels, table is feuil1$);

2. Use it in SQL request like : --> KO

Name_TABLE : SELECT * FROM SQL_table_Name WHERE Customer IN ( MY_CUSTOMER_IN_EXCEL_LIST)

I know it's possible to use 'Entry field' (using : '0123', '5156','9656')

Somebody to help ?? Thanks.

[Qrishna]

Sorry.

do you mean, you only want to load the values which exists in num_customer?

[#3]

Hi,

Yes,  tables in my datawehouse have more than 100.000.000 lines, and users want just see around 100 customers.

I don't want load all lines from my database... 
So, i have an excel file with customer (can change often)... and i want use this file to load only the good line.  

[Qrishna]

oh got it. then try this:

CustomersFromExcel:

Load Disitinct

CustomerID,

OtherCustomerFieldList

from Customer.excel;

Data:

load *,CustID

from xyz

where exists(CustomerID,CustID);

drop table CustomersFromExcel;

[#5]

Sorry,

it seems not work, for information my database is on Sql Server

[Qrishna]

that was a script format .

You have to edit that according your connections and tables.

if you have no idea,please post the table names and their fields.

[#7]

I have an error : impossible open file 'c:\........\my_table'  ...

[#8]

EXCEL_LIST :

LOAD TEXT(TOTO) as TOTO_1
FROM

(biff, embedded labels, table is feuil1$);


DATA :
load * from dwh.dbo.my_table where exists(TOTO_1,TOTO_ID)

[Qrishna]

try this way:

YourCustomerTable:

LOAD Num_Customer

FROM [path:excel_file.xls]

(biff, embedded labels, table is feuil1$;

Name_TABLE :

load *

where where exists(Num_Customer ,CustomerID);

SELECT *

FROM SQL_table_Name

WHERE Customer IN ( MY_CUSTOMER_IN_EXCEL_LIST); //this where  clause is not mandatory//

drop table YourCustomerTable;

Chage the field names as per you table field name.

Thanks

[#10]

Hi,

Thanks to your help.

I'm at home, i hope find time to try tomorrow...