convert TXT from list to Column csv/xls

JaysonJackson
JaysonJackson used Ask the Experts™
on
I have a txt file that is in a list form. (below and attached List.txt) I would like to convert this to a spread sheet that has the columns  and the data filled in (attached column.txt).  any help is appreciated


List data


FirstName            : Test
LastName             : account
Email                : taccount@xyz.com
SamAccountName       : taccount
PasswordNeverExpires : True
ProfilePath          :
LogonScript          : users.bat
HomeDirectory        : \\profiles\$data\taccount
PasswordLastSet      : 6/23/2011 2:27:15 PM
LastLogonTimestamp   : 5/19/2012 7:33:45 AM


FirstName            : Test1
LastName             : account
Email                : t1account@xyz.com
SamAccountName       : t1account
PasswordNeverExpires : True
ProfilePath          :
LogonScript          : users.bat
HomeDirectory        : \\profiles\$data\t1account
PasswordLastSet      : 6/23/2011 2:27:15 PM
LastLogonTimestamp   : 5/19/2012 7:33:45 AM



Column data
FirstName      LastName      Email      SamAccountName      PasswordNeverExpires      ProfilePath      LogonScript      HomeDirectroy      PasswordLastSet      LastLogonTimeStamp
Test      account      taccount@xyz.com      taccount      TRUE            Users.bat      \\profies\$data\taccount      6/23/2011 14:27       5/19/2012 7:33:45 AM
Test1      account      t1account@xyz.com      t1account      TRUE            Users.bat      \\profies\$data\1taccount      6/23/2011 14:27       5/19/2012 7:33:45 AM
list.txt
column.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Here's a batch script (save as Whatever.cmd) that should do the trick.
Just specify the InFile variable; the output file name will be the same as the input file name, but with the extension "csv". You can as well specify the character to be used to separate the columns in the "CsvDelim" variable.
@echo off
setlocal enabledelayedexpansion
set InFile=list.txt
set CsvDelim=;
for %%a in ("%InFile%") do (set OutFile=%%~dpna.csv)
if exist "%OutFile%" del "%OutFile%"
for /f "tokens=1* delims=: " %%a in ('type "%InFile%"') do (
  set %%a=%%b
  if /i "%%a"=="LastLogonTimestamp" (
    >>"%OutFile%" echo !FirstName!%CsvDelim%!LastName!%CsvDelim%!Email!%CsvDelim%!SamAccountName!%CsvDelim%!PasswordNeverExpires!%CsvDelim%!ProfilePath!%CsvDelim%!LogonScript!%CsvDelim%!HomeDirectory!%CsvDelim%!PasswordLastSet!%CsvDelim%!LastLogonTimestamp!
  )
)

Open in new window

Author

Commented:
thanks you made this look so simple..
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
@oBdA

Well done!

~bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial