CSV Null Value Replacer

What does the CSV Null Value Replacer do?

The CSV Null Value Replacer scans a selected column (or the entire table) for non-standard null tokens (such as "NULL", "N/A", "NA", "-", "--", "None", "nil", or "#N/A") and replaces them with a uniform target value, such as empty string "", zero "0", or "Unknown".

Different databases, business intelligence exports, and spreadsheets express missing data in conflicting ways: SQL engines emit NULL, spreadsheets display #N/A, REST APIs export None or nil, and legacy reporting software uses dashes -. When consolidating diverse datasets, these heterogeneous representations prevent numeric parsing, cause type coercion errors, and contaminate database columns. The CSV Null Value Replacer cleanses these values while strictly adhering to RFC 4180 quotation standards.

Core Concepts

• Heterogeneous Null Tokens: Matches multiple case-insensitive representations simultaneously (e.g. NULL, null, N/A, n/a, NA, -, None, nil).
• Custom Replacements: Choose whether to replace null representations with an empty cell (,,), a numeric zero (0), a database identifier (NULL), or custom descriptive text (Unknown).
• Whitespace Tolerance: Automatically trims cells before matching, ensuring padded entries like " N/A " or " - " are accurately caught.
• Header Isolation: Ensures column headers (e.g. an actual column named status-none) are preserved unless Also Replace Header is explicitly turned on.

How to use the tool?

1. Input CSV Data: Paste comma-delimited data into the input box or click Load Sample.
2. Select Target Column: Select a specific column (e.g. discount) or choose All Columns to sanitize the entire table.
3. Configure Null Patterns: Review and customize the list of null tokens to recognize.
4. Set Replacement Value: Enter what null values should turn into (e.g. 0, leave empty for clean commas, or Unknown).
5. Configure Matching Options:
   • Case Sensitive: Enable if you need strict matching on uppercase NULL vs lowercase null.
   • Trim Cells: Strips padding around cells before checking against null tokens.
   • Also Replace Header: Applies replacement to row 1.
   • First Row is Header: Treats row 1 as column titles.
6. Replace & Export: Click Replace Null Values, then Copy or Download the resulting CSV.

Related Developer Utilities

• CSV Blank Cell Filler — Replace empty or blank cells with custom placeholder values.
• 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.

REST API Integration

blueutils.com provides a free REST API endpoint (POST https://blueutils.com/api/csv/csv-null-value-replacer) for programmatic integration.

API Request Parameters

API Request Payload Examples

cURL

curl -X POST https://blueutils.com/api/csv/csv-null-value-replacer \
  -H "Content-Type: application/json" \
  -d '{
    "rawText": "id,product,discount\n101,Mouse,NULL\n102,Keyboard,N/A",
    "options": {
      "columnIndex": 2,
      "replacementValue": "0%",
      "nullRepresentations": ["NULL", "N/A"]
    }
  }'

Python

import requests

url = "https://blueutils.com/api/csv/csv-null-value-replacer"
payload = {
    "rawText": "id,product,discount\n101,Mouse,NULL\n102,Keyboard,N/A",
    "options": {
        "columnIndex": 2,
        "replacementValue": "0%",
        "nullRepresentations": ["NULL", "N/A"]
    }
}
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,product,discount\\n101,Mouse,NULL\\n102,Keyboard,N/A",
          "options": {
            "columnIndex": 2,
            "replacementValue": "0%",
            "nullRepresentations": ["NULL", "N/A"]
          }
        }
        """;

        HttpClient client = HttpClient.newHttpClient();
        HttpRequest request = HttpRequest.newBuilder()
            .uri(URI.create("https://blueutils.com/api/csv/csv-null-value-replacer"))
            .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,product,discount\n101,Mouse,0%\n102,Keyboard,0%",
  "headers": ["id", "product", "discount"],
  "targetColumnIndex": 2,
  "targetColumnName": "discount",
  "replacementValue": "0%",
  "nullRepresentations": ["null", "n/a"],
  "caseSensitive": false,
  "rowCount": 2,
  "columnCount": 3,
  "replacedCount": 2
}

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 replace CSV null values?

Integrating the CSV Null Value Replacer API into automated data pipelines, ETL ingest jobs, or agentic preprocessing provides significant workflow improvements:

• Rapid Script Validation: Pre-cleanses disparate multi-source CSV files before passing data to strict database import routines or strict schema validators.
• Optimized Token Efficiency for AI Agents: Strips irregular #N/A, NULL, and -- noise from LLM prompt matrices, resulting in fewer tokens and clearer context.
• Deterministic Accuracy Without Hallucinations: Language models can mistakenly rewrite surrounding text or alter cell alignment when cleaning tabular strings. Delegating to a deterministic API guarantees 100% data integrity.

Native Usage

How to replace non-standard null values locally without external dependencies:

Windows (CMD / PowerShell)

# PowerShell: Replace NULL, N/A, and - in column index 2 with ""
$csv = Import-Csv -Path "input.csv"
$headers = $csv[0].PSObject.Properties.Name
$targetCol = $headers[2]
$nullTokens = @("NULL", "N/A", "NA", "-", "None", "nil")

$csv | ForEach-Object {
    $val = $_.$targetCol
    if ($nullTokens -contains $val.Trim()) {
        $_.$targetCol = ""
    }
}

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

Linux / Unix (Bash / Shell)

# Bash / AWK: Replace NULL and N/A in column 3 with empty string
awk -F',' 'BEGIN {OFS=","} {
    if (NR > 1) {
        if ($3 ~ /^[ \t]*(NULL|null|N\/A|n\/a|-|None)[ \t]*$/) {
            $3 = ""
        }
    }
    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
replacement_val = '0'
null_tokens = {'null', 'n/a', 'na', '-', '--', 'none', 'nil', '#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 row[target_column_index].strip().lower() in null_tokens:
                row[target_column_index] = replacement_val

    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;
import java.util.Set;

public class CsvNullReplacer {
    public static void main(String[] args) throws Exception {
        Path inputPath = Path.of("input.csv");
        Path outputPath = Path.of("output.csv");
        int targetCol = 2;
        String replacement = "";
        Set<String> nullTokens = Set.of("null", "n/a", "na", "-", "--", "none", "nil", "#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 && nullTokens.contains(cols[targetCol].trim().toLowerCase())) {
                    cols[targetCol] = replacement;
                }
                outputLines.add(String.join(",", cols));
            }
        }
        Files.write(outputPath, outputLines);
    }
}