Python pyexcel Guide: Convert CSV XLSX JSON

Data conversion is essential in programming. You often need to switch formats. pyexcel makes this easy in Python.

This guide shows how to convert between CSV, XLSX, and JSON. We use practical examples. You will learn quickly.

What is pyexcel?

pyexcel is a Python library. It handles spreadsheet data. You can read, write, and manipulate files.

It supports many formats. These include CSV, XLSX, and JSON. The API is simple and consistent.

If you need a complete introduction, check our Python pyexcel Tutorial: Read Write Excel CSV Files.

Installation and Setup

First, install pyexcel. Use pip for installation.

pip install pyexcel pyexcel-xlsx pyexcel-io

The pyexcel-xlsx plugin handles Excel files. pyexcel-io provides input/output support.

If you encounter installation issues, see Install pyexcel in Python with pip and Virtualenv.

For import problems, refer to Fix Python ImportError: No Module Named pyexcel.

Convert CSV to XLSX

Converting CSV to Excel is common. pyexcel makes it straightforward.

Use the save_as function. Provide the source and destination files.

import pyexcel as pe

# Convert CSV to XLSX
pe.save_as(file_name='data.csv', dest_file_name='data.xlsx')

print("CSV converted to XLSX successfully")

CSV converted to XLSX successfully

The file data.csv becomes data.xlsx. The process preserves all data.

Convert XLSX to CSV

Excel to CSV conversion is also simple. Use the same approach.

Call save_as with Excel as source. Specify CSV as destination.

import pyexcel as pe

# Convert XLSX to CSV
pe.save_as(file_name='data.xlsx', dest_file_name='data.csv')

print("XLSX converted to CSV successfully")

XLSX converted to CSV successfully

This creates a CSV file from Excel. It maintains data structure.

Convert JSON to XLSX

JSON data often needs spreadsheet format. pyexcel handles this well.

First, prepare your JSON data. Then use save_as with appropriate parameters.

import pyexcel as pe

# Sample JSON data
json_data = [
    ["Name", "Age", "City"],
    ["John", 30, "New York"],
    ["Alice", 25, "London"],
    ["Bob", 35, "Tokyo"]
]

# Convert JSON to XLSX
pe.save_as(array=json_data, dest_file_name='data.xlsx')

print("JSON converted to XLSX successfully")

JSON converted to XLSX successfully

The JSON array becomes an Excel file. Each sub-array is a row.

Convert XLSX to JSON

Extract data from Excel as JSON. Use the get_array function.

This reads the Excel file. It returns data as a Python list.

import pyexcel as pe
import json

# Convert XLSX to JSON array
data_array = pe.get_array(file_name='data.xlsx')

# Convert to JSON string
json_output = json.dumps(data_array)

print("XLSX converted to JSON:")
print(json_output)

XLSX converted to JSON:
[["Name", "Age", "City"], ["John", 30, "New York"], ["Alice", 25, "London"], ["Bob", 35, "Tokyo"]]

You get structured JSON data. This is ready for web applications.

Convert CSV to JSON

Transform CSV data into JSON format. Combine file reading with JSON conversion.

Use get_array to read CSV. Then convert to JSON.

import pyexcel as pe
import json

# Convert CSV to JSON
data_array = pe.get_array(file_name='data.csv')
json_output = json.dumps(data_array)

print("CSV converted to JSON:")
print(json_output)

CSV converted to JSON:
[["Name", "Age", "City"], ["John", 30, "New York"], ["Alice", 25, "London"], ["Bob", 35, "Tokyo"]]

CSV headers become JSON keys. Data rows become array elements.

Convert JSON to CSV

Store JSON data as CSV file. Use the array parameter with save_as.

Pass your JSON data as a list. Save it as CSV format.

import pyexcel as pe

# JSON data to convert
json_data = [
    ["Product", "Price", "Quantity"],
    ["Laptop", 999, 5],
    ["Mouse", 25, 20],
    ["Keyboard", 75, 15]
]

# Convert JSON to CSV
pe.save_as(array=json_data, dest_file_name='products.csv')

print("JSON converted to CSV successfully")

JSON converted to CSV successfully

This creates a CSV file. It contains your JSON data in tabular form.

Handling Large Files

pyexcel works with large datasets. It processes data efficiently.

Use streaming for big files. This prevents memory issues.

Read files in chunks. Process data incrementally.

Error Handling

Always include error handling. This makes your code robust.

Use try-except blocks. Catch common file errors.

import pyexcel as pe

try:
    pe.save_as(file_name='missing.csv', dest_file_name='output.xlsx')
    print("Conversion successful")
except FileNotFoundError:
    print("Error: Source file not found")
except Exception as e:
    print(f"Conversion failed: {str(e)}")

Error: Source file not found

Proper error handling prevents crashes. It provides helpful messages.

Best Practices

Follow these tips for better code. They improve reliability.

Always check file existence. Validate data before conversion.

Use meaningful file names. Include error handling as shown.

Test with sample data first. Then use production files.

Conclusion

pyexcel simplifies format conversion. It handles CSV, XLSX, and JSON seamlessly.

You learned key conversion methods. These include save_as and get_array.

The examples show practical usage. You can apply them directly.

pyexcel is powerful for data processing. It saves time and effort.

Start using these techniques today. Enhance your data workflow.