Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[avinashelite]

Hi All,

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

Regards,

Avinashelite‌

[avinashelite]

Hi All,

Please feel free to provide your inputs

[ChristofSchwarz]

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.

[avinashelite]

Thanks your comments cschwarz.

SQLtables commands provide meta data information about the tables or data that we are pulling.

[happydays1967]

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

HP

[Anonymous]

Amigos

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.

AGradeço.

[#7]

Thanks for sharing this , a very useful !! let me go through it.. & will give you feedback soon..

[psankepalli]

Hello,

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

SQL##f - SqlState: IM014, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

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.

Thanks

SP

[happydays1967]

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.

Try:

ODBC CONNECT64 TO [Excel Files;DBQ=C:\Users\Prasanna\Desktop\Parsars\Test 20150502 1.xlsx]

HP.

[psankepalli]

Hello Peter,

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)];

  Temp_Tables:

  SQLTABLES;

  DISCONNECT;

// 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.

[avinashelite]

Hi Prasanna,

Can you please share your excel and app, let see whats the issue ?