New filed not found in excel

[dseelam]

Hello everyone,

I have 200 Excel Files named as Eg. Excelfile1, Excelfile2, Excelfile3,..........,Excelfile200

used below Script to load data in to datamodel

Load *,

From Excelfile*.xls

After doing Reload a single table is generated

but now I added a new excel file Excelfile201 with 4 extra columns but now my script is not able to find this 4 new columns

is there any work around to solve this issue ??

Thank you

Message was edited by: Dinesh Reddy Seelam

[sunny_talwar]

May be try a for loop in load instead of using wildcard:

Load Multiple excel sheets using For loop

[dseelam]

Sunny,

this is totally different scenario I am trying to load all the data including extra added columns in a single new file

[dseelam]

Sunny,

The link you provide if the data has to be pulled from diff sheets hear the senario is totally different

[sunny_talwar]

I guess the point is that you cannot load wildcard if your Excel files do not have the same field names because those get auto-concatenated. Alternative? Alternative is to load using For-Loop ->

Load multiple sheets and excel files

Loops in the Script

There are multiple threads and discussions on the topic, I guess you will have to look for them. One example available on the help section which you might find useful ->For each..next ‒ QlikView

[dseelam]

Sunny,

your answer is very help ful in case I have multiple sheets but I am talking about diff fields in different excels

if you see I attached samples I have 3 excel files if you see in qvw I am not able to bring in a field called papaya from test2 excel in to the model by using test*.xlsx

hope my question make sense

[sunny_talwar · Accepted solution]

Check this approah...

SUB DoDir (Root)

  FOR Each Ext in 'xlsx'

      FOR Each File in filelist (Root&'\*.' &Ext)

        LOAD *

        FROM $(File)

  (ooxml, embedded labels, table is Sheet1);;

      NEXT File

  NEXT Ext

  FOR Each Dir in dirlist (Root&'\*' )

      CALL DoDir (Dir)

  NEXT Dir

END SUB

CALL DoDir ('C:\Users\UserName\Downloads\New folder')

[dseelam]

Sunny,

Its working on this small data, some how its blanking out in my case where it has diff sheet names in all the excels and lot of data

What if they have different sheet names and lot of data ?

[sunny_talwar]

You can still make it work using multiple for loops

Load multiple sheets and excel files

[dseelam]

Sunny I am having hard time understanding the content in link provided

can you proved me with understandable format in your code, if sheet names are different in each excel

SUB DoDir (Root)

  FOR Each Ext in 'xlsx'

      FOR Each File in filelist (Root&'\*.' &Ext)

        LOAD *

        FROM $(File)

  (ooxml, embedded labels, table is Sheet1);;

      NEXT File

  NEXT Ext

  FOR Each Dir in dirlist (Root&'\*' )

      CALL DoDir (Dir)

  NEXT Dir

END SUB

CALL DoDir ('C:\Users\UserName\Downloads\New folder')