The CSV component lets users download data from your database as CSV (Comma-Separated Values) files, which can be opened in Excel, Google Sheets, or any spreadsheet application.
Two Usage Modes
The CSV component works differently depending on where you place it:
Use without the shell component to trigger immediate download. Data streams efficiently from database to browser without loading everything in memory.
SELECT 'csv' AS component, 'users.csv' AS filename;
SELECT username, email, created_at FROM users;
/download_users.sql → immediate download starts.
Use after the shell component to add a download button to your page. The entire CSV is embedded in the button element, so use this only for smaller datasets.
SELECT 'shell' AS component, 'Data Export' AS title;
SELECT 'csv' AS component,
'Download Users' AS title,
'users' AS filename,
'file-download' AS icon,
'green' AS color;
SELECT username, email, created_at FROM users LIMIT 100;
Basic Example
Simple CSV download:
SELECT 'csv' AS component, 'report.csv' AS filename;
SELECT * FROM sales WHERE year = 2024;
Custom Separator
Use different delimiters like semicolons or tabs:
-- Semicolon-separated
SELECT 'csv' AS component,
'data.csv' AS filename,
';' AS separator;
SELECT name, price, quantity FROM products;
-- Tab-separated (TSV)
SELECT 'csv' AS component,
'data.tsv' AS filename,
' ' AS separator;
SELECT * FROM inventory;
Excel Compatibility
Add a Byte Order Mark (BOM) for proper Excel encoding:
SELECT 'csv' AS component,
'report.csv' AS filename,
TRUE AS bom;
SELECT product_name, price, category FROM products;
SELECT 'shell' AS component, 'Reports' AS title;
SELECT 'csv' AS component,
'Download Sales Report' AS title,
'sales_2024' AS filename,
'download' AS icon,
'primary' AS color,
'lg' AS size,
';' AS separator,
TRUE AS bom;
SELECT
date,
product_name,
quantity,
revenue
FROM sales
WHERE year = 2024
ORDER BY date DESC;
Filtered Exports
Let users filter what they download:
export_page.sql
SELECT 'shell' AS component, 'Export Data' AS title;
SELECT 'form' AS component, 'GET' AS method;
SELECT 'select' AS type, 'category' AS name, 'Category' AS label,
json_array(
json_object('label', 'Electronics', 'value', 'electronics'),
json_object('label', 'Clothing', 'value', 'clothing'),
json_object('label', 'Food', 'value', 'food')
) AS options;
SELECT 'date' AS type, 'start_date' AS name, 'Start Date' AS label;
SELECT 'date' AS type, 'end_date' AS name, 'End Date' AS label;
SELECT 'button' AS component;
SELECT
'Download CSV' AS title,
'download_export.sql?category=' || COALESCE($category, 'all') ||
'&start_date=' || COALESCE($start_date, '2024-01-01') ||
'&end_date=' || COALESCE($end_date, '2024-12-31') AS link,
'green' AS color;
SELECT 'csv' AS component,
'export_' || $category || '.csv' AS filename;
SELECT *
FROM sales
WHERE
($category = 'all' OR category = $category)
AND date BETWEEN $start_date AND $end_date
ORDER BY date DESC;
Large Dataset Export
Stream millions of rows efficiently:
SELECT 'csv' AS component, 'complete_export.csv' AS filename;
-- This will stream data row by row
-- Memory usage stays constant regardless of table size
SELECT
id,
customer_name,
order_date,
total_amount
FROM orders
ORDER BY order_date DESC;
Multiple Export Options
Offer different exports on one page:
SELECT 'shell' AS component, 'Download Center' AS title;
SELECT 'csv' AS component,
'Active Users' AS title,
'active_users' AS filename,
'users' AS icon,
'blue' AS color;
SELECT username, email, last_login
FROM users
WHERE active = true;
SELECT 'csv' AS component,
'Recent Orders' AS title,
'recent_orders' AS filename,
'shopping-cart' AS icon,
'green' AS color;
SELECT order_id, customer, date, total
FROM orders
WHERE date > date('now', '-30 days');
SELECT 'csv' AS component,
'Product Inventory' AS title,
'inventory' AS filename,
'package' AS icon,
'orange' AS color;
SELECT product_name, sku, quantity, price
FROM products;
Scheduled Reports
Generate reports via cron jobs or scheduled tasks:
# Download daily sales report
curl -o sales_report_$(date +%Y%m%d).csv \
http://localhost:8080/reports/daily_sales.sql
SELECT 'csv' AS component,
'daily_sales_' || date('now') || '.csv' AS filename;
SELECT
date,
product_name,
SUM(quantity) AS total_quantity,
SUM(revenue) AS total_revenue
FROM sales
WHERE date = date('now')
GROUP BY date, product_name;
Aggregated Data Export
Export computed statistics:
SELECT 'csv' AS component, 'monthly_summary.csv' AS filename;
SELECT
strftime('%Y-%m', order_date) AS month,
COUNT(*) AS total_orders,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= date('now', '-12 months')
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month DESC;
Joined Data Export
Export from multiple tables:
SELECT 'csv' AS component, 'customer_orders.csv' AS filename;
SELECT
c.customer_name,
c.email,
c.country,
o.order_id,
o.order_date,
o.total_amount,
o.status
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= date('now', '-1 year')
ORDER BY c.customer_name, o.order_date;
Conditional Exports
Only allow authorized users to download:
-- Check authentication
SELECT 'redirect' AS component, 'login.sql' AS link
WHERE NOT EXISTS (
SELECT 1 FROM sessions
WHERE token = sqlpage.cookie('session_token')
AND expires_at > CURRENT_TIMESTAMP
);
-- Check permissions
SET user_role = (
SELECT role FROM users u
JOIN sessions s ON u.username = s.username
WHERE s.token = sqlpage.cookie('session_token')
);
SELECT 'text' AS component,
'Access Denied' AS contents
WHERE $user_role != 'admin';
-- Export for authorized users
SELECT 'csv' AS component, 'confidential_data.csv' AS filename
WHERE $user_role = 'admin';
SELECT * FROM sensitive_data WHERE $user_role = 'admin';
Top-level Parameters
Text displayed on the download button. Ignored when used as a header component.
Name of the downloaded file (without extension). The .csv extension is added automatically.
Character used to separate values. Common options:
"," - Comma (default)";" - Semicolon (common in Europe)"\t" - Tab (creates TSV files)
Button color. Only applies to button mode. Options: primary, secondary, success, danger, warning, info, light, dark, blue, green, red, etc.
Button size. Only applies to button mode. Options: sm (small), lg (large).
Include a Byte Order Mark at the beginning of the file. Set to true for better Excel compatibility, especially with non-English characters.
- Handles tables with millions of rows
- Constant memory usage
- Fast initial response
- No browser memory limits
- Recommended for exports > 1000 rows
- Entire CSV loaded in browser memory
- Can cause browser slowdown/crashes with large data
- Convenient for small datasets
- No separate page needed
- Recommended for exports < 1000 rows
Best Practices
Column Selection
Export only needed columns:
-- Good: specific columns
SELECT name, email, status FROM users;
-- Avoid: SELECT * includes internal IDs and timestamps
-- that users don't need
Descriptive Names
Use aliases for user-friendly column headers:
SELECT
customer_name AS "Customer Name",
order_date AS "Order Date",
total_amount AS "Total ($)",
status AS "Status"
FROM orders;
SELECT
product_name,
strftime('%Y-%m-%d', created_at) AS created_date,
strftime('%Y-%m-%d %H:%M:%S', updated_at) AS last_updated
FROM products;
NULL Handling
Replace NULLs with meaningful values:
SELECT
customer_name,
COALESCE(phone, 'N/A') AS phone,
COALESCE(notes, '') AS notes
FROM customers;
Troubleshooting
Special Characters
If special characters appear garbled in Excel:
SELECT 'csv' AS component,
'data.csv' AS filename,
TRUE AS bom; -- Add this
Commas in Data
If your data contains commas, values are automatically quoted. If issues persist, use a different separator:
SELECT 'csv' AS component,
';' AS separator; -- Use semicolon instead
Large File Timeout
For very large exports, increase SQLPage’s timeout or export in smaller chunks using filters.