How to export database records to Excel
In Base by Zaweba, Database > Export to .xlsx writes a real Excel file with a sheet per table or query, real dates, money in pounds and filters.
Choose Database > Export to .xlsx in Base by Zaweba. You pick the tables and queries you want, and Base writes a real Excel workbook with one sheet for each. Dates arrive as proper Excel dates and money is formatted as pounds, and each sheet gets a frozen header row and filters, so it is ready to use as soon as it opens.
There are simpler exports too, for CSV and JSON, covered further down.
Choosing what goes in
The best thing to export is usually a query rather than a whole table. A table gives you every field, including record ids and lookups. A query lets you choose the columns, put them in a sensible order, sort them, and filter out what the recipient doesn't need. Build the query in the visual query builder, save it, and include it in the export.
Since each table or query becomes its own sheet, one workbook can carry several related lists. A month-end pack might have a sheet of invoices, a sheet of payments and a sheet of totals by customer from a grouped query.
Why real dates and money matter
A lot of exports from databases arrive in Excel as text that only looks like dates and numbers. Sorting goes wrong, sums come out as zero and people have to convert columns by hand. Base writes dates as Excel dates and money as currency, so sorting, filtering and formulas work straight away.
The frozen header row keeps column names in view as you scroll, and the filters on each header let whoever receives the file narrow it down without setting anything up.
Exporting a query that asks a question
If the query is a parameter query, one with a prompt in square brackets such as [Which town?], Base asks for the value each time it runs, and CSV export asks too. That makes a single saved query reusable: export the Leeds members today and the York members tomorrow without editing anything.
CSV and JSON
Any table or query can also be exported as CSV. That is the plainest format there is, and almost every program can read it, from mailing tools to accounts packages. Use it when the file is going into another system rather than to a person.
JSON export writes the records as an array, which suits developers and web projects that want to read the data in code.
Going the other way
Base imports as well as exports. CSV files and Excel .xlsx and .xls files can be imported into an existing table, with columns matched by name or caption and lookups matched by their text or id. Bad rows are skipped and listed rather than stopping the whole import. Or you can import as a new table and let Base guess the field types.
That makes Excel a handy halfway house. Send a sheet out, let someone fill in missing details, and bring it back.
Tips for a clean export
Give fields captions in the table designer if their names are cryptic; captions are what people see on labels. Remove columns from the query that only make sense inside the database. If the same export is needed every week, save the query with a clear name and put a button for it on a form, since a button can export CSV with one click.
Spreadsheet work after that is up to you. Grid by Zaweba is the Zaweba spreadsheet, and the .xlsx files from Base by Zaweba are ordinary Excel workbooks.
