topic Re: OLEDB for windows10 in QlikView

OLEDB for windows10

Anonymous — Tue, 20 Feb 2018 11:09:18 GMT

Hello,

I have this connection string:

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

to load multiple Excel files in qlikview, but it doesn't run in W10 and gives me this error

Can someone help me, pls?

Thank you.

Re: OLEDB for windows10

gerry_hdm — Tue, 20 Feb 2018 11:44:13 GMT

Hello why don't you load Excel with OLEDB?

Excel is a table

I know the OLEDB only for Access tables

greeting Gerry

Re: OLEDB for windows10

Miguel_Angel_Baeyens — Tue, 20 Feb 2018 12:54:25 GMT

Some points to check:

Did it work before?
Did you create these values from the wizard or copied/pasted from somewhere else?
Do you have the same architecture version (32 bit, x64) for the MDAC, driver, connection in the QlikView script editor and DSN (odbcad32.exe is in two different places in Windows, one for 32 bit DSNs and the other for 64 bit ones)?
I'm missing the "Driver=" part in the DSN definition, can you regenerate manually the connect to string?

Re: OLEDB for windows10

Anonymous — Tue, 20 Feb 2018 13:06:28 GMT

Hi Miguel Angel,

thank u for the reply.

- It works into another PC (I don't remember the OS on it)

- I copied the whole procedure from a QV community post

- I'm not expert in using ODBC/OLEDB so, if u want to teach me the right procedure to create the connection string I'm glad to learn it

Here it is the procedure:

// set the data folder

let vDataFolder = 'E:\OrdiniCLavoro\';

// enumerate files

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

// connect to eaach Excel file

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

// Read list of sheets

Temp_Tables:

sqltables;

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), ''); // sqltables seems to add a random $ sign and single quotes

// Load the data

SampleData:

LOAD *,

Mid('$(vFileName)',Index('$(vFileName)','_')+1) as Filename,

'$(vSheetName)' as [Sheet Name]

FROM [$(vFile)]

(ooxml, embedded labels, header is 2 lines, table is [$(vSheetName)]);

DROP TABLE Temp_Tables;

Thank you.

Re: OLEDB for windows10

Miguel_Angel_Baeyens — Tue, 20 Feb 2018 13:52:52 GMT

It's easier done than said because there are several variables to take into account and without knowing them, it's really difficult to provide any useful advice.

First you need to install some software (drivers) in the operating system so QlikView can use it. Once the driver is installed and properly configured, then you can check whether your script works or needs any modifications.

There are several drivers for Office, check with your IT to see which ones you should use. Probably this one should be enough, but again, it's not QlikView, it's Windows, so IT has to specify which one they support.

Then you will need to create the DSN (data source name), and you can do it using 32 bit or 64 bit architecture. Which one to use, again, IT should say.

This is a good place to get started: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/OLEDB_ODBC.htm

To create a connection string within QlikView you need to go to the Script Editor > Data > Database > from the dropdown select the one you just installed > check "Force 32 Bit" if necessary > Connect (you will be prompted which connection you want to use if ODBC or to create one if OLE DB) > Select and verify that the tables available are the ones you want to load.

Re: OLEDB for windows10

Anonymous — Thu, 22 Feb 2018 07:40:40 GMT

Hello Miguel Angel,

I've solved the problem by downloading the Excel 32 drivers from the Microsoft site; I don't know why they weren't installed jet despite I have the Office installation.

However, after doing it, the OLEDB connection string works properly!

Thank you for the help.