Get a list of files with corresponding file size for excel spreadsheet

Help Activities
1

1
1452×953 36.6 KB

Hello all,

I am trying to get a list of file names and their corresponding file sizes (files are on my local drive) onto an excel spreadsheet, I am able to get the file names listed onto the excel spreadsheet.

However, now for the next step, I have no idea on how to proceed: I think its the fact that I do not know the command/s for it? for example, how I need to utilise “System.IO.Path.GetFileName(item)” to get the file names - I just don’t know the file size equivalent. I have tried adding “.Length” to the end of “System.IO.Path.GetFileName(item)” but I keep getting some error.

If anyone could help me, I would appreciate it. Thanks!

2

@ojenhcln - Please check this…

image

Build Datatable → I am calling the datatable as dt

image
image714×213 8.46 KB

Assign Code → You can add whatever details you want in the below code, just make sure you add the column name in the build datatable activity first…

 dt = (From m In Directory.GetFiles("Your Folder Name")
Select dt.Rows.Add(path.GetFileNameWithoutExtension(m),path.GetExtension(m),New fileinfo(m).LastWriteTime, New fileinfo(m).Length.ToString + " Bytes")).CopyToDataTable

Write Range → Write to your output to sheet.

Here is my ouptut

image
image871×363 15.7 KB

You can do this in a tradional for each loop if you would like…for File length…please use

 new fileinfo(item).length

Hope this helps…

3

as an alternate with a LINQ structured by Query Syntax and reduced by using Directory Info:

(From fi In New DirectoryInfo(strPath).GetFiles()
Let e = fi.Extension
Let n = fi.Name
Let s = fi.SizeInKB
Let lr = fi.LastWriteTime
Let ra = New Object(){n,e,lr,s}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Find starter help here:
Report_Name-Ext-LA-SKB.xaml (7.6 KB)

Find LINQ starter help here:
[HowTo] LINQ (VB.Net) Learning Catalogue

1 Like
4

hi mate,
Thanks for the reply, is it possible to get the file size in Megabytes?

Other than that, the code works brilliantly.

Update: now im getting this: 08/29/2021 12:13:57 Assign: Input array is longer than the number of columns in this table. ← I recreated what I have done with one sequence that I always already working on.

5

Yes, here you go…

  (From m In Directory.GetFiles("Your Folder Name")
  Select dt.Rows.Add(path.GetFileNameWithoutExtension(m),path.GetExtension(m),New 
  fileinfo(m).LastWriteTime, (CDbl(New fileinfo(m).SizeInKB)/1024).ToString("F2") + "MB")).CopyToDataTable

Hope this helps…

6

Hi,
Thank you!

I have one question and one problem left

How would I be able to “write” the excel spreadsheet to a “variable” folder?

Essentially, whenever the program is runned, an input dialog box pops up, I write in name, the name becomes associated to/as a variable. Once the folder is created, it would copy a set of files over into that folder and it would run the “excel” bit and create a file list of files within the folder. But the problem is I am not sure how to “write” the spreadsheet into a variable folder.

I tried something like this: “C:\Users\Media\Videos\Project+Name\FileList.xlsx”
but it did not work.

Also, with the file list, I open up the spreadsheet and it lists the files fine but the problem is Im not able to see the headings inserted “File names, file size, etc”

Thanks

7

It should be like this…

"C:\Users\Media\Videos\" +VariableYouHave +"\FileList.xlsx"

In Write Range propeties, Check the add Header check box…

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.