How to export to CSV in format like this datetime;hostname;username1;username2;

Question

Please help me to parse something to a CSV. I already know how to do it using Powershell, but I need to use a batch file.

I need to get output like this:

datetime;hostname;username1;username2;

where username1 username2 etc are the user names from the local administrators group.

I made this:

@echo off
echo %DATE%
echo %TIME%
set datetimef=%date:~-4%_%date:~3,2%_%date:~0,2%__%time:~0,2%_%time:~3,2%_%time:~6,2%
echo %datetimef%
for /f "tokens=* skip=6" %%a in ('net localgroup Administrators') do (
echo %%a >> "c:\temp\%datetimef%.csv")
pause

However the output file name is:

2021_ 1_Fr__11_42_18.csv

And the date returned is:

Administrator 
Usr1
The command completed successfully

How can I remove The command completed successfully from the resulting output file?

As a side note flamixx, `%COMPUTERNAME%`, does not necessarily expand to the same string as the one assigned to the 'hostname'. — Compo, Dec 10 '21 at 23:16

Gerhard · Answer 1 · 2021-12-10T12:32:22.467

1

I suppose you want something like this?

@echo off
setlocal enabledelayedexpansion
set datetimef=%date:~-4%_%date:~3,2%_%date:~0,2%__%time:~0,2%_%time:~3,2%_%time:~6,2%
set "var=%datetimef%"
for /f "skip=6delims=" %%a in ('net localgroup Administrators ^| findstr /v "successfully"') do set "var=!var!;%%a"
(echo %var%)>>"c:\temp\%datetimef%.csv"

edited Dec 10 '21 at 12:32

answered Dec 10 '21 at 12:26

Gerhard

22,678
7
27
43

score 1 · Accepted Answer · answered Dec 10 '21 at 21:48

It is easy enough to do this in a .bat batch-file run by cmd. If you are on a supported Windows system, PowerShell will be available.

powershell.exe -NoLogo -NoProfile -Command ^
    $DatetimeF = Get-Date -Format 'yyyy_MM_ddThh_mm_ss'; ^
    $Users = (Get-LocalGroupMember -Name Administrators).Name; ^
    $DatetimeF + ';' + $Env:COMPUTERNAME + ';' + ($Users -join ';') ^| ^
        Out-File -FilePath ($DatetimeF + '.csv')

Compo · Answer 3 · 2021-12-11T16:46:48.217

The main problem with the output you have reported, is that it appears to have assumed the returned output of your %DATE% and %TIME% variables. Those values can be configured differently for the locale, machine and/or logged in user, and in your case does not match the %DATE% assumption of dd#MM#yyyy, (where # represents the item sepatators). Yours is clearly either ddd d#M#yyyy, ddd dd#MM#yyyy, ddd M#d#yyyy. or ddd MM#dd#yyyy

There aremany questions and answer already on this site which show methods of getting the date and time using a none locale or otherwise configured format, the most useful, non PowerShell method of doing that uses the built-in WMIC.exe utility.

Just for something a little bit different, i.e. not using net.exe at all, and instead using WMIC.exe:

@Echo Off
SetLocal EnableExtensions DisableDelayedExpansion
Set "{="
Set "}="
For /F Tokens^=6^ Delims^=^" %%G In ('%SystemRoot%\System32\wbem\WMIC.exe OS Get CSName^,LocalDateTime /Format:MOF 2^>NUL') Do If Not Defined { (Set "{=%%G") Else (SetLocal EnableDelayedExpansion
    Set "}=%%G"
    For %%H In ("!}:~,4!_!}:~4,2!_!}:~6,2!__!}:~8,2!_!}:~10,2!_!}:~12,2!;!{!") Do (EndLocal
        Set "{=%%~H"))
For /F "Tokens=1,* Delims==" %%G In ('%SystemRoot%\System32\wbem\WMIC.exe Group Where "Name='Administrators'" Assoc:List /ResultClass:Win32_UserAccount 2^>NUL ^| %SystemRoot%\System32\findstr.exe "^Name="') Do For /F "Tokens=*" %%I In ("%%H") Do If Not Defined } (Set "}=%%I") Else (SetLocal EnableDelayedExpansion
    For %%J In ("!}!") Do (Endlocal
        Set "}=%%~J;%%I"))
Set "CSVBaseName=%{:;="&:"%"
(Echo %{%;%}%;) 1>"C:\Temp\%CSVBaseName%.csv"

If you wanted to still use net.exe, i.e. 'a little bit different' was not what you wanted, then the following should perform the same task:

@Echo Off
SetLocal EnableExtensions DisableDelayedExpansion
Set "{="
Set "}="
For /F Delims^=^ EOL^= %%G In ('%SystemRoot%\System32\net.exe LocalGroup
 Administrators 2^>NUL') Do (If Not Defined { (Set "{=%%G") Else (
        SetLocal EnableDelayedExpansion
        For /F Delims^= %%H In ("!{!") Do (EndLocal
            Set "{=%%H;%%G"
            Set "}=%%H"))
    Set /P "=%%G" 0<NUL | %SystemRoot%\System32\findstr.exe "^\-\-*$" 1>NUL && (
        Set "{="))
If Not Defined } GoTo :EOF
For /F Delims^=^ EOL^= %%G In ('%SystemRoot%\System32\HOSTNAME.EXE'
) Do Set "{=%%G;%}%"
For /F Tokens^=6^ Delims^=^" %%G In ('%SystemRoot%\System32\wbem\WMIC.exe OS Get
 LocalDateTime /Format:MOF 2^>NUL') Do Set "}=%%~nG"
Set "}=%}:~,4%_%}:~4,2%_%}:~6,2%__%}:~8,2%_%}:~10,2%_%}:~-2%"
(Echo %}%;%{%;) 1>"C:\Temp\%}%.csv"

The differences with this being, that it does not assume a specific number of lines to skip, it does not expect the end user language to be English, it uses the reported HostName, as opposed to the ComputerName, (which may not necessarily match), and once again does not rely on the end users computer configuration for defining the date and time string.

This is not "a little bit different", this is a lot confusing way to complicate another working and simpler answer. You not helping or let anyone to learning something with this one... — Io-oI, Dec 10 '21 at 21:43
Anyway, of course, I understand, this answers yourself, and if you need to edit it in the future, it will be easier to open a new question..., I understand that here the way is to teach people to solve their own problems, not exactly create/change them .. — Io-oI, Dec 10 '21 at 22:12
It's not about working or not, nor about limitations, and if it were, it's easier to edit substring variables to adjust where necessary. But understand that any edit to your answer, where character soup, escaping, and multiples `for`, makes it 100x more complicated for a lot more user than you can imagine, yet it works. — Io-oI, Dec 10 '21 at 23:00
Of course it is about 'working' @Io-oI! My code, as you stated works, and unlike the OP's 'Answer''s content, and that offered by Gerhard, it is not limited to the end users language, date and time settings nor does it assume a preset number of lines to skip. As this is an international site, answers which are not specifically limited to language or locale settings are more useful than you are giving credit for. — Compo, Dec 10 '21 at 23:11
both work, but editing yours when necessary is impossible without requiring your international participation. changing what works (with some simple editing when necessary), so "works internationally" is 100x more complicated, — Io-oI, Dec 10 '21 at 23:38

score -1 · Answer 4 · answered Dec 10 '21 at 14:20

thank you all! finally i solved with all your help and came to this:

@echo off
setlocal enabledelayedexpansion
rem set mydate=%:/date:/=%
rem set mytime=%time::=%
rem set mytimestamp=%mydate: =_%_%mytime:.=_%
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
set mytimestamp=%mydate%_%mytime%
for /f "skip=6delims=" %%a in ('net localgroup Administrators ^| findstr /v "successfully"') do (
set param=!param!%%a;)
echo %mytimestamp%;%computername%;!param! >> "c:\temp\%mydate%.csv"

You cannot just post random answers, that is not how it works, please go read the tour again as you should have when you registered on the site. — Gerhard, Dec 10 '21 at 14:28

How to export to CSV in format like this datetime;hostname;username1;username2;

4 Answers4