You are here:

Using MS Access/Importing Excel Spreadsheet to Access

Advertisement


Question
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.

Thanks

Andrew

Answer
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

Volunteer


Manish Batola

Expertise

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.

Experience

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.

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

Education/Credentials
-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 About.com. All rights reserved.