Skip to content
BSWEN
Blog

How to Export H2 Database Data to CSV File

Cowrie
Cowrie
Dev @ Bswen

Problem

I had an H2 database with some test data, and I needed to export the data to a CSV file. I wanted to share the data with a colleague who doesn’t have database tools installed, and they just wanted to open it in a spreadsheet.

I tried looking for export tools, but I realized H2 has a built-in function for this.

Purpose

I wanted to:

  • Extract H2 database table data to CSV format
  • Share the data with tools that read CSV (spreadsheets, analytics)
  • Filter and transform the data during export

The Solution

H2 has a built-in CSVWRITE function that exports query results directly to a CSV file. You can use it with any SELECT statement.

Basic Export

export-users.sql
CALL CSVWRITE(
  '/tmp/users-export.csv',
  'SELECT * FROM users'
);

This exports the entire users table to a CSV file at the specified path.

Filtered Export

I also needed to export only active orders with user names joined from another table:

export-active-orders.sql
CALL CSVWRITE(
  '/tmp/active-orders.csv',
  'SELECT o.id, o.total, u.name FROM orders o
   JOIN users u ON o.user_id = u.id
   WHERE o.status = ''ACTIVE''
   ORDER BY o.created_at DESC'
);

Note the double single quotes (''ACTIVE'') to escape the string within the query string.

What Gets Exported

CSV output
ID,TOTAL,NAME
101,250.00,John Doe
102,99.50,Jane Smith
103,175.25,Bob Wilson

The CSV file includes:

  • Column headers from the SELECT statement
  • Data rows from the query result

What it doesn’t include:

  • Table schema or metadata
  • Indexes or constraints
  • Auto-increment sequences

What I Learned

  1. CSVWRITE is for data, not backup - It exports data values, not the database structure. Don’t use this for database backup.

  2. Any SELECT query works - You can filter, join, sort, and transform data during export.

  3. File path matters - Make sure the H2 process has write permissions to the target directory.

  4. CSV is universal - The output works with spreadsheets, BI tools, and data pipelines.

Summary

Use H2’s CSVWRITE function to export query results to CSV. It’s simple, flexible, and works with any SELECT statement. Just remember it exports data only, not schema metadata, so it’s useful for reporting and data sharing, not for backup and recovery.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Comments

Tags: