title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/datastore/io.md)

DataStore supports reading from and writing to various file formats and data sources.

## Reading Data[​](#reading "Direct link to Reading Data")

### CSV Files[​](#read-csv "Direct link to CSV Files")

```
read_csv(filepath_or_buffer, sep=',', header='infer', names=None, 
         usecols=None, dtype=None, nrows=None, skiprows=None,
         compression=None, encoding=None, **kwargs)
```

**Examples:**

```
from chdb import datastore as pd

# Basic CSV read
ds = pd.read_csv("data.csv")

# With options
ds = pd.read_csv(
    "data.csv",
    sep=";",                    # Custom delimiter
    header=0,                   # Header row index
    names=['a', 'b', 'c'],      # Custom column names
    usecols=['a', 'b'],         # Only read specific columns
    dtype={'a': 'Int64'},       # Specify dtypes
    nrows=1000,                 # Read only first 1000 rows
    skiprows=1,                 # Skip first row
    compression='gzip',         # Compressed file
    encoding='utf-8'            # Encoding
)

# From URL
ds = pd.read_csv("https://example.com/data.csv")
```

### Parquet Files[​](#read-parquet "Direct link to Parquet Files")

Recommended for large datasets - columnar format with better compression.

```
read_parquet(path, columns=None, **kwargs)
```

**Examples:**

```
# Basic Parquet read
ds = pd.read_parquet("data.parquet")

# Read specific columns only (efficient - only reads needed data)
ds = pd.read_parquet("data.parquet", columns=['col1', 'col2', 'col3'])

# From S3
ds = pd.read_parquet("s3://bucket/data.parquet")
```

### JSON Files[​](#read-json "Direct link to JSON Files")

```
read_json(path_or_buf, orient=None, lines=False, **kwargs)
```

**Examples:**

```
# Standard JSON
ds = pd.read_json("data.json")

# JSON Lines (newline-delimited)
ds = pd.read_json("data.jsonl", lines=True)

# JSON with specific orientation
ds = pd.read_json("data.json", orient='records')
```

### Excel Files[​](#read-excel "Direct link to Excel Files")

```
read_excel(io, sheet_name=0, header=0, names=None, **kwargs)
```

**Examples:**

```
# Read first sheet
ds = pd.read_excel("data.xlsx")

# Read specific sheet
ds = pd.read_excel("data.xlsx", sheet_name="Sheet1")
ds = pd.read_excel("data.xlsx", sheet_name=2)  # Third sheet

# Read multiple sheets (returns dict)
sheets = pd.read_excel("data.xlsx", sheet_name=['Sheet1', 'Sheet2'])
```

### SQL Databases[​](#read-sql "Direct link to SQL Databases")

```
read_sql(sql, con, **kwargs)
```

**Examples:**

```
# Read from SQL query
ds = pd.read_sql("SELECT * FROM users", connection)
ds = pd.read_sql("SELECT * FROM orders WHERE date > '2024-01-01'", connection)
```

### Other Formats[​](#read-other "Direct link to Other Formats")

```
# Feather (Arrow)
ds = pd.read_feather("data.feather")

# ORC
ds = pd.read_orc("data.orc")

# Pickle
ds = pd.read_pickle("data.pkl")

# Fixed-width formatted
ds = pd.read_fwf("data.txt", widths=[10, 20, 15])

# HTML tables
ds = pd.read_html("https://example.com/table.html")[0]
```

---

## Writing Data[​](#writing "Direct link to Writing Data")

### to\_csv[​](#to-csv "Direct link to to_csv")

Export to CSV format.

```
to_csv(path_or_buf=None, sep=',', na_rep='', header=True, 
       index=True, mode='w', compression=None, **kwargs)
```

**Examples:**

```
ds = pd.read_parquet("data.parquet")

# Basic export
ds.to_csv("output.csv")

# With options
ds.to_csv(
    "output.csv",
    sep=";",                    # Custom delimiter
    index=False,                # Don't include index
    header=True,                # Include header
    na_rep='NULL',              # Represent NaN as 'NULL'
    compression='gzip'          # Compress output
)

# To string
csv_string = ds.to_csv()
```

### to\_parquet[​](#to-parquet "Direct link to to_parquet")

Export to Parquet format (recommended for large data).

```
to_parquet(path, engine='pyarrow', compression='snappy', **kwargs)
```

**Examples:**

```
# Basic export
ds.to_parquet("output.parquet")

# With compression options
ds.to_parquet("output.parquet", compression='gzip')
ds.to_parquet("output.parquet", compression='zstd')

# Partitioned output
ds.to_parquet(
    "output/",
    partition_cols=['year', 'month']
)
```

### to\_json[​](#to-json "Direct link to to_json")

Export to JSON format.

```
to_json(path_or_buf=None, orient='records', lines=False, **kwargs)
```

**Examples:**

```
# Standard JSON (array of records)
ds.to_json("output.json", orient='records')

# JSON Lines (one JSON object per line)
ds.to_json("output.jsonl", lines=True)

# Different orientations
ds.to_json("output.json", orient='split')    # {columns, data, index}
ds.to_json("output.json", orient='records')  # [{col: val}, ...]
ds.to_json("output.json", orient='columns')  # {col: {idx: val}}

# To string
json_string = ds.to_json()
```

### to\_excel[​](#to-excel "Direct link to to_excel")

Export to Excel format.

```
to_excel(excel_writer, sheet_name='Sheet1', index=True, **kwargs)
```

**Examples:**

```
# Single sheet
ds.to_excel("output.xlsx")
ds.to_excel("output.xlsx", sheet_name="Data", index=False)

# Multiple sheets
with pd.ExcelWriter("output.xlsx") as writer:
    ds1.to_excel(writer, sheet_name="Sales")
    ds2.to_excel(writer, sheet_name="Inventory")
```

### to\_sql[​](#to-sql-method "Direct link to to_sql")

Export to SQL database or generate SQL string.

```
to_sql(name=None, con=None, schema=None, if_exists='fail', **kwargs)
```

**Examples:**

```
# Generate SQL query (no execution)
sql = ds.to_sql()
print(sql)
# SELECT ...
# FROM ...
# WHERE ...

# Write to database
ds.to_sql("table_name", connection, if_exists='replace')
```

### Other Export Methods[​](#to-other "Direct link to Other Export Methods")

```
# To pandas DataFrame
df = ds.to_df()
df = ds.to_pandas()

# To Arrow Table
table = ds.to_arrow()

# To NumPy array
arr = ds.to_numpy()

# To dictionary
d = ds.to_dict()
d = ds.to_dict(orient='records')  # List of dicts
d = ds.to_dict(orient='list')     # Dict of lists

# To records (list of tuples)
records = ds.to_records()

# To string
s = ds.to_string()
s = ds.to_string(max_rows=100)

# To Markdown
md = ds.to_markdown()

# To HTML
html = ds.to_html()

# To LaTeX
latex = ds.to_latex()

# To clipboard
ds.to_clipboard()

# To pickle
ds.to_pickle("output.pkl")

# To feather
ds.to_feather("output.feather")
```

---

## File Format Comparison[​](#format-comparison "Direct link to File Format Comparison")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Format Read Speed Write Speed File Size Schema Best For|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Parquet** Fast Fast Small Yes Large datasets, analytics|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **CSV** Medium Fast Large No Compatibility, simple data|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **JSON** Slow Medium Large Partial APIs, nested data|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Excel** Slow Slow Medium Partial Sharing with non-tech users|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **Feather** Very Fast Very Fast Medium Yes Inter-process, pandas | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Recommendations[​](#recommendations "Direct link to Recommendations")