Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

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

cancel
Showing results for 
Search instead for 
Did you mean: 
avinashelite

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

Last Update:

Feb 6, 2015 2:28:47 AM

Updated By:

avinashelite

Created date:

Feb 6, 2015 2:28:47 AM

Attachments

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

Comments
avinashelite

Hi All,

Please feel free to provide your inputs

0 Likes
ChristofSchwarz
Partner Ambassador
Partner Ambassador

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
Creator
Creator

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
Not applicable

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.

0 Likes
Not applicable

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

psankepalli
Partner - Creator III
Partner - Creator III

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

0 Likes
happydays1967
Creator
Creator

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.

0 Likes
psankepalli
Partner - Creator III
Partner - Creator III

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.

0 Likes
avinashelite

Hi Prasanna,

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

0 Likes
Version history
Last update:
‎2015-02-06 02:28 AM
Updated by: