Skip to main content

How to Convert a CSV File to a Different Delimiter in Batch Script

CSV (Comma-Separated Values) is a standard data format, but sometimes you need to work with systems that require a different separator, such as a pipe (|), a semicolon (;), or a tab. Converting a comma-delimited file to another delimiter is a common data transformation task. While this can be a complex string manipulation problem, it is surprisingly easy to solve with a clever "pure-batch" trick.

This guide will teach you the standard and remarkably simple pure-batch method for converting delimiters using a FOR /F loop and variable substitution. We will also cover the more robust and powerful modern approach using a PowerShell one-liner, which is recommended for its reliability and ability to handle complex CSVs.

The Challenge: Replacing Only the Commas that Separate Fields​

A simple find and replace on the comma character is not a safe solution. It will fail if any of the data fields themselves contain a comma (e.g., "Smith, John"). A correct conversion requires a parser that understands the structure of a CSV file.

The Core Method (Pure Batch): The FOR /F Reassembly Trick​

This is a clever, self-contained batch solution that works for simple CSV files. It does not replace the commas; instead, it deconstructs and then reconstructs each line with a new delimiter.

The logic:

  1. Use a FOR /F loop to read the CSV file line by line, using the comma as the delimiter. This splits each line into its individual fields.
  2. Inside the loop, use ECHO to print the captured fields back out, but place your new desired delimiter between them.
  3. Redirect the output of this ECHO command to a new file.

For any CSV file that might be complex (containing quoted commas, empty fields, or different encodings), a PowerShell one-liner is the definitive and professional solution. It uses a real CSV parser.

Syntax: powershell -Command "Import-Csv 'input.csv' | Export-Csv 'output.csv' -NoTypeInformation -Delimiter '|'"

  • Import-Csv: Reads and correctly parses the source CSV.
  • Export-Csv: Writes the data back out to a new file, using the new delimiter you specify.
  • -NoTypeInformation: Prevents a #TYPE header from being added to the output file.

Basic Example: Converting Commas to Pipes​

Let's convert a simple CSV file.

data.csv
ID,Username,Status
101,Alice,Active
102,Bob,Inactive

Method 1: Pure Batch Script​

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET "InputFile=data.csv"
SET "OutputFile=data_piped.txt"
SET "NewDelimiter=|"

ECHO --- Converting CSV from comma to pipe delimiter ---

(FOR /F "tokens=1,2,3 delims=," %%A IN (%InputFile%) DO (
    ECHO %%A!NewDelimiter!%%B!NewDelimiter!%%C
)) > "%OutputFile%"

ECHO.
ECHO --- Conversion complete. Result: ---
TYPE "%OutputFile%"
ENDLOCAL

Method 2: PowerShell Script​

@ECHO OFF
SET "InputFile=data.csv"
SET "OutputFile=data_piped_ps.txt"
SET "NewDelimiter=|"

powershell -Command "Import-Csv '%InputFile%' | Export-Csv '%OutputFile%' -NoTypeInformation -Delimiter '%NewDelimiter%'"

ECHO --- Conversion complete. Result: ---
TYPE "%OutputFile%"

Output (for both methods, in the new file):

"ID"|"Username"|"Status"
"101"|"Alice"|"Active"
"102"|"Bob"|"Inactive"
note

PowerShell automatically quotes all fields by default, which is a good practice.

How the Pure Batch Script Works​

The command FOR /F "tokens=1,2,3 delims=," %%A IN (...) is the key.

  1. It reads a line (e.g., 101,Alice,Active).
  2. It uses the comma delims=, to split it into three pieces (tokens).
  3. It assigns these pieces to the variables %%A ("101"), %%B ("Alice"), and %%C ("Active").
  4. The command ECHO %%A!NewDelimiter!%%B!NewDelimiter!%%C then reassembles these pieces into a new string, but uses our | delimiter instead of the original comma.
  5. The (...) > "%OutputFile%" part redirects the entire output of the FOR loop into our new file.

Common Pitfalls and How to Solve Them​

Problem: The CSV Contains Quoted Commas​

This is the fatal flaw of the pure-batch method. If a field contains a comma inside quotes (e.g., "New York, NY"), the FOR /F parser will incorrectly split it, corrupting your data.

Solution: This is the primary reason to use the PowerShell method. Import-Csv is a proper CSV parser that understands quotes and will handle this case correctly and automatically. The batch method cannot be reliably fixed for this.

Problem: The Number of Columns is Unknown​

The pure-batch script requires you to explicitly define how many tokens to expect (e.g., tokens=1,2,3). If your file has a variable number of columns, this will fail.

Solution: Again, the PowerShell method is superior. It automatically handles any number of columns without requiring you to specify a count.

Practical Example: A Reusable CSV Converter Script​

This script is a flexible utility that takes the input file, output file, and new delimiter as command-line arguments. It uses the robust PowerShell method.

@ECHO OFF
SETLOCAL
TITLE CSV Delimiter Converter

SET "InputFile=%~1"
SET "OutputFile=%~2"
SET "NewDelimiter=%~3"

IF "%NewDelimiter%"=="" (
    ECHO [ERROR] Not enough arguments.
    ECHO Usage: %~n0 <input_file> <output_file> <new_delimiter>
    ECHO Example: %~n0 data.csv data.psv "|"
    GOTO :End
)

ECHO --- CSV Converter ---
ECHO Input File:    "%InputFile%"
ECHO Output File:   "%OutputFile%"
ECHO New Delimiter: "%NewDelimiter%"
ECHO.

IF NOT EXIST "%InputFile%" (ECHO [ERROR] Input file not found. & GOTO :End)

ECHO Converting...
powershell -NoProfile -ExecutionPolicy Bypass -Command ^
    "Import-Csv '%InputFile%' | Export-Csv '%OutputFile%' -NoTypeInformation -Delimiter '%NewDelimiter%'"

ECHO.
ECHO [SUCCESS] Conversion complete.

:End
ENDLOCAL

Conclusion​

While you can convert simple CSV files using a clever "pure-batch" FOR /F trick, this method is fragile and should be avoided for any data that might be even moderately complex.

  • The pure-batch FOR /F method is a good demonstration of parsing logic but fails on common real-world CSV features like quoted commas.
  • The PowerShell Import-Csv | Export-Csv method is the overwhelmingly recommended best practice. It is a true CSV parser that is reliable, powerful, and simpler to use for this task.

For any data transformation involving CSV files, the PowerShell one-liner is the professional and correct choice.

Table of Contents