Load multiple excel files from Multiple excel sheetsinto QV

[#1]

Hi everybody,

can anybody help me out in completing this challenge

.

well the challenge is

I have a folder in which i have the source files along with the qv file

and the source files which are all xlsx  files

Things to remembe are before understandinng the result i need:

-Source files are not limited in number and may have different names but are all xlsx files

- source files may have no of worksheets and are not limited in number and may vary from book to book

-Name of every sheet in the every excel file is different

-In every source excel file only the 1st sheet will have the header and all other following sheets in that file will have no headers

- The no of rows filled are not confined to anylimit and may vary from sheet to sheet in the file

-the headers are similar in all the source files provided in the 1st sheet of every source file

Result i need :

based on the above conditions ,If i reload the qv by placing all the source files in the folder ,i need to the qv to be reloaded completely with the data in the source files

for refernce the source files are as attached in the image and are attched in excel formats fr quick reference

The code im trying in qlikview is as per the following

***********************************************************************************

for each vFileName in FileList('D:\Users\aju\Desktop\anand\*.xlsx');

for Each vSheet in $(vFileName)

Temp_Sheets:

LOAD *

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is $(vSheet));

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD*

     FROM $(vFileName)

     (biff, embedded labels, header is 1 lines, table is $(vSheet));

next

****************************************************************************************************************

I request you help and resolve this challenge

Thanks ahead !!!

Kind Regards

Anand

[rustyfishbones]

Hi Steve,

I have the following, while trying to Store into qvd's, all the qvd's get created, but the data is from the first Workbook 2014.

I have 3 excel workbooks - 2014,2015,2016, each workbook has 4 sheets and I get 12 QVD's 2014Jan.qvd 2014Feb.qvd..............................................

2014Jan.qvd is correct, but 2015Jan.qvd and 2016Jan.qvd have the data from 2014Jan.qvd

Any Ideas my friend

LET vFolder = '.\';

FOR EACH WorkBook IN FILELIST('$(vFolder)*.xlsx')

  ODBC CONNECT32 TO [Excel Files;DBQ=$(WorkBook)];

  exceltables:

  SQLTABLES;

  DISCONNECT;

  LET vFileName = SUBFIELD(MID(WorkBook, INDEX(WorkBook, '\', -1) + 1),'.',1);

  TRACE Excel FileName $(vFileName);

  FOR i = 0 TO NOOFROWS('exceltables')-1

  LET WorkSheet = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME',i, 'exceltables'),CHR(36)),CHR(39));

  NOCONCATENATE

  LOAD

  FILENAME() AS Name,

  Department,

  Amount

  FROM [$(WorkBook)]

  (ooxml, embedded labels, table is [$(WorkSheet)]);

  TRACE $(WorkSheet) **************************************;

  SLEEP 1000;

  STORE [$(WorkSheet)] INTO $(vFileName)$(WorkSheet).QVD(QVD);

  TRACE $(WorkSheet) **************************************;

  NEXT

  DROP TABLE exceltables;

NEXT WorkBook

[stevedark]

Hi Alan,

You need to add a DROP TABLES line, after each STORE of the QVD:

DROP TABLE [$(WorkSheet)];

What will be happening at the moment is that a table called Jan will get created first, for 2014.  This will still be there when you get back to that month for 2015.  The 2015 load of Jan will therefore go into Jan-1 (because of the NOCONCATENATE).  When you STORE Jan it will store the original one, rather than the new one.  If you look at the data model after the load you will find loads of tables with numeric suffixes.

Pretty much every time you do a STORE in a load script it should be followed by a DROP.

Hope that stores your issue (and that I get a mention in one of your videos on the topic!).

Cheers,

Steve

[rustyfishbones]

Thanks Steve,

You're a gentleman, a definite mention in the next video!

explained brilliantly too.

Regards

Alan

[Anonymous]

Hello Steve... i have a strange problem. For me this code doesn't work if all pasted as it is including loops. i get a script error... no error number.

but it works if i run it for a single file with assigning filename and sheet name manually to the variables.

[stevedark]

Which code are you trying?

Can you paste the code which works without a loop, and the code that fails with a loop, so I can see what is the likely problem.  There is nothing in the screengrab that looks wrong.  I might try dropping the first 10 though, as that could have an impact.