Skip to content
Tutorial Reference

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

Learn how to convert legacy fixed-width text files to CSV format using Batch scripting with string slicing and proper column extraction for modern data tools.

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

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.

Navigation