Fixed Width Text file

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
zagix
Posts: 68
Joined: 16 Oct 2013 23:19

Fixed Width Text file

#1 Post by zagix » 13 Aug 2015 02:55

Hi,

I have an Tab delimited Text file and would like to reset it to fixed width padding spaces where required.
Existing tab text file
11(tab)626350837(tab)6263506492(tab)ID(tab)Friends Limousine Service(tab)111 Glenn Way #-2(tab)Lamar(tab)Ana(tab)y(tab)Racheal

Required fixed width text file
11626350837....6263506492.........ID..Friends Limousine Service.............111 Glenn Way #-2..................Lamar........Ana.............y.Racheal...(white space of 10)


2,15,19,4,38,35,13,15,2,10 (if no data then also needs white space of 10)

Data starts from line 2 as first line is the header.

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Fixed Width Text file

#2 Post by Aacini » 13 Aug 2015 06:40

Why do you delete the first tab in your output example?

Code: Select all

11(tab)626350837...

Try this:

Code: Select all

more < input.txt > output.txt

Antonio

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: Fixed Width Text file

#3 Post by Squashman » 13 Aug 2015 06:43

Does each tab delimited field need to be a certain length. In other words you may need to pad X amount of spaces to the end of each field?

zagix
Posts: 68
Joined: 16 Oct 2013 23:19

Re: Fixed Width Text file

#4 Post by zagix » 13 Aug 2015 09:45

Hi,

Thanks Squashman for the reply.

Does each tab delimited field need to be a certain length.

Yes 2,15,19,4,38,35,13,15,2,10 (if no data then also needs white space of 10)

Before
11(tab)626350837(tab)6263506492(tab)ID(tab)Friends Limousine Service(tab)111 Glenn Way #-2(tab)Lamar(tab)Ana(tab)y(tab)Racheal


After
11626350837....6263506492.........ID..Friends Limousine Service.............111 Glenn Way #-2..................Lamar........Ana.............y.Racheal...(white space of 10)


Like in Before starts with 2 numeric then no need of x pad space
field 2 has 9 digits but in after file 9 digits + 6 space completes 15
filed 3 has 10 digits but in after file 10 + 9 space completes 19
...

Data starts from line 2 as first line is the header.

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Fixed Width Text file

#5 Post by Aacini » 13 Aug 2015 10:28

You should try to be clearer in your descriptions! Try this:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "letter=abcdefghij"
set "tokens="
set maxSpaces=0
set i=0
for %%a in (2,15,19,4,38,35,13,15,2,10) do (
   for %%i in (!i!) do set "tokens=!tokens!"%%!letter:~%%i,1!" "
   set /A i+=1
   set "tab[!i!]=%%a"
   if %%a gtr !maxSpaces! set "maxSpaces=%%a"
)
set "spaces="
for /L %%i in (1,1,%maxSpaces%) do set "spaces= !spaces!"

rem Insert a TAB char here:  -----v
for /F "skip=1 tokens=1-10 delims=   " %%a in (test.txt) do (
   set "output="
   set i=0
   for %%A in (%tokens%) do (
      set "token=%%~A%spaces%"
      set /A i+=1
      for %%I in (!i!) do for %%N in (!tab[%%I]!) do (
         set "output=!output!!token:~0,%%N!"
      )
   )
   echo !output!
)

In the second line below:

Code: Select all

rem Insert a TAB char here:  -----v
for /F "skip=1 tokens=1-10 delims=   " %%a in (test.txt) do (

you must delete the three spaces after "delims= " and insert a TAB character using Notepad Editor (or any other editor).

Antonio

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: Fixed Width Text file

#6 Post by Squashman » 13 Aug 2015 20:12

We also have a format function in the script library.
http://www.dostips.com/DtCodeCmdLib.php#Function.Format

Yury
Posts: 115
Joined: 28 Dec 2013 07:54

Re: Fixed Width Text file

#7 Post by Yury » 14 Aug 2015 03:39

Code: Select all

@echo off
setlocal enabledelayedexpansion

set "in=in.txt"
set "out=out.txt"
set "template=2 15 19 4 38 35 13 15 2 10"

for /f "delims=" %%i in ('forfiles /p "%~dp0." /m "%~nx0" /c "cmd /c echo.0x09"') do set TAB=%%i
for %%i in (%template%) do set/a m+=1& set !m!=%%i

>"%out%" (
<"%in%" set/p line=& echo.!line!
for /f "usebackq skip=1 delims=" %%i in ("%in%") do (
 set line=%%i& for %%j in ("!line:%TAB%=" "!") do (
  set/a n+=1& for %%k in (!n!) do for /l %%l in (1 1 !%%k!) do set SPs= !SPs!
  <nul set/p=%%~j!SPs!& set SPs=
 )
 echo.& set n=
)
)

endlocal
exit/b

foxidrive
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

Re: Fixed Width Text file

#8 Post by foxidrive » 14 Aug 2015 06:29

I tested Aacini and Yury solutions with this data (tabs in the blank spots) - results were not what I expected.

Code: Select all

11   626350837   6263506492   ID   Friends Limousine Service   111 Glenn Way #-2   Lamar   Ana   y   Racheal

Compo
Posts: 599
Joined: 21 Mar 2014 08:50

Re: Fixed Width Text file

#9 Post by Compo » 14 Aug 2015 10:09

I normally use a powershell script/function for this type of thing.

Code: Select all

param([UInt32] $TabWidth = 8)
process {
  $line = $_
  while ( $TRUE ) {
    $i = $line.IndexOf([Char] 9)
    if ( $i -eq -1 ) { break }
    if ( $TabWidth -gt 0 ) {
      $pad = " " * ($TabWidth - ($i % $TabWidth))
    } else {
      $pad = ""
    }
    $line = $line -replace "^([^\t]{$i})\t(.*)$",
      "`$1$pad`$2"
  }
  $line
}

Whilst the above doesn't add the spaces to the line ends, it should convert all tabs to spaces whilst maintaining alignment.

To convert using a tab size of 8 spaces (default) you don't need to add a parameter:

Code: Select all

GC DataIn.txt | .\TabXpand.ps1 | Out-File -encoding ASCII DataOut.txt

To convert using a tab size of 4 spaces just use four as the parameter:

Code: Select all

GC DataIn.txt | .\TabXpand.ps1 4 | Out-File -encoding ASCII DataOut.txt

If your input file is unicode you could of course remove the -encoding ASCII part too.

Before everyone complains that this isn't a powershell forum, I think the same option is available directly in cmd.exe by using the more command.
Example with tabs of 4 spaces

Code: Select all

More /t4 DataIn.txt>DataOut.txt

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: Fixed Width Text file

#10 Post by Squashman » 14 Aug 2015 11:01

Compo wrote:Before everyone complains that this isn't a powershell forum,

Not at all. I think we all are better off learning a little bit of Powershell, but your examples do not solve the users problem as they want a specific amount of space for each tab delimited field.

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Fixed Width Text file

#11 Post by Aacini » 14 Aug 2015 11:27

foxidrive wrote:I tested Aacini and Yury solutions with this data (tabs in the blank spots) - results were not what I expected.

Code: Select all

11   626350837   6263506492   ID   Friends Limousine Service   111 Glenn Way #-2   Lamar   Ana   y   Racheal


It works here!

Code: Select all

The original data:
11(tab)626350837(tab)6263506492(tab)ID(tab)Friends Limousine Service(tab)111 Glenn Way #-2(tab)Lamar(tab)Ana(tab)y(tab)Racheal

After changed "(tab)" by an Ascii TAB (that will show different when posted here):
11   626350837   6263506492   ID   Friends Limousine Service   111 Glenn Way #-2   Lamar   Ana   y   Racheal

The output from my solution:
11626350837      6263506492         ID  Friends Limousine Service             111 Glenn Way #-2                  Lamar        Ana            y Racheal   
|/\_____________/\_________________/\__/\____________________________________/\_________________________________/\___________/\_____________/|/\________/
2       15                19         4                    38                                   35                       13          15       2     10

Did you inserted the TAB in the specified line?

Antonio

PS - Where is the OP? :o

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Fixed Width Text file

#12 Post by Aacini » 14 Aug 2015 13:19

A Batch-JScript hybrid script solution!

EDIT: Small bug fixed (didn't separate lines!)

Code: Select all

@set @Batch=1 /*

@echo off
setlocal EnableDelayedExpansion

set "regExp="
set "retVal="
set /A i=1, maxSpaces=0
for %%a in (2,15,19,4,38,35,13,15,2,10) do (
   set "regExp=!regExp!\t(.*)"
   set "retVal=!retVal!+($!i!+spaces).substr(0,%%a)"
   if %%a gtr !maxSpaces! set "maxSpaces=%%a"
   set /A i+=1
)
set "regExp=/^%regExp:~2%$/gm"
set "retVal=%retVal%+'\r\n'"

CScript //nologo //E:JScript "%~F0" "%regExp%" "%retVal:~1%" %maxSpaces% < test.txt
goto :EOF     */


// JScript section


var arg = WScript.Arguments, regExp = arg(0), retVal = arg(1), maxSpaces = parseInt(arg(2)), spaces = "";

for ( var i = 1 ; i <= maxSpaces ; i++ ) spaces += " ";

WScript.Stdout.WriteLine(
   WScript.Stdin.Readall().replace(
      eval(regExp),
      function ($0,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10) {return (eval(retVal));}
   )
);

This solution correctly manage the case when there is "no data", that is, when there are two TAB's together.

PS - I tried to create the function parameters in a variable and then define the function this way:

Code: Select all

function ( eval(funcParams) ) { return ...

but it marked an error. It seems that eval can not be used always; perhaps there are specific cases when certain declarations can not be performed inside an eval...

Antonio
Last edited by Aacini on 16 Aug 2015 07:47, edited 1 time in total.

foxidrive
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

Re: Fixed Width Text file

#13 Post by foxidrive » 14 Aug 2015 21:21

Aacini wrote:The output from my solution:
11626350837 6263506492 ID Friends Limousine Service 111 Glenn Way #-2 Lamar Ana y Racheal


I did something wrong last night and the result isn't reproducible. I did replace the TAB but I can't figure
out what it was that I stuffed up...

Yuri, does your solution produce more spaces than required?
The first two terms are also meant to run together, right?

This creates the test file I used:

Code: Select all

@echo off
(
echo(-----BEGIN CERTIFICATE-----
echo(YWFhDQoxMQk2MjYzNTA4MzcJNjI2MzUwNjQ5MglJRAlGcmllbmRzIExpbW91c2lu
echo(ZSBTZXJ2aWNlCTExMSBHbGVubiBXYXkgIy0yCUxhbWFyCUFuYQl5CVJhY2hlYWwN
echo(Cg==
echo(-----END CERTIFICATE-----
)>"test.txt.decode"
certutil /decode /f "test.txt.decode" "test.txt"
del "test.txt.decode"


PS - Where is the OP? :o


Good question. :evil:

Yury
Posts: 115
Joined: 28 Dec 2013 07:54

Re: Fixed Width Text file

#14 Post by Yury » 14 Aug 2015 23:33

foxidrive wrote:Yuri, does your solution produce more spaces than required?




Oh yeah :oops: !

This is the correct solution:

Code: Select all

@echo off
setlocal enabledelayedexpansion

set "in=in.txt"
set "out=out.txt"
set "template=2 15 19 4 38 35 13 15 2 10"

for /f "delims=" %%i in ('forfiles /p "%~dp0." /m "%~nx0" /c "cmd /c echo.0x09"') do set TAB=%%i
for %%i in (%template%) do set/a m+=1& set !m!=%%i

>"%out%" (
<"%in%" set/p line=& echo.!line!
for /f "usebackq skip=1 delims=" %%i in ("%in%") do (
 set line=%%i& for %%j in ("!line:%TAB%=" "!") do (
  set/a n+=1& for %%k in (!n!) do (
   for /f %%l in ('cmd/u/c echo.%%~j^| more^| findstr .^| find/c /v ""') do (
    for /f %%m in ('set/a !%%k!-%%l') do (
     for /l %%n in (1 1 %%m) do (
      set SPs= !SPs!
     )
    )
   )
  )
  <nul set/p=%%~j!SPs!& set SPs=
 )
 echo.& set n=
)
)

endlocal
exit/b


.

foxidrive
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

Re: Fixed Width Text file

#15 Post by foxidrive » 15 Aug 2015 00:55

Aacini wrote:A Batch-JScript hybrid script solution!


Antonio, there seems to be no padding after "Rachael"

Yury wrote:This is the correct solution:


It seems good. The OP will have to confirm them both.

Post Reply