# Solved: Outputting a batch as a CSV



## AJ Russell (Apr 20, 2009)

Hi,

I have found a batch file script (on this forum) that checks a directory and then outputs the Tree name and size in bytes - as so..


```
@Echo Off
Call :_TotalSize "D:\TEST1" >> D:\output.csv
Call :_TotalSize "D:\TEST2" >> D:\output.csv
Goto :EOF
:_TotalSize
Set _size=0
For /F "tokens=3 delims= " %%I In ('Dir "%~1" /a-d /S ^|Find /I "file(s)"') Do Set _size=%%I
Echo %1 %_size% Bytes
Goto :EOF
```
What i want to be able to do is give the directory a name and output as a correct CSV.

Currently the above outputs as -


```
"D:\TEST1\" 176,093,828 Bytes
```
And as the CSV seperates each comma, each value appears i seperate boxes in excel - i want to remove the commas that the batch file adds in (if possible!) and make it output in megs.

As well as give each line a title, for instance,

Call :_TotalSize "TEST 1" "D:\TEST1" >> D:\output.csv
Call :_TotalSize "TEST 2" "D:\TEST2" >> D:\output.csv

So the end CSV will appear as

TITLE DIRECTORY SIZE
Test1 D:\TEST1 1024
Test2 D:\TEST2 2048

etc...

Any assistance would be greatly appreciated.

Regards

Andrew


----------



## Squashman (Apr 4, 2003)

_Posted via Mobile Device_
I guess I don't understand why you would want to remove the commas. It is a heck of a lot easier importing a delimited file into excel then a non delimeted file.


----------



## TheOutcaste (Aug 8, 2007)

Welcome to TSG!

I think he just wants to remove the commas in the size, ie, the thousands separator. The delimiter commas could then be added, unless he wants each line all in the same column.

Adding the */-C* switch to the *Dir* command will take care of those commas. I don't know of anyway to change the output to MiB though.
Two options, you can do the math in the batch file, which has some limits. The Command Prompt only deals with whole numbers (it truncates the decimal portion) less than 2,147,483,647. Anything less than 1,048,576 bytes would show as 0, and any size over 2,147,483,647 bytes would be displayed as a negative number until you reach 4,294,967,295 bytes, when it would give an error.

If you need it displayed in MiB, best to do the math in Excel. This would also let you display decimal numbers.

You can actually let the batch file enter the formula, though using the comma as a delimiter means you can't use the Round function, so you have to get a bit more complicated if you want to specify decimal places.

This should do what you want. Set the number of decimal places in the 2nd line:

```
@Echo Off
Set _NumDecimals=2
>>D:\output.csv Echo Title,Directory,Size
Call :_TotalSize "Test 1" "D:\TEST1" >> D:\output.csv
Call :_TotalSize "Test 2" "D:\TEST2" >> D:\output.csv
Goto :EOF
:_TotalSize
Set _Size=0
For /F "Tokens=3 Delims= " %%I In ('Dir "%~2" /A-D /-C /S ^|Find /I "file(s)"') Do Set _Size=%%I
Echo %[COLOR=Red][B]~[/B][/COLOR]1,%[COLOR=Red][B]~[/B][/COLOR]2,=Int(%_Size%*10^^%_NumDecimals%/1048576)/10^^%_NumDecimals%[COLOR=Blue],MiB[/COLOR]
Goto :EOF
```
If you don't want the MiB in the 4th column, just remove the *,MiB* part, or replace it with whatever you want.
This also removes the quotes, so the title and directory name will not be surrounded by double quotes. If you need the quotes, remove the red *~*'s. (The quotes _are_ needed on the Call statement)

HTH

Jerry


----------



## AJ Russell (Apr 20, 2009)

Thanks a lot OutCast,

Works a treat


----------



## AJ Russell (Apr 20, 2009)

Squashman, 

TheOutcaste was correct on removing the delimiter on the thousandths for size, as it was populating it as 1,000,000 bytes, which was placing 1 in a row, 000 in a row and the trailing 000 bytes in a final row. Rather Annoying


----------



## ghostdog74 (Dec 7, 2005)

here's a vbscript alternative, no need to bother about thousand separators

```
Set objFS = CreateObject("Scripting.FileSystemObject")
strFolder = "c:\perl"
Set objFolder = objFS.GetFolder(strFolder)
Sub ScanDir (objFolder)
	For Each strDir In objFolder.SubFolders
		ScanDir(strDir)
		WScript.Echo strDir.Name & "," & strDir.Path &","&strDir.Size
	Next
End Sub
ScanDir objFolder
```
save as myscript.vbs and at command prompt, type

```
c:\test> cscript /nologo myscript.vbs > newfile.csv
```


----------

