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

[ojenhcln (Mark)]

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!

[prasath17 (Guru)]

@ojenhcln - Please check this…

Build Datatable → I am calling the datatable as dt

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

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…

[ppr (Peter Preuss)]

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

[ojenhcln (Mark)]

@prasath17

(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

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.

[prasath17 (Guru)]

@ojenhcln

is it possible to get the file size in Megabytes?

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…

[ojenhcln (Mark)]

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

[prasath17 (Guru)]

@ojenhcln

I tried something like this: “C:\Users\Media\Videos\Project+Name\FileList.xlsx”

It should be like this…

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

@ojenhcln

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”

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