Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
As suggested by some thread in qlik community,to use .xlsb file format,we should follow the below steps:
1. Make sure Excel ODBC drivers are installed. It install by default most of the time while installing Office.
2. Go to Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose either 'Excel Files'from the list ->
Press 'Configure' -> finally choose the file as ODBC source
Once the connection is addded successfully, In QV script editor you can select the odbc connection
I downloaded excel drivers but I am not sure if I configured it properly.
The below screenshot is just giving me option to select a file.What if I want to select a whole folder where all excel files will come?
Please suggest what should I choose in below screenshot:
Also,Could anyone suggest what username and password am I going to use while connecting to ODBC in qv script
?
Thanks in advance.
You couldn't access a folder per odbc only tables respectively files. That meant you need an additionally routine to grab multiple files - most convenient will be a loop per filelist/dirlist and it should be look like this one:
let vCurrentYearMonth = date(today() - 1, 'YYMM');
let vLastYearMonth = date(monthstart(today() - 1) - 1, 'YYMM');
for each vRevisor in 'a', 'b', 'c', 'd'
for each vPeriod in '$(vLastYearMonth)', '$(vCurrentYearMonth)'
for each file in filelist('X:\Path\Revision\$(vRevisor)\$(vPeriod)??_????.xlsb');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
RevisionsprotokollODBC:
Load *;
SELECT * FROM `$(file)`.`RevisionsProtokoll$`;
next
next
next
- Marcus
Thanks a lot Marcus.
I am just trying to understand the code.
Let me try and then revert.