Loop through folder contents (excel files) and save as text file

[StevenCooke]

Hi

When I use the MS Outlook management VBO, I can use the action ‘Get Received Items’ and store the email details in a collection. In this collection I can see all the fields that have been returned

I can then use those fields in other actions e.g. save attachment based on the entry ID of the returned email.

I’m trying to do something similar with the file management VBO but not having much luck. I have an action which gets the contents of a folder and adds them to a collection. However there is no list of fields that I can see to make use of in later actions.

This shows the contents of the collection and this list of files changes from month to month with different file names so I need to capture the actual filename and/or Path

But on the fields tab there is nothing

What I am trying to do is setup up a loop to process each file in that collection using the MS Excel VBO and use the ‘save workbook as’ action to save the .xlsx as a .txt file. I can see fields from the email collection in the text items

But nothing for the file management list.

I’m possibly going about this wrong but any pointers on how to do this would be appreciated

thanks

Steve

 and

[ewilson · Accepted solution]

Hi @StevenCooke,

I assume you're using the Get Files action within the Utility - File Management VBO?​ The definition of the output Collection is actually created within the Code stage on that action. The columns are defined as:

• Path (Text)
• Folder (Text)
• Name (Text)
• Extension (Text)
• Created (DateTime)
• Last Accessed (DateTime)
• Last Written (DateTime)
• Read Only (Flag)
• Bytes (Number)

The value in parenthesis is the data type of the column. Just because the columns aren't statically defined in the File collection doesn't mean you can't still access their values in the fashion you've described. If I wanted to loop through the files in a process I would simply add a Loop stage to the process and set the Collection name to Files (are whatever you named the output parameter from the Get Files action). Then I could load each spreadsheet into the Excel VBO by simply referencing it as [Files.Path].

Does that make sense?

Cheers,
Eric