'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Import and Normalize Excel Data into Access or SQL Server
Question details
Convert a large, flat Excel spreadsheet with inconsistent data into a structured, normalized relational database.
- Product
- Excel, Microsoft Access, SQL Server
- Device & OS
- not provided
- Scenario
- The user needs to migrate a long-maintained spreadsheet into a database application to utilize forms, command buttons, year-based filters, and calculated fields.
- Observed behavior
- The data is currently stored as a flat-file list in Excel, which requires splitting into related tables using relational database normalization principles to function efficiently in Access or SQL Server.
Review your existing Excel spreadsheet and identify distinct entities (e.g., Customers, Orders, Products) to plan your relational database tables before beginning the import process.
Normalize Data and Import using Access Append Queries
The most robust method involves importing the flat Excel data temporarily into Access and using append queries to distribute it into properly structured, normalized tables.
Relational databases like Access and SQL Server require normalized data (separate, related tables) rather than a single flat file. Access has a strict limit on the number of columns per table, making it mandatory to split wide, unoptimized Excel sheets.
Open Microsoft Access, navigate to the 'External Data' tab, select 'New Data Source' > 'From File' > 'Excel', and import your raw spreadsheet into a temporary staging table.
Go to the 'Create' tab and use 'Table Design' to build new, separate tables representing your entities, ensuring each has an appropriate primary key.
Click 'Query Design' on the 'Create' tab, add your staging table, and change the query type to 'Append' from the ribbon.
Map the relevant fields from the staging table to your new normalized tables. Run the queries in the correct order, populating parent tables before child tables to maintain referential integrity.
Once your data is successfully normalized, use the 'Create' tab tools to build forms, command buttons, filters, and calculated fields for managing your records.
Clean Data in Excel First and Import Directly to SQL Server
Clean and split the data into separate worksheets within your spreadsheet application before using the SQL Server Import and Export Wizard.
Use WPS Office to Prepare Your Excel Data for Database Import
While building relational databases requires specialized software like SQL Server or Access, preparing and cleaning your flat-file data beforehand is crucial. WPS Office provides a powerful, free, and highly compatible spreadsheet tool to clean, filter, and organize your large datasets before importing them into a database.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx or .csv flat-file data.
- 3. Clean and Organize Data: Use the Data tab to remove duplicates, format columns, and prepare your tables for normalized database import.
Frequently Asked Questions
Why won't my large Excel spreadsheet import into Access directly?
Microsoft Access has a strict limit of 255 columns per table. If your flat-file Excel spreadsheet exceeds this limit or contains highly unstructured data, the direct import will fail. You must clean and normalize the data into smaller, related tables first.
What does it mean to normalize Excel data?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. For Excel data, this means taking a single wide worksheet and splitting it into multiple related tables (e.g., separating customer details from order records) linked by unique identifier keys.
Can I keep my calculated fields when importing Excel to SQL or Access?
No, relational databases like SQL Server and Access are designed to store raw data in tables. Calculated fields from Excel are generally not imported as static columns. Instead, you recreate these calculations using queries, views, or calculated fields in the database application's reporting layer.
What is the best way to move Excel data into normalized Access tables?
The recommended approach is to import your entire Excel sheet into a temporary "staging" table in Access. Once imported, you can build and run a series of Append Queries to distribute specific columns from the staging table into their respective normalized, permanent tables.
