topic Re: Get attributes from files in QlikView

Get attributes from files

christophebrault — Tue, 03 Apr 2012 10:26:46 GMT

Hi,

In my scirpt, I load xls files, with FileName, FileSize, FileTime...

I want to get the name of the person who created the .xls. Can i do this whith attribute() ?

 subfield('$(File)','\',-1)                                                                       as FileName,
            FileSize( '$(File)' )                                                                                 as FileSize, 
            Round(FileSize( '$(File)' )/1048576)                                        as FileSizeMB, 
           Round(FileSize( '$(File)' )/1073741824)                                        as FileSizeGB, 
            FileTime( '$(File)' )                                                                                 as FileTime,
          mid(subfield('$(File)','\',-1),2,4) as FileYear,
          Attribute('$(File)','Auteur') as Auteur

I tried it in the last line with 'Auteur' (I'm french) but it doesn't work...

Any idea ?

Re: Get attributes from files

flipside — Tue, 03 Apr 2012 13:08:36 GMT

Hi,

I couldn't get Attribute() to work either, althought the help file says it should. You can get this info by using a function in your module script and calling it in script - there's basically three methods here (FSO,Shell & WMI) but some attributes can be obtained by more than one.

FUNCTION

Function strFileProps (File)

'Details via FSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(File) Then
Set objFile = objFSO.GetFile(File)

strFileProps = strFileProps & "File name: " & objFile.Name & VbCrLf
tmp2 = objFile.Name 'for shell32 code
strFileProps = strFileProps & "File path: " & objFile.Path & VbCrLf
strFileProps = strFileProps & "Folder placed on drive: " & objFile.Drive & VbCrLf
strFileProps = strFileProps & "Date created: " & objFile.DateCreated & VbCrLf
strFileProps = strFileProps & "Date last accessed: " & objFile.DateLastAccessed & VbCrLf
strFileProps = strFileProps & "Date last modified: " & objFile.DateLastModified & VbCrLf
strFileProps = strFileProps & "Parent folder: " & objFile.ParentFolder & VbCrLf
tmp = objFile.ParentFolder 'for shell32 code
strFileProps = strFileProps & "File size: " & objFile.Size & " bytes" & VbCrLf
strFileProps = strFileProps & "File size: " & objFile.Attributes & " bytes" & VbCrLf
if objFile.attributes and 1 then
strFileProps = strFileProps & "Read-Only: YES" & VbCrLf
else
strFileProps = strFileProps & "Read-Only: NO" & VbCrLf
End If

'get additional file details via shell32.dll
set shell = CreateObject("Shell.Application")
set objFolder = shell.NameSpace(tmp)
set objFolderItems = objFolder.Items()

For Each objItem in objFolderItems

If objItem = tmp2 then
strFileProps = strFileProps & "File Size: " & objFolder.GetDetailsOf(objItem,1) & VbCrLf
strFileProps = strFileProps & "Type: " & objFolder.GetDetailsOf(objItem,2) & VbCrLf
strFileProps = strFileProps & "Last Modified: " & objFolder.GetDetailsOf(objItem,3) & VbCrLf
strFileProps = strFileProps & "Date Created: " & objFolder.GetDetailsOf(objItem,4) & VbCrLf
strFileProps = strFileProps & "Last Accessed: " & objFolder.GetDetailsOf(objItem,5) & VbCrLf
strFileProps = strFileProps & "Attributes: " & objFolder.GetDetailsOf(objItem,6) & VbCrLf
strFileProps = strFileProps & "Status: " & objFolder.GetDetailsOf(objItem,7) & VbCrLf
strFileProps = strFileProps & "Owner: " & objFolder.GetDetailsOf(objItem,8) & VbCrLf
strFileProps = strFileProps & "Author: " & objFolder.GetDetailsOf(objItem,9) & VbCrLf '9 = author
End If

'get owner- alternative method
Set objWMIService = getObject("winmgmts:")
Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting='" & File & "'")
intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objFile)

If intRetVal = 0 Then
strFileProps = strFileProps & "Owner: " & objFile.Owner.Domain & "\" & objFile.Owner.Name
Else
strFileProps = strFileProps & "Couldn't retrieve security descriptor."
End If

Else
strFileProps = "Selected file does not exist!"
End If

End Function

.. then in your load script ...

x = strFileProps(yourfile);

flipside

Get attributes from files

christophebrault — Wed, 04 Apr 2012 09:06:51 GMT

Great.

This i a good example of using function in script. Thanks

Re: Re: Get attributes from files

Michael_Tarallo — Wed, 06 Nov 2013 14:41:05 GMT

Here (below attachment) is a .QVW and a screenshot of the result from this solution. It works very well. Please note the appropriate settings in the module.

Note to use: LET AttributeX = strFileProps('C:\Temp\task1.bat'); - so the variable is assigned appropriately.

Thanks flipside - for this awesome and comprehensive solution.

Regards,

Michael Tarallo

Sr. Technical Product Marketing Manager
QlikView and QlikView Expressor
@mtarallo

Re: Get attributes from files

hic — Thu, 07 Nov 2013 10:25:34 GMT

The following works fine on my computer...

HIC

For each vFileName in FileList('<Path1>\*.jpg')
Load
'$(vFileName)' as FileName,
Lower(SubField('$(vFileName)','.',-1)) as FileExtension,
Attribute('$(vFileName)','Model') as Model,
Attribute('$(vFileName)','Make') as Make,
Attribute('$(vFileName)','ColorSpace') as ColorSpace,
Attribute('$(vFileName)','ExposureTime') as ExposureTime
Autogenerate 1;
Next vFileName

For each vFileName in FileList('<Path2>\*.mp3')
Concatenate
Load
'$(vFileName)' as FileName,
Lower(SubField('$(vFileName)','.',-1)) as FileExtension,
Attribute('$(vFileName)','Album') as Album,
Attribute('$(vFileName)','Artist') as Artist,
Attribute('$(vFileName)','Title') as Title,
Attribute('$(vFileName)','Track') as Track,
Attribute('$(vFileName)','Year') as Year
Autogenerate 1;
Next vFileName

Re: Get attributes from files

Michael_Tarallo — Thu, 07 Nov 2013 14:00:44 GMT

Hi HIC - does that work for any other file type? - I believe Attribute() only works for images and other media as per the help doc.

I attempted this on a .txt file and it did not work for me.

Do we know what tokens to use for other file type attributes?

Thoughts?

Mike

Re: Get attributes from files

jerrysvensson — Thu, 07 Nov 2013 14:19:18 GMT

From Helpfile.

Returns the value of the meta tags of different file formats, e.g. MP3, WMA, WMV and JPG files, as text.

Filename is the name of a media file including path, if needed.

Attributename is the name of a meta tag.

If the file filename does not exist, is not a supported file format or does not contain a meta tag named attributename, null will be returned.

A large number of meta tags can be used, e.g. ‘Artist’ or ‘Date Picture Taken’. The supported tags can automatically be generated in the script. The keyboard shortcut for this generation is Ctrl + Q,J,P,G for jpg files (keep the Ctrl key pressed while typing the QJPG combination), Ctrl + Q,M,P,3 for mp3 files and Ctrl + Q,W,M,A for wma files.

Re: Get attributes from files

Michael_Tarallo — Thu, 07 Nov 2013 14:34:44 GMT

So Attribute() is for just media file types - Correct? (MP3, WMA, WMV and JPG files) - not PPT, XLS, TXT - etc. I wanted to make sure.

Re: Get attributes from files

hic — Thu, 07 Nov 2013 16:20:15 GMT

If I remember correctly, it can read EXIF tags.

But there are many more file attributes. How to put attributes on files is not really standardized; or rather, there exist several methods. So, you will find that there are a number of cases where it cannot read the attribute.

HIC