Click here for important information about Computing.net.

Solved Batch processing- Convert row to column in text files

Tags:
script
Programming
problems
batch
filename
May 23, 2012 at 14:05:36
Specs: Windows 7
I have multiple text files with date as time stamps on each file name. I need to create a column and move date in the column

For e.g this file (20100101ACME) which looks like following

STID STNM RELH TAIR WSPD WVEC WDIR
ACME 110 27 5.7 5.6 5.6 343
ACME 110 32 4.1 4.0 4.0 342
ACME 110 34 3.6 4.6 4.6 345

Needs to be converted into:
STID STNM RELH TAIR WSPD WVEC WDIR Date
ACME 110 27 5.7 5.6 5.6 343 01/01/2010
ACME 110 32 4.1 4.0 4.0 342 01/01/2010
ACME 110 34 3.6 4.6 4.6 345 01/01/2010

The file name is unique, first 8 letters represent date and the alphabets represent name.

I found a script (computing.net/answers/programming/batch-to-add-filename-as-first-column/24851.html) which was similar to what I need but it inserted the entire file name and added "," between two columns.


Hope I stated my problem clear here


See More: Batch processing- Convert row to column in text files

Report •

✔ Best Answer
May 24, 2012 at 00:14:27
Please test this script. Note that filenames must begin with 2 and no allowance is made for filenames containing spaces. Backup files (yyyymmddACME.backup etc) may be deleted when you are confident the script works as expected.

@echo off
cls
setlocal enabledelayedexpansion

for /f "tokens=*" %%1 in ('dir /b /a-d 2*.*') do (
    set filename=%%1
    set fileyear=!filename:~0,4!
    set filemnth=!filename:~4,2!
    set fileday=!filename:~6,2!

    ren %%1 %%1.backup

    set /p header=<%%1.backup
    echo !header! Date>%%1

    for /f "skip=1 tokens=*" %%A in (%%1.backup) do (
        echo %%A !filemnth!/!fileday!/!fileyear!>>%%1


  )
)


Please come back & tell us if your problem is resolved.


See in Thread ↓

#1
May 23, 2012 at 15:40:41
What are the date formats shown? i.e. is the date in the filename in the format yyyymmdd or yyyyddmm and is 01/01/2010 in the format dd/mm/yyyy or mm/dd/hyyy ?


Please come back & tell us if your problem is resolved.


Report •

#2
May 23, 2012 at 20:26:56
The format is in yyyy-mm-dd.

Report •

#3
May 23, 2012 at 21:05:44
Ok, I can accept that the date format in the filename is yyyymmdd but in what format is the date added to each output line to be? You show it as 01/01/2010, should this be interpreted as mm/dd/yyyy or should it be dd/mm/yyyy?


Please come back & tell us if your problem is resolved.


Report •

Related Solutions
See More ↓

#4
May 23, 2012 at 21:35:12
Does not really matter once I am aware of the format but lets agree on mm/dd/yyyy

Report •

#5
May 24, 2012 at 00:14:27
✔ Best Answer
Please test this script. Note that filenames must begin with 2 and no allowance is made for filenames containing spaces. Backup files (yyyymmddACME.backup etc) may be deleted when you are confident the script works as expected.

@echo off
cls
setlocal enabledelayedexpansion

for /f "tokens=*" %%1 in ('dir /b /a-d 2*.*') do (
    set filename=%%1
    set fileyear=!filename:~0,4!
    set filemnth=!filename:~4,2!
    set fileday=!filename:~6,2!

    ren %%1 %%1.backup

    set /p header=<%%1.backup
    echo !header! Date>%%1

    for /f "skip=1 tokens=*" %%A in (%%1.backup) do (
        echo %%A !filemnth!/!fileday!/!fileyear!>>%%1


  )
)


Please come back & tell us if your problem is resolved.


Report •

#6
May 24, 2012 at 12:07:25
Works like a charm! Thanks a lot Wahine.

Report •
Start a discussion

Ask Question