Skip to main content

How to Convert a Fixed-Width File to CSV in Batch Script

Many legacy mainframes and banking systems export data in Fixed-Width formats, where columns aren't separated by commas but by a specific number of characters (e.g., Column 1 is always characters 1-10, Column 2 is 11-25). While these look clean in a text editor, they are impossible for modern tools like Excel to parse without conversion. CSV Conversion allows you to insert standard delimiters, making the data accessible to modern automation.

In this guide, we will demonstrate how to slice strings and convert them to CSV using Batch variables.

The Strategy: String Slicing​

Batch variables use the syntax !var:~start,length! to extract specific parts of a string. To convert a fixed format:

  1. Read the file line-by-line.
  2. Extract each column using the known character coordinates.
  3. Echo the extracted values separated by commas.

Implementation Script​

Suppose your file has:

  • Column 1 (ID): Start 0, Length 5
  • Column 2 (Name): Start 5, Length 15
  • Column 3 (Dept): Start 20, Length 10
@echo off
setlocal enabledelayedexpansion

set "Source=LegacyExport.txt"
set "Output=CleanData.csv"

echo Converting Fixed-Width to CSV...

:: Write the header first
> "%Output%" echo ID,Name,Department

for /f "usebackq delims=" %%L in ("%Source%") do (
    set "line=%%L"

    :: Slice out the columns
    set "col1=!line:~0,5!"
    set "col2=!line:~5,15!"
    set "col3=!line:~20,10!"

    :: Remove trailing spaces by reading each value back through for /f
    for /f "tokens=*" %%A in ("!col1!") do set "col1=%%A"
    for /f "tokens=*" %%A in ("!col2!") do set "col2=%%A"
    for /f "tokens=*" %%A in ("!col3!") do set "col3=%%A"

    :: Remove leading spaces with a substitution loop
    :trimCol1
    if "!col1:~0,1!"==" " set "col1=!col1:~1!" & goto :trimCol1
    :trimCol2
    if "!col2:~0,1!"==" " set "col2=!col2:~1!" & goto :trimCol2
    :trimCol3
    if "!col3:~0,1!"==" " set "col3=!col3:~1!" & goto :trimCol3

    :: Append to CSV
    >> "%Output%" echo !col1!,!col2!,!col3!
)

echo [DONE] Conversion complete.

endlocal
pause
warning

The goto labels inside a for loop will break the loop iteration. See the restructured version below that moves the trimming and writing logic into a called subroutine so that labels can be used safely.

Because goto inside a for /f body terminates the loop, the reliable approach is to call a subroutine for each line:

@echo off
setlocal enabledelayedexpansion

set "Source=LegacyExport.txt"
set "Output=CleanData.csv"

echo Converting Fixed-Width to CSV...

:: Write the header first
> "%Output%" echo ID,Name,Department

for /f "usebackq delims=" %%L in ("%Source%") do (
    call :ProcessLine "%%L"
)

echo [DONE] Conversion complete.
endlocal
pause
goto :eof

:ProcessLine
    set "line=%~1"

    :: Slice out the columns
    set "col1=!line:~0,5!"
    set "col2=!line:~5,15!"
    set "col3=!line:~20,10!"

    :: Remove trailing spaces (tokens=* strips leading/trailing whitespace)
    for /f "tokens=*" %%A in ("!col1!") do set "col1=%%A"
    for /f "tokens=*" %%A in ("!col2!") do set "col2=%%A"
    for /f "tokens=*" %%A in ("!col3!") do set "col3=%%A"

    :: Remove any remaining leading spaces
    :trimC1
    if "!col1:~0,1!"==" " set "col1=!col1:~1!" & goto :trimC1
    :trimC2
    if "!col2:~0,1!"==" " set "col2=!col2:~1!" & goto :trimC2
    :trimC3
    if "!col3:~0,1!"==" " set "col3=!col3:~1!" & goto :trimC3

    :: Append to CSV
    >> "%Output%" echo !col1!,!col2!,!col3!
    goto :eof

Why Convert Fixed-Width to CSV?​

  1. Modern Interoperability: Most modern databases and reporting tools (like Power BI or Google Sheets) expect delimited data, not fixed-width.
  2. Size Reduction: Fixed-width files use trailing spaces to fill empty gaps, making the file much larger than it needs to be. A CSV only uses as much space as the data itself.
  3. Searchability: Searching for a specific value in a CSV is more reliable than searching a fixed-width file where a value might overlap two column boundaries.

Important Limitations​

  1. Alignment Sensitivity: If the legacy system changes its column widths even by 1 character, your script will slice in the wrong places and "shatter" your data. Always verify the schema.
  2. Blank Lines: As with most FOR /F loops, blank lines in the source will be skipped.
  3. Embedded Commas: If your legacy data contains a comma within the text (e.g., inside a "Name" field), your new CSV will have an "Extra" column. Consider using a semicolon (;) or pipe (|) as your delimiter if your data is "Dirty."

Conclusion​

Converting fixed-width files to CSV is a vital "Data Modernization" task. It bridges the gap between mid-century legacy systems and 21st-century automation tools. By mastering string slicing in Batch, you can programmatically extract structured information from rigid text blocks and turn it into a fluid, cloud-ready dataset that is easy to analyze, report on, and share.

Table of Contents