json-and-csv-data-transformation

JSON and CSV Data Transformation

Transform data between JSON, CSV, and other formats. Filter, map, flatten nested objects, and reshape data for analysis, reporting, and API integration.

When to use

• Use case 1: When the user asks to convert data between JSON and CSV formats
• Use case 2: When you need to filter, extract, or transform specific fields from data
• Use case 3: For flattening nested JSON structures into tabular format
• Use case 4: When processing API responses for analysis or reporting

Required tools / APIs

• jq — Command-line JSON processor (essential for JSON manipulation)
• csvkit — Suite of CSV tools (csvjson, csvcut, csvgrep, etc.)
• No external API required

Install options:

# Ubuntu/Debian
sudo apt-get install -y jq csvkit

# macOS
brew install jq csvkit

# Node.js (native support, no packages needed for basic operations)
# For advanced CSV parsing: npm install csv-parse csv-stringify

Skills

json_to_csv

Convert JSON array to CSV format.

# Simple JSON array to CSV
echo '[{"name":"Alice","age":30},{"name":"Bob","age":25}]' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, (map([.[ $keys[] ]]) | .[] | @csv)'

# JSON file to CSV file
jq -r '(.[0] | keys_unsorted) as $keys | $keys, (map([.[ $keys[] ]]) | .[] | @csv)' data.json > output.csv

# JSON to CSV with specific fields
jq -r '.[] | [.id, .name, .email] | @csv' users.json

# Using csvkit (simpler syntax)
cat data.json | in2csv -f json > output.csv

Node.js:

function jsonToCSV(jsonArray) {
  if (!Array.isArray(jsonArray) || jsonArray.length === 0) {
    return '';
  }
  
  // Get headers from first object
  const headers = Object.keys(jsonArray[0]);
  
  // Escape CSV values
  const escape = (val) => {
    if (val === null || val === undefined) return '';
    const str = String(val);
    if (str.includes(',') || str.includes('"') || str.includes('\n')) {
      return `"${str.replace(/"/g, '""')}"`;
    }
    return str;
  };
  
  // Build CSV
  const headerRow = headers.join(',');
  const dataRows = jsonArray.map(obj =>
    headers.map(header => escape(obj[header])).join(',')
  );
  
  return [headerRow, ...dataRows].join('\n');
}

// Usage
// const data = [
//   { name: 'Alice', age: 30, city: 'New York' },
//   { name: 'Bob', age: 25, city: 'San Francisco' }
// ];
// console.log(jsonToCSV(data));