How to load multiple xls files in script

[gshockxcc]

Hi,

     I am VERY new to Qlikview, and I don't fully understand all of the scripting functions.  So I'm really struggling to understand how to load multiple xls files.  I did quite a bit of searching here, and I have only been able to find this link that is somewhat relevant.  When I copy that code into my script editor, I get errors that I don't know how to debug. 

I have loaded a single table, so I am somewhat familiar with how to build the script  But I don't know how to do this for all files in a directory. 

Any help would be greatly appreciated.

Thanks,

[martinpohl]

The problem is

Data:

load * resident LoadDone.

This will concatenate to the LoadDone table because they have the same structure.

use

Data:

noconatenate load

* resident Load Done

instead

Regards

[gshockxcc]

Steve,

     Excellent.  This is working perfectly now.  Can you educate me a little bit?  I am still learning the syntax for the scripting aspect.  How do the commented lines below function, an why it was giving me that error?

//now() as DateLoaded

//AUTOGENERATE(1)

I looked up "Autogenerate" in the help documentation, and while the words are quite clear, I don't understand what is actually happening when Qlikview is "automatically generating data."

Also, I am now at the point where I am adding the "Load As" statements, as before:

Load

     @1 As ...

     etc. 

Where in my script does this go? 

Thanks for your help,

-Kristan  

[stevedark]

Those two lines only do anything when appearing below the two lines above:

LoadDone:

LOAD

    now() as DateLoaded

AUTOGENERATE(1);

This will create a table called LoadDone with one row and one column, the current date and time will be stored into the field DateLoaded.

The AUTOGENERATE statement creates a number of rows in a statement (the number being the one in the bracket) and you can create data on the fly.  I've used one to generate the data for the example document I posted recently:

QlikView App: Accumulate values in the Data Model

You only need to use the @1 as in the script if the columns in your data are showing as @1.  If you are seeing actual field names you need not change anything else.  If you do need to do the field replacement this will replace the * in the load from CSV.  You will need to remove the existing QVD's and .done files if you are changing the data model at all.

Good luck.

Steve

[gshockxcc]

Steve,

     Thanks for the education.  That helps a lot.  Suppose that I expect additional columns will be added to the data.  I have already established a .qvd file, and read in hundreds of files.  With the new file that  is added, it may have 1 or more additional columns than the previous files.  Can I just add to the Load list, or do I need to delete all of the ".done" files and the old .qvd, and reload everything?

For example, suppose I have:

LOAD

@1 As [Date],

@2 As ...

...

@58 As ...

Now I want to add @59 and @60.  Can I just simply add them to the load list in the script?

Thanks much,

-Kristan    

[gshockxcc]

Steve,

     I posted elsewhere in the forum because I didn't want to hijack this thread.  But I haven't really gotten an answer that completely makes sense to me. 

http://community.qlik.com/thread/115405

I want to breakout the Year, Month, Day, and Time separately.  I have another small file that works well, but it's a single load from an Excel (.xlsx) file.  I don't know how to take that code, and apply it to the code you provided.

Here's the code that I have used previously:

Directory;

LOAD Date,

  Month(Date) as MonthDate,

  Time(Date) as TimeDate,

  Year(Date) as YearDate,

  Day(Date) as DayDate;

I tried incorporating this into the LOAD statement, as well as several other methods by trial and error.  But to no avail.

Thanks in advance,

-Kristan

[stevedark]

HI Kristan,

If the new columns appear in only some of the files it is going to be problematic.  If you do a LOAD * and precede the LOAD statements with a CONCATENATE statement you may be able to get it to work.  If you knew the cut of point where the file gained the extra fields (perhaps based on the file name or file date) you could use variables to build the load script - but again this is a bit more involved than a simple load of all identical fields.

Regarding the breaking the Date down into various parts; those functions only work on dates, data coming from CSV will inherently be a text string, rather than a date.  You will therefore need to convert to a date format from string, the syntax will need to be a bit like this:

Date#(@12, 'DD/MM/YYYY') as Date,

Where @12 is the column with the date in, and the date format in quotes matches the format of the date in the text file.

To get the date parts, you can either nest statements, for example:

Year(Date#(@12, 'DD/MM/YYYY')) as Year,

Month(Date#(@12, 'DD/MM/YYYY')) as Month,

Or do a preceding load, which will allow you to refer to the date you formatted previously, for example:

LOAD

     *,

     Year(Date) as Year,

     Month(Date) as Month,

     Date(MonthStart(Date), 'MMM-YY') as [Month Year]

     ;

LOAD

     @1 as FirstField,

     Date#(@12, 'DD/MM/YYYY') as Date,

The preceding load reduces repetition of the conversion code, and is generally the best way to go - but the in-line conversion may be easier to follow in the code.

Hope that helps,

Steve

[#8]

Hi Steve,

I have a bunch of log files (one gets generated every day) that I want to load into my document. I tried:

for each vFile in FileList('NPrinting\Log\Current Logs\*.log')

     load

          Source,

          Date,

          Time,

          Type,

          Message

     from [$(vFile)]

     (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

next

However, this doesn't load anything, and when I try to see what's happening with the debugger, it just skips right to "Script ended". I'm sure it's just a syntax error on my part. Can you set me straight?

[stevedark]

My guess would be that the relative path to the log files is wrong.  For this to work there would need to be an NPrinting folder in the same folder as your .qvw file.

To test put the full path to the logs in the code, for example:

for each vFile in FileList('C:\Users\Public\Documents\NPrinting\Log\*.log')

If that works, you can put in the relative path accordingly, ..\ to navigate up a folder in the file structure.  Remember, the location starts from where your QVW is stored.

You may want to see this poist:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

It describes how you can incrementally load from log file CSVs, using QVDs to store history.

Hope that helps,

Steve

[#10]

Thanks, Steve. It’s amazing what a little “R:\” can do for you, even if you’re not a pirate.

[Anonymous]

i used below code but my qvd file is not generated can you please let me know what i made a mistake in this

thanks in advance

for each vFile in FileList('E:\Final Data\FAN_*.xlsx')

     let vQVD = replace(vFile, '.xlsx', '.qvd');

     if alt(FileSize('$(vQVD)'), 0) = 0 then

          NextFile:

          LOAD

              *,

    MakeDate([YR],Month(Date#(Left([MON],3),'MMM'))) as Datefun,

  date#(MakeDate(Year(Date# (YR,'yyyy')), Month(Date#(MON, 'MMM')),Day(DATE#('01','dd'))),'yyyy/MM/dd')  As FileDate,

num( month(date#(MON,'MMM'))) as MONTH

          FROM $(vFile)

          (ooxml, embedded labels, table is FAN_PL);

          ;

          STORE NextFile INTO $(vQVD) (qvd);

          DROP TABLE NextFile;

     end if

next