Skip to main content

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

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​

  1. Use the FOR /F command to loop line-by-line over the file.
  2. Use the skip=1 parameter to ignore the header row.
  3. Use the delims=, parameter to break each line into individual columns (tokens).
  4. 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
------------------------------------------
How Token Mapping Works

The tokens=1,2,3 option assigns the first three comma-delimited fields to %%A, %%B, and %%C respectively. Batch automatically assigns consecutive letters, %%A gets token 1, %%B gets token 2, and %%C gets token 3. If your CSV has more columns, extend the token list (e.g., tokens=1,2,3,4,5) and the variables continue alphabetically through %%D, %%E, and so on.

Why Read Data from CSV?​

  1. Mass User Creation: Reading FirstName,LastName,Email from an HR spreadsheet to automate Active Directory onboarding.
  2. Server Health Checks: Keeping a spreadsheet of Hostname,IPAddress,Environment and having the script iterate through it to test connectivity.
  3. 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 (
    ...
)
Empty Fields Shift Columns

If a row has a missing value between delimiters (e.g., John,,HR), Batch's for /f tokenizer skips the empty token entirely. This shifts HR into the %%B (lastName) position, corrupting all subsequent fields. To prevent this, ensure every column has a value, using a placeholder like N/A for missing data.

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 (
Saving from Excel

When saving from Excel, choose "CSV (Comma delimited) (*.csv)", not "CSV UTF-8." The UTF-8 variant adds a Byte Order Mark (BOM) that Batch's for /f reads as part of the first field on the first data row. If you must use UTF-8, add chcp 65001 >nul at the start of your script and increase skip= by 1 if the BOM causes issues.

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

Output:

Creating account for: John Doe (HR)
Creating account for: Jane Smith (IT)
Creating account for: Alice Johnson (Finance)
Creating account for: Bob Wilson (Marketing)
Why PowerShell for Complex CSV?

PowerShell's Import-Csv correctly handles quoted fields, embedded commas, multi-line fields, and various encodings. It parses the CSV into objects with named properties, which you can then output in a clean, Batch-friendly format for the for /f loop to consume.

Conclusion​

Reading data from external CSVs unlocks the power of parameter-driven automation. By separating your target lists from your logic, you build safer, more maintainable scripts. While Batch handles flat, comma-separated lists efficiently, ensuring clean structure without enclosed commas is essential. Mastering the for /f looping construct is the gateway to professional-grade data imports in Windows system administration.

Table of Contents