topic Stored Procedure for spreadsheet in QlikView

Stored Procedure for spreadsheet

Fri, 27 May 2011 13:41:29 GMT

Hi,

I am developing a stored procedure to take a data from a spreadsheet and use it to create QVD's.

What I have done is created a spreadsheet Column 1 - Table name 2 - Field List 3- QVD name.

Does anyone know how I would read the data from this spreadsheet into my procedure?

Mckay

Re: Stored Procedure for spreadsheet

IAMDV — Fri, 27 May 2011 15:00:07 GMT

Do you want SQL Syntax or Qlikview Load Script to read the stored procedure? What flavour of SQL are you using?

Cheers - DV

Stored Procedure for spreadsheet

Fri, 27 May 2011 16:51:45 GMT

Thanks for the reply DV

I've got a store procedure at the minute but Im trying to use that stored procedure to read in from a spreadsheet.

What I want is to read the Table name from the spreadsheet then the procedure will search for them in the database and take the selected fields from the spreadsheet and store that with the QVDFileName associated.

Im using Oracle database.

Script is

connectionstring= 172###########

Spreadsheet:

LOAD

TABLENAME,

FIELDLIST,

QVDFILENAME

From$(Data_Path)Spreadsheet.xls

//Procedure

sub Load_Table($(TABLENAME), $(FIELDLIST), $(QVDFILENAME))

LOAD *

Resident Spreadsheet;

$(TABLENAME):

SQL SELECT $(FIELDLIST)

FROM $(TABLENAME)

STORE $(TABLENAME) into $(Data_Path)$(QVDFILENAME);

call sub Load_Table($(TABLENAME), $(FIELDLIST), $(QVDFILENAME);

end sub;

But I just cant get it to work.

Re: Stored Procedure for spreadsheet

mike_garcia — Fri, 27 May 2011 16:58:11 GMT

To Read the Table Name from the spreadsheet, or a list of fields, you need to create a connection via ODBC to the Excel File, then execute the SQLTables; command, which will give you a list of Tables and some attributes.

SQLColumns; will give you a list of fields.

Then, use Disconnect or simply place the new connection string to the SQL DataBase (which will in turn disconnect the previous connection) and execute the store procedure.

Mike.