Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
christophebrault
Valued Contributor

Get attributes from files

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 ?

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Get attributes from files

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

Next

'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

8 Replies
flipside
Valued Contributor II

Re: Get attributes from files

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

Next

'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

christophebrault
Valued Contributor

Get attributes from files

Great.

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

Employee
Employee

Re: Re: Get attributes from files

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.

FileAttributes2.png

11-6-2013 9-40-06 AM.png

Thanks flipside - for this awesome and comprehensive solution.

Regards,

Michael Tarallo

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

Regards,
Mike Tarallo
Qlik
Employee
Employee

Re: Get attributes from files

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 

Employee
Employee

Re: Get attributes from files

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

Regards,
Mike Tarallo
Qlik
jerrysvensson
Valued Contributor II

Re: Get attributes from files

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.

Employee
Employee

Re: Get attributes from files

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.

Regards,
Mike Tarallo
Qlik
Employee
Employee

Re: Get attributes from files

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

Community Browser