topic Re: connection string to connect excel in script side in QlikView

connection string to connect excel in script side

Sat, 04 Oct 2014 07:18:12 GMT

HI All,

I want to write a connection string to connect excel in script side so how to write connection string in qlikview and in that excel sheet I have multiple sheet so for that i want to connect excel with connection string.QlikTech Admin

Re: connection string to connect excel in script side

anbu1984 — Sat, 04 Oct 2014 07:34:04 GMT

Click Table Files in Edit script and select your excel file. Then it will open below File Wizard. Here in Tables drop down, select your sheet name and click finish

LOAD col1,col2 ...

FROM

[Your_File_name.xlsx]

(ooxml, no labels, table is your_sheet_name);

Re: connection string to connect excel in script side

Sat, 04 Oct 2014 08:24:15 GMT

Hi,

You can use the below sample code:

let vTargetPath = 'C:\Excel\';

for each vFile in filelist('$(vTargetPath)*.xlsx')

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

sheets:

SQLTABLES;

DISCONNECT;

for i = 0 to NoOfRows('sheets')-1

let vSheet = PurgeChar(Peek('TABLE_NAME', $(i), 'sheets'), '$');

ExcelDoc:

LOAD *

FROM

$(vFile)

(ooxml, embedded labels, table is [$(vSheet)]);

DROP Table sheets;

If you have 1 excel file only, just provide the full document path to the vTargetPath and remove the outer for...next.

Hope this helps.