CSV Blank Cell Filler

What does the CSV Blank Cell Filler do?

The CSV Blank Cell Filler detects empty, null, or whitespace-only cells in a selected CSV column (or across all columns) and replaces them with a user-specified replacement value (such as "N/A", "0", "NULL", or "-").

In production data engineering, missing values can disrupt machine learning pipelines, break SQL database imports, and generate parsing errors in analytics dashboards. The CSV Blank Cell Filler standardizes missing data across records while strictly maintaining RFC 4180 quotation escaping, column indexing, and multiline table structures.

Core Concepts

• Empty vs Whitespace Cells: Standard empty cells (,,) contain zero characters. When Treat Spaces as Blank is enabled, cells containing only spaces or tabs (," ",) are also detected and filled.
• Single Column or Table-Wide: You can target a specific column that requires default values (e.g. replacing missing discounts with 0) or fill all missing data points across the entire spreadsheet.
• Header Preservation: By default, column headers in row 1 remain untouched unless Also Fill Header is explicitly checked.
• RFC 4180 Escaping: Replacement values containing commas or quotes are automatically wrapped in double quotes to preserve CSV integrity.

How to use the tool?

1. Input CSV Data: Paste comma-delimited data into the editor or click Load Sample.
2. Select Target Column: Choose a specific column (e.g. price) or select All Columns.
3. Set Fill Value: Enter your desired replacement text (e.g. N/A, 0, NULL, Unknown).
4. Configure Options:
   • Treat Spaces as Blank: Treats whitespace-only cells as blank.
   • Also Fill Header: Applies filling to blank headers in the first row.
   • First Row is Header: Treats row 1 as column titles.
5. Fill Blank Cells: Click Fill Blank Cells to execute the substitution.
6. Export: Use Copy or Download to save the sanitized CSV file.

Related Developer Utilities

• CSV Column Trimmer — Strip leading and trailing whitespace from CSV columns.
• CSV Empty Row Remover — Clean blank and whitespace-only rows from CSV files.
• CSV Column Renamer — Rename specific CSV column headers.
• CSV Delimiter Converter — Convert delimiters between comma, semicolon, tab, and pipe.

REST API Integration

blueutils.com provides a free REST API endpoint (POST https://blueutils.com/api/csv/csv-blank-cell-filler) for programmatic integration.

API Request Parameters

API Request Payload Examples

cURL

curl -X POST https://blueutils.com/api/csv/csv-blank-cell-filler \
  -H "Content-Type: application/json" \
  -d '{
    "rawText": "id,name,price\n101,Mouse,\n102,Keyboard,49.99",
    "options": {
      "columnIndex": 2,
      "fillValue": "0.00"
    }
  }'

Python

import requests

url = "https://blueutils.com/api/csv/csv-blank-cell-filler"
payload = {
    "rawText": "id,name,price\n101,Mouse,\n102,Keyboard,49.99",
    "options": {
        "columnIndex": 2,
        "fillValue": "0.00"
    }
}
headers = {"Content-Type": "application/json"}
response = requests.post(url, json=payload, headers=headers)
print(response.json())

Java

import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;

public class Main {
    public static void main(String[] args) throws Exception {
        String jsonPayload = """
        {
          "rawText": "id,name,price\\n101,Mouse,\\n102,Keyboard,49.99",
          "options": {
            "columnIndex": 2,
            "fillValue": "0.00"
          }
        }
        """;

        HttpClient client = HttpClient.newHttpClient();
        HttpRequest request = HttpRequest.newBuilder()
            .uri(URI.create("https://blueutils.com/api/csv/csv-blank-cell-filler"))
            .header("Content-Type", "application/json")
            .POST(HttpRequest.BodyPublishers.ofString(jsonPayload))
            .build();

        HttpResponse<String> response = client.send(request, HttpResponse.BodyHandlers.ofString());
        System.out.println(response.body());
    }
}

API Response Parameters

API Response Payload Examples

Success Response (HTTP 200 OK)

{
  "isValid": true,
  "result": "id,name,price\n101,Mouse,0.00\n102,Keyboard,49.99",
  "headers": ["id", "name", "price"],
  "targetColumnIndex": 2,
  "targetColumnName": "price",
  "fillValue": "0.00",
  "treatWhitespaceAsBlank": true,
  "includeHeader": false,
  "rowCount": 2,
  "columnCount": 3,
  "filledCount": 1
}

Validation Failure Response (HTTP 400 Bad Request)

{
  "isValid": false,
  "error": "CSV input cannot be empty."
}

Rate Limit Exceeded Response (HTTP 429 Too Many Requests)

{
  "error": "API rate limit exceeded. Please wait or contact support@blueutils.com."
}

Why use an API to fill blank CSV cells?

Integrating the CSV Blank Cell Filler API into ETL workflows, automated data import microservices, or AI ingestion pipelines provides several advantages:

• Rapid Script Validation: Pre-process third-party vendor CSV dumps to replace missing columns with default fallback constants before database insertion.
• Optimized Token Efficiency for AI Agents: Replacing empty fields with predictable tokens (e.g. NULL or None) improves LLM prompt coherence and eliminates token drift on irregular rows.
• Deterministic Accuracy Without Hallucinations: Language models can skip delimiters or misalign table matrices when filling empty spots. Delegating to a deterministic API guarantees 100% data integrity without hallucinations.

Native Usage

How to fill blank CSV cells locally without external dependencies using native system utilities:

Windows (CMD / PowerShell)

# PowerShell: Replace blank values in column index 2 with "N/A"
$csv = Import-Csv -Path "input.csv"
$headers = $csv[0].PSObject.Properties.Name
$targetCol = $headers[2]

$csv | ForEach-Object {
    if ([string]::IsNullOrWhiteSpace($_.$targetCol)) {
        $_.$targetCol = "N/A"
    }
}

$csv | Export-Csv -Path "output.csv" -NoTypeInformation

Linux / Unix (Bash / Shell)

# Bash / AWK: Fill empty cell in column 2 with "N/A"
awk -F',' 'BEGIN {OFS=","} {
    if (NR > 1 && ($2 == "" || $2 ~ /^[ \t]+$/)) {
        $2 = "N/A"
    }
    print
}' input.csv > output.csv

Python

# Python standard library (csv module)
import csv

input_file = 'input.csv'
output_file = 'output.csv'
target_column_index = 2
fill_value = 'N/A'

with open(input_file, mode='r', newline='', encoding='utf-8') as infile:
    reader = csv.reader(infile)
    rows = list(reader)

if rows:
    header = rows[0]
    data_rows = rows[1:]
    for row in data_rows:
        if target_column_index < len(row):
            if not row[target_column_index].strip():
                row[target_column_index] = fill_value

    with open(output_file, mode='w', newline='', encoding='utf-8') as outfile:
        writer = csv.writer(outfile)
        writer.writerow(header)
        writer.writerows(data_rows)

Java

// Java standard library (java.nio and java.util)
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;

public class CsvFiller {
    public static void main(String[] args) throws Exception {
        Path inputPath = Path.of("input.csv");
        Path outputPath = Path.of("output.csv");
        int targetCol = 2;
        String fillValue = "N/A";

        List<String> outputLines = new ArrayList<>();
        try (BufferedReader reader = Files.newBufferedReader(inputPath)) {
            String line;
            boolean isHeader = true;
            while ((line = reader.readLine()) != null) {
                if (isHeader) {
                    outputLines.add(line);
                    isHeader = false;
                    continue;
                }
                String[] cols = line.split(",", -1);
                if (targetCol < cols.length && cols[targetCol].trim().isEmpty()) {
                    cols[targetCol] = fillValue;
                }
                outputLines.add(String.join(",", cols));
            }
        }
        Files.write(outputPath, outputLines);
    }
}