Turn a Large Data Set Into a Useful Public Table

If your source contains millions of rows, start with the question readers need to answer—not with an upload. Publish a checked summary or a clearly defined extract, while retaining the original file for verification.

This workflow turns daily station-level trip counts into a smaller table of monthly station totals. The result includes the number of daily records behind each total, so readers can assess coverage as well as trip counts.

When is a data set “large”?

For this task, treat a data set as large when its size makes your normal inspection, calculation or publishing workflow unreliable. Row count matters, but so do column count, long text fields and the work needed to process them.

A hard limit is different from slow performance. An Excel .xlsx worksheet supports 1,048,576 rows, including any header row; older .xls sheets support 65,536. Microsoft warns that opening a file beyond the grid limits leaves some data unloaded, and saving over the original can lose those records. Keep the source unchanged. For Excel for PC, Microsoft documents a Get Data–to–PivotTable workflow for summarizing data beyond the worksheet grid. (Microsoft guidance)

Before preparing the public version, choose its scope:

• Full table: readers need individual records, and the destination can retain them all.
• Filtered extract: readers need a defined period, region or category. State the selection rule.
• Summary: readers need comparisons or trends. State how the groups and totals were calculated.

Do not publish the first rows that happen to fit and describe them as the complete dataset.

1. Define the input before importing it

The following is an illustrative dataset, not observed transport data. Save it as daily_trips.csv in a working folder:

service_date,station_id,trips
2026-01-01,0012,120
2026-01-02,0012,130
2026-01-01,0074,20
2026-01-02,0074,30

The input contract is:

Field	Meaning	Required rule
service_date	Date of the daily observation	YYYY-MM-DD; not missing
station_id	Public station identifier	Text; not missing
trips	Trips counted that day	Nonnegative integer written as digits; not missing

One row represents one station on one date. That pair must be unique. A missing observation is not automatically a zero-trip day.

For a real source, record its URL, retrieval date, reuse terms and coverage before processing it. Remove sensitive information locally; do not upload it as part of this public-sharing workflow.

2. Import and validate locally

Install the DuckDB command-line client. Open a terminal in the working folder and start a new persistent database with duckdb publication.duckdb if the executable is on your PATH. Enter the SQL blocks below in that same session.

Leave disk space for the database, export and temporary files. DuckDB can process larger-than-memory workloads by spilling to disk, but this does not guarantee that every workload will fit your machine.

Specify the header, delimiter and types rather than letting identifiers become numbers. Keep trips as text initially so its digit-only rule can be checked before conversion. DuckDB supports these explicit CSV import options:

CREATE TABLE daily_raw AS
SELECT *
FROM read_csv(
    'daily_trips.csv',
    header = true,
    delim = ',',
    dateformat = '%Y-%m-%d',
    columns = {
        'service_date': 'DATE',
        'station_id': 'VARCHAR',
        'trips': 'VARCHAR'
    }
);

Check missing values, invalid counts and duplicate station-date pairs:

SELECT COUNT(*) AS invalid_rows
FROM daily_raw
WHERE service_date IS NULL
   OR NOT isfinite(service_date)
   OR station_id IS NULL
   OR trim(station_id) = ''
   OR trips IS NULL
   OR NOT regexp_full_match(trips, '[0-9]+')
   OR TRY_CAST(trips AS BIGINT) IS NULL;

SELECT service_date, station_id, COUNT(*) AS copies
FROM daily_raw
GROUP BY service_date, station_id
HAVING COUNT(*) > 1;

Here, regexp_full_match checks the entire value, and TRY_CAST returns null when conversion fails. The sample should have zero invalid rows and no duplicate groups.

Resolve any errors or duplicates before continuing. Do not enable error-skipping merely to make the import finish. Then convert the checked counts and record the source totals:

CREATE TABLE daily AS
SELECT service_date, station_id, CAST(trips AS BIGINT) AS trips
FROM daily_raw;

SELECT
    COUNT(*) AS source_rows,
    MIN(service_date) AS first_date,
    MAX(service_date) AS last_date,
    SUM(trips) AS source_trips
FROM daily;

For the sample, expect 4 source rows, dates January 1–2, 2026, and 300 trips. DuckDB’s aggregate documentation distinguishes row counts from non-null value counts and defines SUM over non-null values—so check missing values before trusting a total.

For the full file, reconcile the imported count with the source’s documented record count or a separately verified export count. Check that its dates match the documented coverage.

3. Build and reconcile the public summary

Group the checked records by station and calendar month:

CREATE TABLE monthly AS
SELECT
    CAST(date_trunc('month', service_date) AS DATE) AS month,
    station_id,
    SUM(trips) AS trips,
    COUNT(*) AS daily_rows
FROM daily
GROUP BY 1, 2;

SELECT
    COUNT(*) AS published_rows,
    SUM(daily_rows) AS represented_source_rows,
    SUM(trips) AS published_trips
FROM monthly;

Here, date_trunc selects the start of the month; the cast exports it as a date rather than a timestamp.

For this unfiltered summary, represented_source_rows must equal source_rows, and published_trips must equal source_trips. The sample should produce 2 published rows, representing 4 source rows and 300 trips:

month	station_id	trips	daily_rows
2026-01-01	0012	250	2
2026-01-01	0074	50	2

These are sums of available daily records, not complete January totals. For real data, compare coverage against the expected reporting dates for each station. daily_rows exposes coverage but does not prove completeness.

Export to a separate file. DuckDB’s COPY ... TO supports CSV output with a header and can overwrite an existing destination, so choose the filename deliberately:

COPY (
    SELECT * FROM monthly
    ORDER BY month, station_id
) TO 'monthly_station_trips.csv' (FORMAT CSV, HEADER);

4. Check the published result before sharing

TablePage accepts CSV, TSV, XLSX and XLS files and turns a dataset into a public, shareable page with a filterable table. Upload only the reviewed public output—not the working database or an unchecked source file.

After processing, verify:

• The retained row count matches the exported summary count.
• Station identifiers still read 0012 and 0074, not 12 and 74.
• Dates, totals and a known station filter produce the expected values.
• The accompanying description explains the row unit, source, coverage, aggregation and missing-data rules.

If using the API, inspect the upload response’s truncated and malformed fields and reconcile the metadata row_count; an accepted upload alone is not proof of complete ingestion. These fields are documented in TablePage’s API schema, checked October 29, 2026. If a response flags truncation or malformed records, or counts or identifiers differ, resolve the discrepancy before sharing.

Keep the query and validation counts alongside each release. For help wording the context readers need, use the guide to data table captions and summaries. Share the link only after the public table matches the reviewed output.