Merge Multiple Excel Files Into One Table
Point Query Streams at the folder of workbooks and every worksheet becomes a SQL table — the same tab from every file, unioned, typed and read-only. No consolidation macro, no master workbook to rebuild each month.
Query Streams is a secure, real-time database integration platform that turns a folder of Excel workbooks into live SQL tables you can query from Microsoft Excel, Google Sheets, Airtable and your AI assistant. Learn more at QueryStreams.com and sign up for free to combine your first folder of workbooks in minutes.
Somewhere in almost every organisation there is a folder like this one. A workbook per month, or per branch, or per quarter, each with the same three or four tabs, each produced by someone who had no idea it would one day need to be added up. Once a year somebody is asked to “consolidate the workbooks”, and the honest answer is that it takes a day and has to be done again next year.
The reason it is hard is not the arithmetic. It is that the interesting data is not in the folder, it is across the folder, spread over forty-eight files and one hundred and forty tabs. An Excel Folder connector flips that around: it presents the folder as a set of SQL tables, one per worksheet name, so the same tab from every workbook is already unioned before you write a single line of SQL.
Why the usual ways to combine Excel files run out of road
There are three well-trodden routes, and each of them fails in the same place: they produce a merged thing — a master workbook, a query load, a pivot cache — that is correct on the day it runs and wrong the day after.
| Approach | Handles many tabs | What it costs you |
|---|---|---|
| Copying sheets into a master workbook | By hand | Redone every period, and the master file grows until it is slow to open. |
| Power Query “From Folder” | One at a time | Combines one selected sheet per query, lives inside the workbook that owns it, and loads every row into the file. Three tabs means three queries, maintained separately. |
| A VBA loop or an office script | If written to | Somebody owns and debugs it, it opens every workbook to read it, and a file left open by a colleague stops the run. |
| An online “merge XLSX” service | No | You upload the company’s financials to a stranger’s server. For anything commercial that is the end of the conversation. |
| Query Streams Excel Folder | All of them | Nothing is merged, opened in Excel, copied or uploaded — every worksheet becomes a table where it sits. |
The shared machinery underneath — folder scanning, the file ledger, the encrypted local cache, the files and files_events inventory tables, the caps and the read-only guarantee — is the same for every folder type and is explained once in how to query a folder of files with SQL. What follows is only the part that is specific to workbooks, and workbooks are specific in more ways than you would expect.
One table per worksheet, not one table per folder
This is the headline difference from a CSV folder, where every file unions into a single table. A workbook is not one thing, it is a small database: workbook equals database, sheet equals table. So an Excel Folder connector pins one data table per worksheet name and fills it with the rows of that sheet from every workbook in the folder.
There is no table name to fill in on an Excel Folder connector, because the worksheets decide it for you. Each pinned table is named data_ followed by the sanitised sheet name, so a folder of workbooks whose tabs are Orders, Returns and Summary gives you data_orders, data_returns and data_summary. Each is a proper pinned table with real column types, not a bag of text.
SELECT strftime(order_date, '%Y-%m') AS month,
region,
COUNT(*) AS orders,
SUM(line_total) AS revenue
FROM data_orders
GROUP BY month, region
ORDER BY month, revenue DESC;
Because every row carries the _source_file column described in the hub guide, the worksheets of a single workbook can be put back together again. Joining a detail sheet to its own header sheet on _source_file reunites the two tabs of the same file, which is the SQL equivalent of the cross-sheet formula you would otherwise write forty-eight times.
SELECT s.report_month,
s.prepared_by,
SUM(o.line_total) AS detail_total
FROM data_orders AS o
JOIN data_summary AS s
ON s._source_file = o._source_file
GROUP BY s.report_month, s.prepared_by
ORDER BY s.report_month;
Two sheet names that sanitise to the same table name — “Q1 Orders” and “Q1-Orders”, say — are a named refusal rather than a silent overwrite, and the message tells you which two sheets collided. Rename one in the workbooks, or list only the sheets you want.
A workbook missing one pinned sheet is set aside entirely
This is the behaviour that surprises people, so it is worth explaining properly rather than filing under “quirks”.
Once the connector has pinned tables for Orders, Returns and Summary, every workbook is expected to carry all three. A workbook that has Orders and Summary but no Returns tab is parked whole — not partially ingested with its Returns rows quietly absent. Its Orders rows stay out of data_orders too, and the reason is recorded against the file.
The alternative sounds friendlier and is far more dangerous. Ingesting the two tabs that are present would give you a data_returns table that is missing one month entirely, with nothing in the numbers to say so. Every total computed from it would be wrong by exactly the amount nobody can see. A missing sheet is the textbook silent-partial-data failure, and the connector treats it as a data error rather than a formatting variation.
The rule runs in the other direction as well. A workbook that has gained a tab the pin has never seen is also parked, because a new sheet is new data and the connector will not decide on your behalf whether it belongs. The same goes for a sheet whose columns have moved: an added column, a dropped column, or a type that has flipped from text to number all park the workbook and name the sheet and column that disagreed.
sheets option to an explicit include-list, such as Orders and Returns, tells the connector that everything else is deliberately out of scope — so a colleague’s “Notes” or “Pivot1” tab no longer counts as an unexpected sheet. It is the single most useful setting on this driver.
An include-list is checked as strictly as everything else: a sheet named in the list but found in none of the sampled workbooks is a named refusal listing the sheets that were found, because a typo in an include-list would otherwise look exactly like a working configuration.
The refusal that saves you from a folder of empty tables
Follow the whole-workbook rule to its conclusion and there is an unpleasant corner. If the workbooks in a folder legitimately have different sheet sets — one has Orders and Summary, another has Shipments and Summary — then the pinned set is the union of all of them, no single workbook carries the whole union, and every file parks. The connector would pin five tables, ingest nothing, and report a technically honest success.
So that outcome is detected during the first sync and refused by name before any of it happens. The message lists the sheets it was about to pin, shows you the differing sheet sets it actually saw with example filenames, and points at the two real fixes.
- Narrow the sheets option to the tabs every workbook genuinely shares — usually the right answer when one common sheet is what you were after.
- Switch the table layout to one table per layout, which stops using the sheet name as the grouping key altogether. That is the next section.
A refusal with two named remedies is a better outcome than five empty tables and a green tick, and it is the reason this connector is worth pointing at a folder you are not sure about.
One table per layout, for folders assembled by humans
Excel is human territory. Workbooks in a real folder rarely share one layout, because they were made by people rather than by a nightly export. The optional one table per layout mode is built for exactly that.
In this mode the unit is not the sheet name, it is the individual sheet inside an individual workbook, and sheets are grouped by their exact shape: the ordered list of column names and types. Each distinct shape becomes its own table. The consequence is liberating and slightly odd at first: a tab called Sheet1 in one workbook joins a tab called Data in another whenever their columns and types match exactly, while two tabs that share a name but not a shape end up in separate tables.
| Behaviour | Default layout (pinned) | One table per layout |
|---|---|---|
| Grouping key | The worksheet name | The exact column name and type sequence |
| Sheet sets may differ between workbooks | No — refused up front | Yes, that is the point |
| Near-miss layouts | Parked as drift | Become a separate table — there is no nullable merging of “almost the same” |
| Table names | data_sheetname | Derived from the filenames that fed the group |
| Ceiling | One table per sheet found | 25 distinct layouts, then a named refusal |
Names in this mode come from the files rather than the tabs. The connector takes the leading part that the group’s filenames have in common and trims trailing numeric fragments, so orders_2026_01.xlsx through orders_2026_12.xlsx produce a table called orders — and, importantly, a lone shipments_week_01.xlsx produces shipments_week rather than shipments_week_01. That matters because a pinned table is never renamed once your saved queries reference it, so the name a single file produces has to be the same name its future siblings will produce. If nothing usable remains, the sheet name is used instead; if two groups still want the same name, one gets a short hash suffix.
Changes here are additive and never retrospective. A sheet whose layout the pin has never seen parks its workbook rather than being forced into the nearest table; changing a connector option re-samples the folder and admits the new layout as a new table, and every layout the previous pin had already named keeps its name exactly.
Every number in a spreadsheet is a floating point number
Under the covers, an .xlsx file stores every numeric cell as a double. There is no integer type on the sheet, which means an order number, a quantity and a percentage all arrive looking identical to a reader. Left alone, that gives you a column of DOUBLE where you expected whole numbers, and identifiers rendered as 1002.0.
Query Streams probes the sampled values instead of trusting the declared type. Where every value in a numeric column is whole, finite and within range, the column is promoted and pinned as BIGINT — so identifiers, counts and quantities behave like integers in your SQL and in the spreadsheet they land in.
The promotion is then defended file by file. Every workbook is re-checked before its rows are written, and a fractional, infinite or out-of-range value in a promoted column parks that workbook naming the sheet and the column. This is deliberate: converting a double to an integer rounds silently, so the friendly behaviour would turn 0.5 units into either zero or one and never tell you which. A named park is the honest alternative.
Types must also agree across the folder. If invoice_no reads as a number in most workbooks and as text in one, the first sync refuses and quotes both files and both types, rather than taking a majority vote and dropping the odd one out. Columns that are simply absent from some workbooks are a gentler case: they are pinned as nullable and read as empty from the sheets that lack them.
Where the connector thinks your table starts and stops
A worksheet is not a file format, it is a canvas, and people put things on it. Two rules govern how a sheet is turned into rows, and both are easy to work with once you know them.
Reading begins at the first row that actually contains cells, and that row is treated as the header, supplying the column names. Blank rows above the data are skipped, so a table that starts at A4 because someone left space at the top is read correctly. A title or a merged banner in A1 is not blank, though, so it would be read as the header instead — which shows up immediately as absurd column names rather than as a subtle wrong answer.
Reading stops at the first completely empty row. A blank spacer row before a totals block is therefore a natural boundary: the data above it is the table, and the totals row below is excluded, which is usually what you want since a totals row would otherwise double your numbers. It also means a blank row left in the middle of a long list truncates that sheet silently, so if a per-file row count looks short, an empty row is the first thing to check.
SELECT _source_file,
COUNT(*) AS rows,
MIN(order_date) AS first_date,
MAX(order_date) AS last_date
FROM data_orders
GROUP BY _source_file
ORDER BY _source_file;
That single query is the completeness check for a folder of workbooks. Because a file is always all-in or all-out, a workbook that is missing from the results has been parked, and the reason is waiting in files_events. A workbook whose count is much lower than its neighbours’ has usually met an empty row.
The reader is staged with the agent, not downloaded
Reading .xlsx properly is a real piece of engineering, and Query Streams uses the official signed Excel reader for its query engine rather than a home-grown parser, which is where the genuine type inference comes from. That binary is around 46 MB and is locked to the engine version it was built for.
It is therefore staged alongside the Network Agent when the agent is installed or updated, and never fetched at query time. Agents run on machines that may sit behind a proxy, on an isolated network segment, or simply offline for a while, and a connector that silently needed an internet download the first time somebody queried it would fail in exactly those places. If the file is genuinely absent the connector says so by name and tells you to run the installer again — it does not fall back to a lesser parser and give you a different answer.
One consequence worth knowing: listing the tabs in a workbook is done by the agent itself, by reading the workbook’s own index — an .xlsx is a zip archive, and the sheet list lives inside it. A file that is not a valid Open XML workbook cannot be indexed, so a legacy binary .xls renamed to .xlsx, a truncated upload or a half-written file parks with “not readable as an xlsx workbook” rather than producing a confusing parse error. Genuine .xls files need resaving as .xlsx first.
| What happened | What the connector does |
|---|---|
| Workbooks disagree on a column’s type | Refuses the first sync, quoting both files and both types |
| No workbook carries every pinned sheet | Refuses, showing the sheet sets seen and the two fixes |
| A workbook is missing a pinned sheet | Parks that workbook whole; the rest keep syncing |
| A workbook has gained a sheet | Parks it, unless the sheets include-list puts it out of scope |
| A column is added, dropped or retyped in one sheet | Parks that workbook, naming the sheet and column |
| A fractional value in a promoted whole-number column | Parks that workbook, naming the sheet and column |
| A file is not a readable .xlsx | Parks it by name; at first sync it is skipped and reported |
| An unrecognised driver option | Refused outright — a setting doing nothing is worse than an error |
Note the difference between the first two rows and the rest. Disagreements about the shape of the folder are refusals, because there is no safe table to pin. Problems with an individual workbook are parks, because the other forty-seven files have done nothing wrong and should keep working.
Every Excel setting, and when to change it
An Excel Folder connector adds only two settings of its own, Sheets and Table layout, and two shared ones behave distinctively enough on workbooks to be worth covering here. Everything else on the form — root folders, recursion and max depth, symlinks, the filename parse pattern, folder tokens, the scan interval, hashing, the delete policy, event retention, the scan limits and the four ingest caps — is shared by every folder type and is documented once in the File Set connector guide. This section covers what workbooks add on top.
One field you will look for and not find is Table name. CSV, Parquet and JSONL folders have one because everything they hold unions into a single table. An Excel folder does not, because the worksheets already name the tables — data_orders, data_returns and so on, as described above. There is nothing to type, and nothing you can type to change it.
Sheets
Default: empty, which means every sheet in every workbook. This is the setting that does the most work on this driver, and the one worth reaching for first when something is not behaving.
How to set it: one sheet name per line, in the box labelled Sheets, each matching a worksheet tab name exactly as it appears in the workbook. Each name you list becomes its own pinned table; everything you do not list is treated as deliberately out of scope and is never read.
Orders Returns
When to change it. Two situations, and both are common. The first is the workbook that carries tabs nobody wants as a table — a “Notes” sheet, an “Instructions” sheet, a “Pivot1” left behind by whoever built the file, a “Chart data” helper range. The second is the nine-tab workbook where you only care about Orders. In either case, naming the sheets you want is cleaner than trying to describe the ones you do not.
It is also the tidiest fix when a single stray tab is causing a refusal or a run of parked workbooks. Because every workbook is expected to carry every pinned sheet — and a workbook missing one is set aside entirely, as A workbook missing one pinned sheet is set aside entirely explains above — one colleague adding a personal tab to their copy is enough to park that file. Listing the sheets you actually want puts the stray tab out of scope and the workbook back in.
If you get it wrong: a sheet you name that appears in none of the sampled workbooks is a named refusal that lists the sheets it did find, not a silently empty table. A typo cannot pass for a working configuration.
Table layout
Default: one pinned layout, which expects every workbook in scope to share a shape. The alternative is one table per layout, which groups sheets by their exact columns and types instead of by sheet name, up to 25 distinct layouts.
Worth saying plainly: per-layout is more often the right answer on an Excel folder than on any other folder type. A folder of Parquet files was written by a machine and will be uniform. A folder of workbooks was assembled by people over several years, and uniformity is the exception. If the default refuses your folder, that is not a fault to work around — it is the connector telling you this is a per-layout folder.
What happens when you change it: the folder is re-sampled and the tables are rebuilt. That is by design, because the pinned shape is exactly what changed. Table names already in use are carried across the rebuild, so existing saved queries, spreadsheet refreshes and Nova questions keep working. The section One table per layout, for folders assembled by humans above covers how the names are derived and how the two modes differ.
Include and exclude patterns
These are shared settings, but the Excel card seeds one of them for you and there is a lock-file trap worth naming. Include patterns arrives pre-filled with **/*.xlsx, which is usually correct as it stands. Narrow it when the folder holds workbooks that are not part of the same data set — **/month-end-*.xlsx, or exports/**/*.xlsx to skip a sibling archive folder.
**/~$* to Exclude patterns on any shared drive. When somebody has a workbook open, Microsoft Excel writes a hidden lock file beside it named ~$whatever.xlsx. It matches the include pattern, it is not a readable workbook, and it parks — so a colleague with a file open produces a fresh parked file on every scan. Excluding the prefix once is the permanent fix. Your exclude patterns are added to the built-in list rather than replacing it.
One rule the wizard enforces: a mixed folder is two connectors on the same root, one per driver. If the same folder holds workbooks and CSVs, widening the include pattern to catch both does not work, because two formats cannot share a pinned shape. Point an Excel Folder connector and a CSV Folder connector at the same path instead.
Manifest overrides (JSON)
Behind Show advanced options. This is the escape hatch for a pinned shape that is right in structure but wrong in detail: a column you want renamed, a type you want pinned, a column you would rather not have at all. It is applied when the shape is pinned, so it becomes part of the table definition rather than something re-applied on every query.
{
"renames": { "qty": "quantity" },
"retypes": { "price": "DECIMAL(18,4)" },
"exclude": [ "internal_notes" ]
}
- renames — give a column a better name than the header row did. Useful on workbooks, where a header cell may be a sentence.
- retypes — pin a column to a type you choose rather than the one that was sniffed.
- exclude — leave columns out of the table entirely.
For Excel specifically, retypes is the practical answer to the floating point problem described above. A currency column has decimals, so it is never promoted to a whole-number type and stays floating point, where very large sums can drift. Pinning it with "total": "DECIMAL(18,2)" gives you exact arithmetic; pinning an identifier with "invoice_no": "BIGINT" stops it rendering as 1002.0. A retype is treated as a deliberate instruction and applied as the rows are read.
Emptying the box clears your overrides and returns the tables to the shape that was sampled. Only plain type names with optional precision are accepted — DECIMAL(18,2), VARCHAR, BIGINT, DATE — and anything unusual is rejected when you save rather than at sync time.
Setting it up
You need a Query Streams account and the Network Agent installed somewhere that can see the folder — a file server, a workstation with the share mapped, or your own laptop. Excel Folder connectors require Network Agent 2.6 or newer.
- Install the agent from the download page if it is not already running.
- In the portal, add a data connector and choose Excel Folder.
- Enter the folder path as the agent’s machine sees it. Include patterns is already seeded with
**/*.xlsx; narrow it to something like**/month-end-*.xlsxif the folder holds workbooks you do not want combined. - If the workbooks carry working tabs you do not care about, list the ones you do want in Sheets, one per line. Leave it empty to take every sheet.
- Save. The agent samples up to 64 workbooks, pins one table per sheet and reports what it found — including any workbook it could not read.
- Open the query builder, pick the connector and query the worksheet tables — or ask Nova AI to write the SQL for you.
\\finance\month-end, or a local path on the agent’s own machine, and check that the account the agent runs under can read it.
Where the worksheet tables can go
- Microsoft Excel and Google Sheets — run the query from the add-in and the combined rows land in the sheet, refreshed on demand.
- Airtable, Smartsheet, Baserow, SeaTable and Anvil — scheduled syncs push the consolidated data into the tool your team already uses.
- Nova AI — ask a question in plain English and get SQL across every workbook, with a chart.
- Claude, Cursor, ChatGPT and Grok — through the Query Streams MCP server, an AI assistant can query the folder of workbooks directly.
- REST API — turn a consolidated worksheet table into an endpoint for a partner or an internal application.
There is a pleasing symmetry in the first of those. The folder of workbooks becomes a live table that you query from a spreadsheet — so the answer arrives in Microsoft Excel without anyone having to open forty-eight Excel files to produce it.
Frequently Asked Questions
How do I merge multiple Excel files into one table? +
**/*.xlsx. Each worksheet name becomes a SQL table holding that tab’s rows from every workbook in the folder. Nothing is merged into a new file, so workbooks added later are included automatically on the next sync.
Why do I get one table per worksheet instead of one combined table? +
data_orders, data_summary — and the same tab from every workbook is unioned inside it. If you want everything from one tab in one table, that is exactly what you get.
One workbook was set aside because it lacked a sheet. Why not just import the others? +
files_events while the other workbooks keep syncing. Add the sheet back, or narrow the sheets include-list to the tabs that genuinely appear everywhere.
My workbooks have different tabs. Can I still combine them? +
Can I combine only one specific worksheet from every workbook? +
sheets option to that one name. Only it is pinned and read, and every other tab is treated as deliberately out of scope, which also stops scratch tabs and pivot sheets from parking workbooks. A sheet named in the option but present in none of the sampled workbooks is reported as an error rather than ignored, so a typo cannot pass unnoticed.
Why are my ID numbers showing decimals, or why did that stop? +
Will currency totals be exact? +
Does it read .xls files, and does my sheet need to start in cell A1? +
How many workbooks can one connector handle? +
Are my workbooks opened, changed or uploaded? +
SELECT, WITH, PRAGMA, DESCRIBE or EXPLAIN statement, and anything that could write is rejected before it runs.
Can I join workbook data to my database? +
Get Started
Stop consolidating workbooks by hand
Point the agent at the folder once and query every worksheet as a SQL table — from Microsoft Excel, Google Sheets, Airtable or your AI assistant. Next month’s workbook joins on its own, and your files never leave your network.
Related guides: How to query a folder of files with SQL | Combine multiple CSV files into one table | Download the Query Streams Agent | All File Set Connector guides
Category: File Set Connectors
Tags: merge multiple excel files, combine excel files, multiple excel files into one, query excel files with sql, excel folder connector, xlsx folder, consolidate workbooks
Meta Description: Merge multiple Excel files into one live SQL table. One table per worksheet, no uploads, read-only.
English 
Spanish 
German 
French 
Portuguese 
Japanese 
Korean 
Chinese 
Italian 
Dutch 
Indonesian 
Polish