Feb 5, 2015 11:28:47 PM
This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.
//to read each file from the specified folder
FOR EACH file in FileList('filepath\*.xlsx');
//In order to get the file information from SQLtables command making use of the ODBC connection format ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
tables: SQLtables; DISCONNECT; FOR i = 0 to NoOfRows('tables')-1 LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); Table: Load * , FileBaseName()as FIle, FileDir() as Dir, FileName() as File_Name, '$(sheetName)' as Sheet_name From $(file)(ooxml, embedded labels, table is [$(sheetName)]); NEXT i Next
Hope this helps !!!
please find the attachment for the eg: qvw and test fiels
Please feel free to provide your inputs
Excellent man. I have no idea what the command "SQLtables" really does, but the ODBC connectioin knows what to do and returns information about the Excel sheets and more. I had a perfect use for this , thanks.
Thanks your comments cschwarz.
SQLtables commands provide meta data information about the tables or data that we are pulling.
I got a '[Microsoft][ODBC Driver Manager] the specified DSN contains an architecture mismatch between the Driver and Application. I am working on a 64 bit Win 7 laptop and I had to use CONNECT64 to be able to get the data from the .xlsx files, even though (or maybe especially because) QV is 32 bits
Might save somebodies valueable time
Tenho um controle CASHFLOW, em planilhas com informações diaria do fluxo de caixa uma em cada planilha são varias, no entanto tenho uma outra pasta chamada fechamento- onde nela consta toda movimentação diaria que corresponde ao mês do fechamento sendo que há uma pasta dessa para cada Mês, ou seja uma para janeiro e outra fevereiro, em cada uma delas tenho os valores a realizar e realizado, meio confuso não, além disso em algumas delas a formula, irei conseguir, como a base ira enteder celular com estas formulas, preciso muito de ajuda.
Thanks for sharing this , a very useful !! let me go through it.. & will give you feedback soon..
I am using the same code to load multiple excel sheets to my QV 11.20 version. its giving the DNS Error.
The Error is:
SQL##f - SqlState: IM014, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\Prasanna\Desktop\Parsars\Test 20150502 1.xlsx]
Also, when I try to connect to Excel file through QV >> Edit Script >> ODBC (Force 32) >> Connect >> Select Excel Files >> Test Connection ( User ID & PWD blank, Not sure what I need to enter so, left is blank) giving the following error.
Connection Test Failed
Do I need to enter any User ID & Pwd (I am administrator to this computer).
I am not sure how to connect to the Excel files with ODBC. because these already has DNS connection. I can Load data from excel. but my QV cannot detect SQLTables under ODBC connection.
Please help me. I really appreciate your help.
Are you working on a Win 64 machine? Even though QlikView might be the 32 bit version, you have to use the CONNECT64 because the DSN is a 64 bit DSN.
ODBC CONNECT64 TO [Excel Files;DBQ=C:\Users\Prasanna\Desktop\Parsars\Test 20150502 1.xlsx]
I have tried with your suggestion i.e; ODBC CONNECT64. but even I am getting the same error.
This is my following code
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];
// Get just the file name
let vFileName = mid(vFile, index(vFile, '\', -1) + 1);
for iSheet = 0 to NoOfRows('Temp_Tables') - 1
let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');
let vSheetName = replace(vSheetName, chr(39), '');
The error is ;
SQL##f - SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\sprasanna\Desktop\Parsers\Bloomingdales 20150502 1.xlsx]
Please help me.
Can you please share your excel and app, let see whats the issue ?