Batch file convert comma delimited to fixed length

Question

How to convert a text file to a fixed length file:

Here is my code attempt and the sample text file.

del answer.txt

@ECHO on
@setlocal ENABLEDELAYEDEXPANSION
cls
set space=  
set var

:: loop through records
for /f "tokens=1-6 skip=1 delims=," %%a in (comma3.txt) do (
echo tokens %%a %%b %%c %%d %%e %%f
set var=%%a%space%%%b%space%%%c%space%%%d%%e%%f
echo var %var%
echo %var% >> answer.txt
)
endlocal
pause

Input:

1116559,P1303251287,20130325225906CD,13013822,1,0000
1104220,P1303250282,20130325070119CD,,1,0000
1064743,P1303251094,20130325191600CD,,0,0000
1100819,P1303250369,20130325091722CD,,0,0000
1101405,P1303250051,20130325010740CD,,0,0000

Desired output:

1116559   P1303251287   20130325225906CD   13013822   1   0000
1104220   P1303250282   20130325070119CD              1   0000
1064743   P1303251094   20130325191600CD              0   0000
1100819   P1303250369   20130325091722CD              0   0000
1101405   P1303250051   20130325010740CD              2   0000

Note:

The set var command does not store the variables.

Help GREATLY appreciated!

Answer 1

Expanding on Rojos answer, I created an excel macro that used column widths on line 1, exported the file as csv, the called powershell to convert to space delimited like so:

runCommand = "echo Please Wait . . . "

'Change to correct drive and directory:

runCommand2 = Left(SaveDirectory, 2) <br>
runCommand3 = "cd " & SaveDirectory<br>

'remove " marks that excel may insert

 runCommand7 = "powershell ""(get-content '" & SaveDirectory & "\" &     SaveName & ".csv" & "') -replace('""""','') | out-file '" & SaveDirectory & "\" & SaveName & "2.csv'"""

'Loop through cells on first row of spreadsheet to create powershell command with correct column widths

runCommand4 = "powershell ""Get-Content '" & SaveName & "2.csv'" & " | %{'"

x = 1
c = 0

Do Until Cells(1, x) = ""
    runCommand4 = runCommand4 & "{" & c & ",-" & Cells(1, x).Value & "}"
    x = x + 1
    c = c + 1

Loop

runCommand4 = runCommand4 & "' -f $_.split(',')} | Out-File Answer2.txt"""

runCommand5 = "cls"
runCommand6 = "pause"

Delete the rows with column widths so that they are not exported:

Rows("1:1").Select
Selection.Delete Shift:=xlUp

Find and replace any commas and tabs as these may cause issues:

Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Save the workbook as CSV:

ActiveWorkbook.SaveAs Filename:=SaveDirectory & "\" & SaveName, FileFormat _
    :=xlCSV, CreateBackup:=False

Pass all the commands to cmd

Call Shell("C:\Windows\System32\cmd /c" & runCommand & "&" & runCommand2 & "&" & runCommand3 & "&" & runCommand7 & "&" & runCommand4 & "&" & runCommand6, vbNormalFocus)

ActiveWorkbook.Close

This may be a convenient solution if you import your csv to excel.

Answer 2

@ECHO off
SETLOCAL
set columnsizes=10 14 19 11 4 4
DEL answer.txt 2>nul

:: loop through records
for /f "delims=" %%a in (comma3.txt) do (
SET "result="
SET line=%%a
CALL :process %columnsizes%
)
ECHO.======================================
TYPE answer.txt
ECHO.======================================
PAUSE 
GOTO :eof

:process
REM IF "%1"=="" echo %result%>>answer.txt ECHO %result%&GOTO :eof
IF "%1"=="" echo %result%>>answer.txt&GOTO :eof
SET "column="
:colloop
IF NOT DEFINED line GOTO endcol
SET ch1=%line:~0,1%
SET line=%line:~1%
IF NOT "%ch1%"=="," SET column=%column%%ch1%&GOTO colloop
:endcol
SET column=%column%                           x
CALL SET result=%result%%%column:~0,%1%%
SHIFT
GOTO process

This should crack the problem.

The FOR loop assigns the contents of each line to line in turn. The routine :process analyses the aline and builds each column, character-by-character until it finds a comma. It then adds a large number of spaces to the end of the line - the "x" will be lopped off in the next statement and it's a litle dificult to show trailing spaces on lines (and some editors throw them away unprompted)

The CALL SET line uses a parser characteristic. Suppose result is currently abcd and column is 123 ..manyspaces.. x and %1 is 5. The parser processes the line by replacing any %var% by its current value, then CALLs it, so what is CALLed is

SET result=abcd  +%+%column:~0,5+%

without the +s of course - they're to show how the parser sees the code.

So that neatly lops off the unwanted trailing spaces and the x in column; the SHIFT removes the first element from the parameter list provided to :process and the next column is processed until all have been done and there are no parameters left. Write out the result, and we're done...

Answer 3

Here's a one-liner to solve all your problems. :)

>answer.txt powershell "Get-Content comma3.txt | %{'{0,-10}{1,-14}{2,-19}{3,-11}{4,-4}{5}' -f $_.split(',')}"

If you're putting this into a batch script, be sure to replace % with %% in that line.

Contents of answer.txt:

1116559   P1303251287   20130325225906CD   13013822   1   0000
1104220   P1303250282   20130325070119CD              1   0000
1064743   P1303251094   20130325191600CD              0   0000
1100819   P1303250369   20130325091722CD              0   0000
1101405   P1303250051   20130325010740CD              0   0000

A feeble explanation:

Someone more comfortable with powershell can probably explain this better than I, as it took a bunch of trial and error for me to compose that line. But basically, as I understand it, it means as follows:

• Send the output of a powershell command to answer.txt
• Read comma3.txt
• The % is shorthand for for each line
• Return a formatted line similar to printf "%-10s %-14s %-19s etc." using line.split(",") for the string arguments

There's probably a Write-Content command for powershell, but it was easier just to have the command console redirect the output of powershell. If you're working with very large csv files and this command works too slowly, Write-Content might be worth investigating.

Answer 4

@ECHO on
@setlocal ENABLEDELAYEDEXPANSION
cls

:: loop through records
for /f "tokens=1-6 delims=," %%a in (comma3.txt) do (
set var=%%a %%b %%c %%d %%e %%f
REM  The double comma is a problem without a good solution
REM     so if the last token is null, drop it and indent. 
if '%%f' == '' set var=%%a %%b %%c          %%d %%e
REM  !'s should be used with ENABLEDELAYEDEXPANSION
echo !var! >> answer.txt
)
endlocal