How do I maintain data types when converting CSV to JSON?

The Challenge of Type Conversion from CSV

CSV is fundamentally a text format—everything stored in a CSV file is technically a string of characters. When you open a CSV file in a text editor, you see only text: "42" looks no different from "hello". JSON, by contrast, distinguishes between different data types: strings, numbers, booleans, null values, arrays, and objects.

During CSV-to-JSON conversion, the converter must interpret text values and determine their appropriate JSON types. Should "42" become the JSON number 42 or the string "42"? Should "true" become the boolean true or the string "true"? Should an empty cell become null, an empty string "", or be omitted? These decisions are critical for downstream processing—a number 42 can be used in mathematical operations, while the string "42" cannot.

Type inference errors are subtle because the JSON remains valid and can be processed, but calculations become impossible and filtering fails. If an age field contains strings instead of numbers, filtering for ages greater than 30 will fail, and averaging ages produces nonsensical results.

Understanding JSON Data Types

JSON officially supports these fundamental types:

String: Text enclosed in double quotes. Examples: "hello", "2025-01-31", "42"

Number: Numeric values without quotes. Examples: 42, 3.14, -100, 1.5e10

Numbers in JSON include integers and floating-point values. There's no distinction between integer and float types—both are "number".

Boolean: true or false (lowercase, unquoted)

Null: Represents missing or unknown values. Written as null (lowercase, unquoted)

Array: Ordered list of values. Example: [1, 2, 3] or ["a", "b"]

Object: Key-value pairs. Example: {"name": "John", "age": 30}

When converting CSV to JSON, each cell value maps to one of these types. The choice impacts how the data can be used.

Automatic Type Detection Methods

Modern CSV-to-JSON converters use various approaches to detect types:

Pattern matching looks at the text representation to identify likely types:

• If a value matches the pattern of a number (digits, possibly with decimal point and minus sign), treat as number
• If a value is exactly "true" or "false" (case-insensitive), treat as boolean
• If a value is empty, treat as null or empty string
• Otherwise, treat as string

name,age,salary,active
John,30,95000,true
Jane,25,75000,false

With pattern matching:

• "John" → string (doesn't match number or boolean patterns)
• "30" → number (matches number pattern)
• "95000" → number (matches number pattern)
• "true" → boolean (exact match for "true")

Column sampling examines multiple rows to determine consistent types:

1. Look at the first 100 rows (or configurable amount)
2. For each column, determine the most common type
3. Apply that type to all values in the column

This handles inconsistent data better—if most values are numbers but one is "N/A", you can decide whether to treat the column as numbers (converting "N/A" to null) or as strings (keeping everything as text).

Schema inference with machine learning can be more sophisticated, analyzing patterns and using statistical methods to determine likely types. This is overkill for most CSV conversion but valuable for complex, messy data.

Handling Common Type Scenarios

Numeric values should become JSON numbers:

age,price,quantity
30,99.99,5
25,49.50,3

Should convert to:

[
  {"age": 30, "price": 99.99, "quantity": 5},
  {"age": 25, "price": 49.50, "quantity": 3}
]

Not:

[
  {"age": "30", "price": "99.99", "quantity": "5"},
  {"age": "25", "price": "49.50", "quantity": "3"}
]

The key is that quotation marks (required in JSON for string values) should not appear around the numbers.

Leading zeros require special handling:

code,id
007,001
042,999

If code represents a numeric code that should retain leading zeros (like a product code or license number), it should be a string:

[
  {"code": "007", "id": "001"},
  {"code": "042", "id": "999"}
]

But if "code" is truly numeric:

[
  {"code": 7, "id": 1},
  {"code": 42, "id": 999}
]

The difference matters: string "007" preserves the original format, number 7 enables arithmetic but loses the original representation.

Boolean values in CSV often appear as various representations:

name,active,verified,premium
John,true,yes,1
Jane,false,no,0
Bob,TRUE,YES,TRUE

Your converter needs to recognize that "true", "TRUE", "yes", "YES", "1", and similar all mean boolean true, while "false", "FALSE", "no", "NO", "0" mean boolean false.

Date and time values present challenges:

date,timestamp
2025-01-31,2025-01-31 14:30:00
1/31/2025,31-01-2025

JSON has no native date type—dates must be strings or numbers (Unix timestamp). Best practice is using ISO 8601 format (YYYY-MM-DD):

[
  {"date": "2025-01-31", "timestamp": "2025-01-31T14:30:00Z"},
  {"date": "2025-01-31", "timestamp": "2025-01-31"}
]

Don't convert to numbers unless you have specific reason to use Unix timestamps.

Null and empty values need consistent handling:

name,email,phone
John,[email protected],555-1234
Jane,,
Bob,[email protected],555-5678

When converting:

• Empty cells should become null: {"name": "Jane", "email": null, "phone": null}
• NOT empty strings: {"name": "Jane", "email": "", "phone": ""}
• NOT missing properties: {"name": "Jane"}

Consistent null representation is important for downstream validation.

Special values like "N/A", "NA", "TBD", "-", etc. should be normalized:

score,status
95,Complete
N/A,In Progress
87,Complete

Best practice is converting recognizable null indicators to JSON null:

[
  {"score": 95, "status": "Complete"},
  {"score": null, "status": "In Progress"},
  {"score": 87, "status": "Complete"}
]

This requires configuring your converter to recognize what values represent missing data.

Manual Type Specification

For critical conversions, manually specify types rather than relying on auto-detection:

// Pseudo-code for manual type specification
const typeSpec = {
  id: "number",
  name: "string",
  email: "string",
  salary: "number",
  active: "boolean",
  created_date: "string"  // Keep as string, don't convert to number
};

const json = convertCSV(csvData, typeSpec);

This approach eliminates guessing and ensures the exact types you expect.

In programming languages:

Python with pandas:

import pandas as pd

df = pd.read_csv('data.csv', dtype={
  'id': int,
  'name': str,
  'salary': float,
  'active': bool
})

json_data = df.to_json(orient='records')

JavaScript/Node.js with custom conversion:

const typeSpec = {
  id: 'number',
  salary: 'number',
  active: 'boolean'
};

const rows = csvRows.map(row => {
  const obj = {};
  for (const [key, value] of Object.entries(row)) {
    if (typeSpec[key] === 'number') {
      obj[key] = parseFloat(value);
    } else if (typeSpec[key] === 'boolean') {
      obj[key] = value.toLowerCase() === 'true';
    } else {
      obj[key] = value;
    }
  }
  return obj;
});

Validation and Type Checking

After conversion, validate that types are correct:

// Validate specific fields are numbers
const isValidNumber = (value) => typeof value === 'number' && !isNaN(value);

// Validate all salary values are numbers
const allValid = data.every(record => isValidNumber(record.salary));

if (!allValid) {
  console.error('Some salary values are not valid numbers');
}

Use schema validation libraries to enforce type correctness:

JSON Schema (with libraries like ajv):

{
  "type": "object",
  "properties": {
    "id": {"type": "number"},
    "name": {"type": "string"},
    "salary": {"type": "number"},
    "active": {"type": "boolean"},
    "email": {"type": ["string", "null"]}
  },
  "required": ["id", "name", "salary"]
}

This schema enforces that id, name, and salary must exist, that id and salary are numbers, and that name is a string. The email field can be either string or null.

Handling Type Conflicts and Ambiguity

What happens when a column has mixed types?

value
42
hello
true
3.14
null

This column contains numbers, strings, booleans, and null—no single type fits. Options:

1. Coerce all to strings: ["42", "hello", "true", "3.14", "null"] - Safe but loses type information

2. Detect the "most common" type: If 80% are numbers, treat all as numbers. Values that don't fit convert to null: [42, null, null, 3.14, null] - Loses data

3. Keep as mixed types: [42, "hello", true, 3.14, null] - Maintains information but breaks type safety

4. Create a separate field for type:

[
  {"value": "42", "value_type": "number"},
  {"value": "hello", "value_type": "string"}
]

• Preserves information but requires special handling

Choose based on your data and downstream requirements.

Type Safety in Data Pipelines

For critical applications, implement type safety:

At conversion time: Validate all values match expected types before writing JSON

At import time: Validate JSON against schema before using in application

At runtime: Perform type checks before operations:

if (typeof user.age !== 'number') {
  throw new Error('Expected age to be a number');
}

const adultAge = user.age + 18;  // Safe, guaranteed to be number

This defense-in-depth approach catches type errors at the earliest opportunity.

Best Practices for Type Maintenance

1. Specify types explicitly: Don't rely on auto-detection for important data. Specify expected types in your conversion configuration.

2. Test with edge cases: Include values like "0", "00", "0.0", "-1", "1e10" to ensure numeric interpretation works correctly.

3. Normalize representations: Standardize how you represent types in CSV (always "true"/"false" for booleans, never "yes"/"no").

4. Document type assumptions: Record what types you expect for each field and how you're interpreting values.

5. Validate output: Always validate converted JSON has the expected types before relying on it.

6. Keep original CSV: Retain source CSV in case type assumptions were wrong and re-conversion is needed.

7. Use established libraries: Don't write custom parsing. Use libraries designed for CSV-to-JSON conversion that have been tested extensively.

Common Type Pitfalls

ZIP codes: "01234" should be string, not number (leading zeros matter)

Phone numbers: Should be string or separate into components, not number

IDs: Depends on usage. If you'll do arithmetic, use number. If just identifying records, use string.

Percentages: Store as numbers (0-100) not strings ("95%")

Currency: Store as numbers with separate currency code, not strings ("$95.99")

Boolean indicators: Standardize on "true"/"false" or "yes"/"no", not mixed

Conclusion

Maintaining data types during CSV-to-JSON conversion requires understanding that CSV is text-based and JSON supports multiple types. Automatic type detection works for straightforward cases but can fail on edge cases. For critical data, specify types explicitly, test thoroughly, validate output, and document your type handling approach. Using proper libraries and implementing validation at multiple stages ensures your converted JSON maintains data type integrity throughout your data pipeline.