How to Read Data from an Excel/CSV Spreadsheet in Batch Script

Learn how to parse CSV files in Windows Batch scripts using FOR /F to loop through data rows, skip headers, and handle delimiters for automation tasks like user creation or server checks.

Batch scripts frequently need to process large lists of servers to ping, Active Directory users to create, or IP addresses to scan. Storing these lists natively in the script is messy. A cleaner approach is to use an external flat file. By storing your data in a Comma-Separated Values (CSV) formatted file (which opens universally in Microsoft Excel), you empower business operators to manage the input data without altering the code.

In this guide, we will demonstrate how to read a users.csv file, skip the header row, and execute commands on each data row.

The Strategy: Parse and Loop

Use the FOR /F command to loop line-by-line over the file.
Use the skip=1 parameter to ignore the header row.
Use the delims=, parameter to break each line into individual columns (tokens).
Map the tokens to script variables and execute your logic.

Setup: Create the Input File (`users.csv`)

:: Create a sample users.csv file manually
(
    echo FirstName,LastName,Department
    echo John,Doe,HR
    echo Jane,Smith,IT
    echo Alice,Johnson,Finance
    echo Bob,Wilson,Marketing
) > "users.csv"

Implementation Script

@echo off
setlocal enabledelayedexpansion

:: Get script directory to avoid drive/path issues
set "scriptDir=%~dp0"
set "inputFile=%scriptDir%users.csv"

echo ==========================================
echo         CSV USER IMPORT TOOL
echo ==========================================
echo File: !inputFile!
echo.

:: Ensure the input file exists
if not exist "!inputFile!" (
    echo [ERROR] File not found.
    echo Expected: !inputFile!
    pause
    exit /b
)

set "rowCount=0"

:: Read CSV safely
for /f "usebackq skip=1 tokens=1,2,3 delims=," %%A in (`type "!inputFile!"`) do (

    set "firstName=%%A"
    set "lastName=%%B"
    set "department=%%C"

    if "!firstName!"=="" (
        echo [SKIP] Missing first name
    ) else if "!lastName!"=="" (
        echo [SKIP] Missing last name
    ) else if "!department!"=="" (
        echo [SKIP] Missing department
    ) else (
        set /a rowCount+=1

        echo [!rowCount!] Creating account: !firstName! !lastName! ^(!department!^)

        REM net user "!firstName!.!lastName!" Password123 /add ^
        REM /fullname:"!firstName! !lastName!" ^
        REM /comment:"!department!"
    )
)

echo.
echo ------------------------------------------
echo Completed: !rowCount! valid rows processed
echo ------------------------------------------

pause
endlocal

Output:

==========================================
        CSV USER IMPORT TOOL
==========================================
File: C:\Users\David\Desktop\users.csv

[1] Creating account: John Doe (HR)
[2] Creating account: Jane Smith (IT)
[3] Creating account: Alice Johnson (Finance)
[4] Creating account: Bob Wilson (Marketing)

------------------------------------------
Completed: 4 valid rows processed
------------------------------------------

Why Read Data from CSV?

Mass User Creation: Reading FirstName,LastName,Email from an HR spreadsheet to automate Active Directory onboarding.
Server Health Checks: Keeping a spreadsheet of Hostname,IPAddress,Environment and having the script iterate through it to test connectivity.
Separation of Logic and Data: Non-technical staff can update the input spreadsheet (Excel) without risking accidentally breaking the Batch script syntax.

Important Considerations

Quoted Fields with Commas

If a cell in the CSV contains a comma (e.g., "Doe, John",IT), Batch will incorrectly split the field at the comma, shifting all subsequent columns. Pure Batch cannot natively handle RFC 4180 CSV quoting. The CSV must be "clean", meaning no embedded commas within fields. If your data requires commas, consider using a different delimiter:

:: Use pipe (|) as delimiter for data containing commas
for /f "usebackq skip=1 tokens=1,2,3 delims=|" %%A in ("!inputFile!") do (
    ...
)

Semicolon Delimiters

In some European locales, Excel natively exports CSVs using semicolons (;) instead of commas. If the script reads all data into a single token, change the delimiter:

for /f "usebackq skip=1 tokens=1,2,3 delims=;" %%A in ("!inputFile!") do (

Handling More Complex CSV with PowerShell

For CSV files with quoted fields, embedded commas, or Unicode content, use PowerShell's Import-Csv which fully supports RFC 4180:

@echo off
setlocal enabledelayedexpansion

set "inputFile=users.csv"

:: Process each row using PowerShell's proper CSV parser
for /f "usebackq tokens=1,2,3 delims=," %%A in (`
    powershell -NoProfile -Command "Import-Csv '%inputFile%' | ForEach-Object { $_.FirstName + ',' + $_.LastName + ',' + $_.Department }"
`) do (
    set "firstName=%%A"
    set "lastName=%%B"
    set "department=%%C"

    echo Creating account for: !firstName! !lastName! ^(!department!^)
)

pause