Batch File to Convert Txt files

[codemonkey]

Hi,
I am trying to convert data in a text file to a delimiter format to open in excel.
 
Below is a sample code of what I want to do:
 
Text file comes like below (Always a first word with ":" and then information regarding it next

Code: [Select]


Apple:  1234
Orange: Jon Thomas
Pear:  He was walking down the street

What I want the new txt or csv file to look like in excel (The pipes are just used for reference as columns)

Code: [Select]


 Apple    |   Orange    |                Pear                          |
1234      | Jon Thomas|   He was walking down the street|

I was trying to use a For  F/ delims command that I used to merge CSV files however I could not figure it out.
 
Thanks,

[foxidrive]

This will give you a .CSV for to open in excel.  Line length becomes important if the files are large.

Code: [Select]

@echo off
setlocal EnableExtensions EnableDelayedExpansion
for /f "tokens=1,* delims=: " %%a in (file.txt) do (
set "var=!var!,%%a"
set "var2=!var2!,%%b"
)
>file.csv echo %var:~1%
>>file.csv echo %var2:~1%


[foxidrive]

Here's another way to do it which handles larger files - though there is a leading comma on each line.

Code: [Select]

@echo off
for /f "tokens=1,* delims=: " %%a in (file.txt) do (
set /p "=,%%a">>filea.tmp <nul
set /p "=,%%b">>fileb.tmp <nul
)
echo.>>filea.tmp

copy /b filea.tmp+fileb.tmp file.csv
del filea.tmp
del fileb.tmp

[codemonkey]

Fox,
Thank you for the help. This is actually exactly what I was looking for.

[Squashman]

Small change to fix the leading comma.

Code: [Select]

@echo off
setlocal enabledelayedexpansion
set comma=
for /f "tokens=1,* delims=: " %%a in (file.txt) do (
set /p "=!comma!%%a">>filea.tmp <nul
set /p "=!comma!%%b">>fileb.tmp <nul
set comma=,
)
echo.>>filea.tmp

copy /b filea.tmp+fileb.tmp file.csv
del filea.tmp
del fileb.tmp

[foxidrive]

Small change to fix the leading comma.

Good thinking.