Most businesses discover they need a database the same way — a spreadsheet that was "temporary" two years ago is now the company's primary data source, it crashes when filtered, and nobody can tell which version is correct. Excel is excellent for analysis, but it was never designed to store and protect business data at scale. Microsoft SQL Server is the natural destination for Excel users: it's built on the same ecosystem, integrates directly with the tools you already use, and SSMS — the management interface — has a built-in wizard that can import your Excel file directly without converting it to CSV first.
Excel vs SQL Server — key differences
| Capability | Excel | SQL Server |
|---|---|---|
| Maximum rows | ~1,000,000 (hard limit) | Billions — no practical limit |
| Performance at scale | Slows significantly above ~100k rows | Consistent speed regardless of row count |
| Concurrent users | One editor at a time (file locked) | Hundreds of simultaneous readers and writers |
| Data type enforcement | Flexible — any value in any cell | Strict — wrong type rejected at insert |
| Accidental data loss | One keystroke can delete a column | Changes require explicit SQL — hard to do by accident |
| "One version of the truth" | Files float around in email versions | Single central database everyone connects to |
| Row-level security | If they have the file, they see everything | Grant access per user, per table, per row |
| Power BI integration | Static Excel connection — manual refresh | Live connection — dashboards update in real time |
| Automation | Macros — manual trigger required | SQL Agent jobs, triggers, scheduled pipelines |
If your spreadsheet has fewer than 50,000 rows, opens instantly, and is edited by only one person, Excel is probably still the right tool. The migration pays off when multi-user access, row count, or data integrity becomes a problem — and all three tend to arrive at the same time.
Before You Import: Cleaning the Spreadsheet
SQL Server's import wizard is tolerant in some ways — it reads .xlsx files directly — but strict in others. A few Excel conventions that are invisible to humans cause hard failures in the database. Worth addressing before the import, not after.
Remove merged cells
Merged cells are a visual trick — the underlying data exists only in the top-left cell; the others are empty. When SQL Server reads the sheet row by row, it sees those empty cells as missing column values and either fails or imports nulls into the wrong columns. Un-merge all cells: Home → Find & Select → Go To Special → select Blanks to locate them.
Simplify column headers
The first row becomes your column names. SQL Server allows spaces in column names if they're double-quoted in every query, but this creates friction permanently. Replace spaces with underscores (Customer Name → customer_name), remove special characters (#, !, %, $, parentheses), and keep names short. The wizard uses header values verbatim — whatever is in row 1 becomes the column name in the database.
Delete "ghost" rows
Excel sometimes treats cells below the visible data as part of the table if a formula or space character was ever typed there. These ghost rows import as rows of NULLs and inflate your row count. Highlight everything below your last real data row, right-click → Delete (not just press the Delete key — that clears values but leaves the rows).
Replace text placeholders with blanks
"N/A", "TBD", "None", "--" — any text in a column that the wizard interprets as numeric or date will cause a type error during import. Use Find and Replace to clear them to empty cells. An empty cell imports as NULL, which is the correct database representation of a missing value.
Ensure each sheet is one flat table
The wizard imports one sheet at a time, treating it as a single rectangular grid. If a sheet has a summary table below the main table, or two side-by-side datasets, split them into separate sheets first. Each dataset should become its own database table.
The SSMS Import Wizard — No CSV Required
Unlike PostgreSQL, which requires you to convert your file to CSV first, SQL Server Management Studio includes an Import Data wizard that reads .xlsx Excel files directly. If your file is already clean, this is the fastest path to getting your data into the database — typically under five minutes for files up to a few hundred thousand rows.
What you'll need before starting
- SQL Server Management Studio (SSMS) — free download from Microsoft
- A connection to your SQL Server instance
- The cleaned Excel file (
.xlsxor.xls) - The Microsoft Access Database Engine (required for Excel reading — free from Microsoft if not already installed)
Open the Import Wizard
In SSMS's Object Explorer, expand your server → Databases → right-click the database you want to import into → Tasks → Import Data. The SQL Server Import and Export Wizard opens.
Choose Microsoft Excel as the data source
In the "Data Source" dropdown, select Microsoft Excel. Click Browse to locate your .xlsx file. Make sure "First row has column names" is checked — this tells the wizard to treat row 1 as headers, not data.
Set SQL Server as the destination
In the "Destination" dropdown, select SQL Server Native Client (for older versions) or Microsoft OLE DB Driver for SQL Server (for newer). Your server and database should already be filled in from the connection you used to open SSMS.
Choose which sheets to import
The wizard lists each sheet in the Excel file as a table to import. Check the sheet(s) that contain your data. By default, the wizard creates a new table in the destination database with the same name as the sheet (e.g., "Sheet1quot;). You can click Edit Mappings to rename the destination table and change how columns are mapped.
Review column mappings
The Edit Mappings screen shows each source column from Excel matched to a destination column in SQL Server. The wizard guesses each column's data type from the first few rows of the spreadsheet. Review these carefully — if a column should be DATE but the wizard guessed NVARCHAR, change it now. If you're unsure of the correct type, leave it as NVARCHAR(255) for the first import and alter the column type after verifying the data.
Run and verify
Click Next → Finish. The wizard shows a progress screen with row counts per table. When complete, open a new query in SSMS, run SELECT TOP 10 * FROM your_table, and confirm the first ten rows look correct. Check the row count against the original spreadsheet.
Common Wizard Failures and Fixes
The wizard fails more often than the SSMS interface suggests it might. Most failures are caused by the spreadsheet preparation issues above — but a few are wizard-specific quirks worth knowing.
Fix: The wizard samples only the first 8 rows to infer column types. If all first-8 rows in a column are numeric but row 9 contains "N/A", the column is declared as FLOAT and row 9 fails. Re-run with all columns mapped to NVARCHAR(MAX) — a "load everything as text" import — then use ALTER TABLE and UPDATE statements to cast columns to correct types after the data is loaded. This is the same staging table approach used in the SQL Server CSV guide.
Fix: The wizard needs the Microsoft Access Database Engine to read Excel files. Download the "Microsoft Access Database Engine 2016 Redistributable" from Microsoft's download center. Install the 64-bit version if you're running 64-bit SSMS, and the 32-bit version if you're running 32-bit SSMS — they must match.
Fix: Ghost rows below your data were imported as NULL rows. Return to the spreadsheet, select all rows below the last real data row, right-click and choose Delete (not just clear), then re-export and re-import.
Fix: Excel stores dates internally as integers counting from January 1, 1900. If the wizard detects the column as a number rather than a date, it imports the raw integer. In the Column Mappings screen, explicitly change the destination type from FLOAT to DATE for that column — or format the Excel column as Short Date before importing.
For imports that fail due to data quality issues across many rows — duplicate primary keys, type mismatches at scale — the BULK INSERT troubleshooting guide covers the diagnostic patterns that apply equally to wizard-driven imports.
After the Import: What Opens Up
Once data is in SQL Server, several capabilities become available immediately that Excel couldn't provide. These are worth setting up right after the first successful import.
Power BI connects to SQL Server via DirectQuery or Import mode. DirectQuery means every chart and filter in Power BI runs a live SQL query — your dashboards reflect the current state of the database the moment anyone views them. No manual refresh, no stale screenshots emailed around.
If your team prefers working in Excel, you can connect Excel back to SQL Server using Data → Get Data → From Database → From SQL Server Database. Excel pulls the latest data from the database on demand — your team gets the familiar interface, and the database gets the data integrity. No more copying and pasting between files.
SQL Server lets you grant access per user, per table, or even per row using views and permissions. A manager can see all sales regions; a regional rep sees only their territory. This is impossible to replicate reliably in Excel.
If you receive updated Excel or CSV exports regularly — weekly sales reports, daily inventory feeds — the first manual import can become a repeatable pipeline using BULK INSERT or a scheduled SQL Server Agent job. For large recurring files, the bulk loading guide covers the high-performance path.
When the wizard isn't enough
The SSMS Import Wizard works well for one-time migrations of files up to a few hundred thousand rows. For files with millions of rows, recurring imports, or data that needs transformation before landing in the database, the wizard's 8-row type sampling and lack of batching make it the wrong tool. BULK INSERT with TABLOCK and BATCHSIZE is the correct path for high-volume work.
See also
Automate the spreadsheet → database pipeline
Elvity handles type inference, encoding, schema creation, and recurring import scheduling — so the one-time wizard migration becomes a reliable, automated pipeline.