Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
christophebrault
Specialist
Specialist

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 ?

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

8 Replies
flipside
Partner - Specialist II
Partner - Specialist II

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
Specialist
Specialist
Author

Great.

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Michael_Tarallo
Employee
Employee

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
hic
Former Employee
Former Employee

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 

Michael_Tarallo
Employee
Employee

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
Partner - Specialist II
Partner - Specialist II

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.

Michael_Tarallo
Employee
Employee

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
hic
Former Employee
Former Employee

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