You are here:

Using MS Access/Importing Excel Spreadsheet to Access


Hello Manish

I creating a processing fee database but need to import and excel spreadsheet flat file into access.  The spreadsheet, consisting of well, date, volume type, and volumes, is not normalized.  I am not sure how to append new excel data to my normalized access tables - table analyzer in Access 2010 seems best for a new database, not appended data.  One solution may be to import the spreadsheet and then link the data to my tables.  Is there a way though to modify the excel data to append to my existing tables.



Hi Andrew,

The word "WELL",gives me an idea that you are from the Oil industry. I used to face similar migration situations when I was in the Oil industry too. I used to perform the following:

- Make sure the spreadsheet has the proper data in the proper field[s].e.g:(No text info entered in the date field etc)
- Make sure you have a readable header cause this automatically becomes your field names when this spreadsheet is attached as a table in MS-Access.
- Create a table called tbl_WellData in MS-Access which has the below data types
0.WELL_ID as your Primary key (Autonumber)
1.WELL (TEXT) since its alphanumeric
2.Date (Date/Time) Short date
3.Volume type (Text) since its a measure of unit
4.Volumes (Numeric)with fixed if its Unit of Measure value OR Standard if it has monetary volume
-Save your Well Table Successfully
-Link your Excel Spreadsheet to your MS-Access database where your newly created Well table exists
-Create an Append Query with Source as your "Excel linked table" and your DESTINATION as your Access tbl_WellData table
-Map the correct fields from the Excel Table to your MS-Access tbl_WellData field names
-Click on the datasheet view button on the MS-Access toolbar and check if the data is getting fetched for migration
-If you see the exact number of rows as per what are existing in your Excel linked Spreadsheet which means your doing good, else you need to check up for wrong data values stored in the wrong fields
-If you see exact number of rows then Click on the Exclamatory Icon on your MS-Access toolbar to perform the Append Operation.
-Certain warning will flash to inform you of the number of records which are about to get appended etc.
-Keep clicking OK and you should have your data migrated to Ms Access.

This is the only way to get your data through from Excel to Access in your situation. Also you can have the links and query saved for future use. As data gets appended in your linked spreadsheet, you can have a Date condition parameter in your query to migrate only the required records that fall within a specific date range by cycling through the above mentioned steps.

I hope this will help you.

Best regards
Manish Batola.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Manish Batola


ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.


29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Worked in various multinationals catering from Aerospace, Oil & Gas etc.

-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 All rights reserved.