What are ways to convert JSON to CSV locally without any third party tools
Categories:
Convert JSON to CSV Locally: No Third-Party Tools Required
Learn various methods to transform JSON data into CSV format using built-in command-line tools and scripting languages like PowerShell, without relying on external libraries or services.
Converting JSON (JavaScript Object Notation) to CSV (Comma Separated Values) is a common data manipulation task. While many online converters and third-party libraries exist, there are scenarios where you need to perform this conversion locally, perhaps due to security policies, lack of internet access, or a preference for native tools. This article explores several robust methods to achieve this using standard command-line utilities and scripting languages available on most operating systems, focusing on PowerShell for Windows and common Unix-like tools.
Understanding JSON and CSV Structures
Before diving into conversion methods, it's crucial to understand the fundamental differences between JSON and CSV. JSON is a hierarchical, semi-structured data format, often representing objects and arrays. CSV, on the other hand, is a flat, tabular data format where each line represents a record and fields are separated by a delimiter (typically a comma). The challenge in conversion often lies in flattening the hierarchical JSON structure into a two-dimensional table suitable for CSV.
flowchart LR
A[JSON Data] --> B{Parse JSON}
B --> C{Extract Keys/Headers}
C --> D{Extract Values}
D --> E{Flatten Data}
E --> F{Format as CSV Rows}
F --> G[CSV Output]General process for converting JSON to CSV
Method 1: Using PowerShell (Windows)
PowerShell offers powerful cmdlets for working with structured data, including JSON. The ConvertFrom-Json cmdlet parses JSON strings into PowerShell objects, and ConvertTo-Csv can then transform these objects into CSV format. This method is highly effective for well-structured JSON where all objects have the same properties or a consistent schema.
$jsonInput = @'
[
{
"id": 1,
"name": "Alice",
"city": "New York"
},
{
"id": 2,
"name": "Bob",
"city": "London"
}
]
'@
$jsonInput | ConvertFrom-Json | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath "output.csv" -Encoding UTF8
# To display content:
Get-Content "output.csv"
Converting a simple JSON array of objects to CSV using PowerShell
-NoTypeInformation parameter for ConvertTo-Csv is crucial to prevent PowerShell from adding a type information header (e.g., #TYPE System.Management.Automation.PSCustomObject) to the first line of your CSV, which is usually undesirable.Handling Nested JSON with PowerShell
When JSON contains nested objects or arrays, a direct ConvertFrom-Json | ConvertTo-Csv might not produce the desired flat CSV structure. In such cases, you need to manually flatten the objects before converting to CSV. This often involves iterating through the JSON objects and selecting/expanding properties.
$nestedJson = @'
[
{
"orderId": "A101",
"customer": {
"name": "Charlie",
"email": "charlie@example.com"
},
"items": [
{"product": "Laptop", "qty": 1},
{"product": "Mouse", "qty": 1}
]
},
{
"orderId": "A102",
"customer": {
"name": "Diana",
"email": "diana@example.com"
},
"items": [
{"product": "Keyboard", "qty": 1}
]
}
]
'@
$flatData = $nestedJson | ConvertFrom-Json | ForEach-Object {
$order = $_
$order.items | ForEach-Object {
[PSCustomObject]@{
OrderId = $order.orderId
CustomerName = $order.customer.name
CustomerEmail = $order.customer.email
Product = $_.product
Quantity = $_.qty
}
}
}
$flatData | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath "nested_output.csv" -Encoding UTF8
Get-Content "nested_output.csv"
Flattening nested JSON and converting to CSV in PowerShell
Method 2: Using Command-Line Tools (jq and awk/sed)
For Unix-like environments (Linux, macOS, WSL), a combination of jq (a lightweight and flexible command-line JSON processor) and standard text processing tools like awk or sed provides a powerful way to convert JSON to CSV. jq is technically a third-party tool, but it's so ubiquitous in command-line environments that it's often considered a de-facto standard for JSON manipulation and is typically available via package managers. If jq is strictly disallowed, Python or Node.js scripts (covered in other articles) would be the next best native options.
# Sample JSON data (e.g., in data.json)
cat <<EOF > data.json
[
{
"id": 1,
"name": "Alice",
"city": "New York"
},
{
"id": 2,
"name": "Bob",
"city": "London"
}
]
EOF
# Extract headers and data, then format as CSV
jq -r '(
.[] | keys_unsorted
) as $keys |
(
$keys | @csv
),
(
.[] | map(. | tostring) | @csv
)' data.json > output.csv
# Display content
cat output.csv
Converting simple JSON to CSV using jq
jq command above first extracts all keys from the first object to form the header row, then iterates through each object, converts its values to strings, and formats them as CSV rows. This approach assumes a consistent schema across all JSON objects in the array.Handling Nested JSON with jq
Flattening nested JSON with jq requires more complex filters, often involving flatten or custom object construction. The goal is to project the nested data into a single-level object structure that jq can then output as CSV.
# Sample nested JSON data (e.g., in nested_data.json)
cat <<EOF > nested_data.json
[
{
"orderId": "A101",
"customer": {
"name": "Charlie",
"email": "charlie@example.com"
},
"items": [
{"product": "Laptop", "qty": 1},
{"product": "Mouse", "qty": 1}
]
}
]
EOF
# Flatten nested JSON and convert to CSV using jq
jq -r '(
.[] |
.orderId as $orderId |
.customer.name as $customerName |
.customer.email as $customerEmail |
.items[] |
{
OrderId: $orderId,
CustomerName: $customerName,
CustomerEmail: $customerEmail,
Product: .product,
Quantity: .qty
} |
(keys_unsorted | @csv),
(map(. | tostring) | @csv)
)' nested_data.json | head -n 2 > nested_output.csv
# Note: The above jq command will repeat headers for each item.
# A more robust approach for headers is to get them once:
(jq -r '(
.[] |
.orderId as $orderId |
.customer.name as $customerName |
.customer.email as $customerEmail |
.items[] |
{
OrderId: $orderId,
CustomerName: $customerName,
CustomerEmail: $customerEmail,
Product: .product,
Quantity: .qty
}
) | (.[0] | keys_unsorted | @csv)' nested_data.json; \
jq -r '(
.[] |
.orderId as $orderId |
.customer.name as $customerName |
.customer.email as $customerEmail |
.items[] |
{
OrderId: $orderId,
CustomerName: $customerName,
CustomerEmail: $customerEmail,
Product: .product,
Quantity: .qty
} |
map(. | tostring) | @csv
)' nested_data.json) > nested_output_flat.csv
cat nested_output_flat.csv
Flattening nested JSON and converting to CSV using jq with separate header extraction
jq for nested structures, carefully consider how you want to flatten the data. If an array of objects is nested, you might need to create multiple rows in the CSV for each item in that array, duplicating parent data. The example above demonstrates this for items.