Hello again and welcome back to our course on Access 2016.
In this section I’m going to introduce you to one or two basic ways of importing data
into an Access database.
Now before we start I should point out that so far and in fact for most of this course
when we talk about an Access database we really mean a single file, an ACCDB file.
And within that ACCDB file there are many things.
Amongst the things within an ACCDB file there will be a number of tables.
Now we’ve already created one or two tables and of course in a database that supports
particularly some kind of business activity there will probably be many tables.
Now it’s not the case that the tables holding the data will always be in the database, in
our case the ACCDB file that you’re working on.
Sometimes the tables may be external to your database.
Let me give you an example.
I mentioned just a little earlier on that when it comes to dealing with people booking
vacations with Esprit de Tour and paying for vacations they probably book and pay for them
using their local currency.
So there will be a table somewhere where we look up a currency conversion rate.
Now that is not a table that we would necessarily maintain in our own database.
We may use a table in another database somewhere else or we may use a service, links to a currency
exchange database.
And it will quite often be the case that a database application will get its data from
a number of tables in a number of different places.
Now when we get data in that way, when we basically access data from somewhere else
we use the general term Linking.
And one of the things that you can do in Access is to link two tables in other databases.
Now I’m not going to cover linking in this course.
If you go to the External Data tab you’ll see that there are two groups on that tab.
There is an Import and Link group and there’s an Export group.
Now Export we’re going to look at later.
Import and Link, well I’ve just said we’re not going to cover link.
What we’re going to look at in this section is Import.
Now what happens when we import data is that we will get data from somewhere else but we
will import it into one or more tables in our own Access database.
Now there are various places that you can import data from.
For instance you can import data from an Excel workbook.
You can import data from another Access database.
You can import data from a selection of ODBC.
That’s Open Database Connectivity databases.
That means other types of database, not necessarily Access databases but which conform to a standard
that is called ODBC.
And in fact there are many other places.
You can get data from XML files.
You can get data from SharePoint, from data services.
This would include something like a currency exchange service.
So there are many other places you can get data from to import into Access.
But what I’m going to cover on this section by way of an introduction to importing data
is importing data from a text file.
And this is a very common way of importing data into a database.
Having said that for those of you who are familiar with Microsoft Excel and for those
of you who have a copy of Microsoft Excel I’m also going to partly demonstrate importing
from Excel.
So if you have and use Excel then you might like to try this particular option because
text files and Excel are two of the most common ways of getting data into an Access database.
So let’s start with importing some trips into the Esprit de Tour database.
What I’ve got here is an Excel workbook.
If you don’t use Excel or you don’t understand Excel workbooks there’s absolutely no need
to worry because this is really only for the people that use Excel.
You’ll still be able to follow what I do as I import data into the Esprit de Tour database
and you’ll still be able to do the exercise in the next section.
Now I’ve got about a dozen trips here and they’re the first ones that the company
is going to offer.
And each column in the workbook corresponds to a field in the trip table that we created.
And the column headings tell you which field they correspond to.
So we’ve got Code, TripName, ActivityLevel, Country.
You’ve seen all those before of course.
Now for each of the trips there is one row in the Excel workbook.
So the first trip, GCA, Grand Canyon Family Rafting Adventure, activity level moderate,
country U.S.A.
There’s then a description.
The duration in days and minimum price in U.S. dollars, maximum price in U.S. dollars.
And there are about a dozen of these.
Now what I’m going to do is to show you how you would start importing this data in
Excel.
If you don’t use Excel as I say don’t worry.
We’ll be moving on to the way that we’re actually going to do it very shortly.
So let me close the workbook.
Now I’m in the Esprit de Tour database.
I’m going to go to the External Data tab and I’m going to click on Excel.
Get external data from an Excel spreadsheet.
Now the first thing we do is to locate the spreadsheet.
Well this particular spreadsheet I’ve put in the course files folder.
So if you do want to try this yourself as an Excel import you’ll find it in that folder.
And that’s what it’s called EspritDeTourTripImport.xlsx.
So I open that which is fine and then I have three options.
First of all I could import the source data into a new table in the current database.
So I could import that data and put it in a brand new table.
I’m not going to do that on this occasion.
I could append a copy of the records to the existing table and then I choose my table.
Now of course in the Esprit de Tour database we only have one table at the moment.
It’s the trip table.
And in fact appending those records to that table is exactly what I’m going to do.
The third option is to link to the data source by creating a linked table.
What I could do is not to import the data into Access at all, to leave it in that Excel
workbook and to link to it in the Excel workbook.
And the way that I do that is I choose that third option and what Access basically does
is to set up, it’s a sort of dummy table in a way.
It’s almost like a window into that workbook so that you can access the data as though
the data were in a table in this Access database but the data is actually held in an Excel
workbook.
Now once you’d made the choices we’ve got there.
So you’ve chosen the Excel workbook.
You’ve clicked on Append a copy of the records to the table, on the table.
You then click on OK and you go through a sequence of steps to make sure that you’re
getting the right information.
Let’s just start this off without actually doing it.
So I click on OK and as you can see it’s already worked out how to slice the data up.
Let’s just scroll along there.
Let’s go to the end and make sure it’s got prices and durations.
Okay.
It looks fine actually.
So we then click on Next and away we go doing the import.
Now on this occasion I’m not going to do that so I’m going to cancel, but by all
means try it yourself.
And we’re going to do the import from a text file.
The text file in question is also in the course files folder.
It’s called EspritDeTourTripImport.txt and I have it open here in Notepad.
Apart from giving those of you who use Excel the opportunity to import this data from Excel
looking at the data in Excel it’s a lot easier to see the structure of the data than
it is in this text file.
The text file is in a format that’s called Tab Delimited.
And it’s got a number of rows.
The first row contains the field names.
These are the field names that match the field names in our trip table.
And then each subsequent row in this file is one of the trips.
Now each of the fields in a trip is separated from the next by a tab character and that’s
why it’s called Tab Delimited.
And when Access reads a file like this it recognizes where each field starts by the
tab characters.
Now you don’t have to use a tab character as a separator and various other separators
are available.
One of the most common separators to use is a comma.
And we refer in that case to a comma separated file or a comma separated values file, a CSV
file.
Now I can’t really use commas here because the data itself contains commas.
Look at the third row there, ITI.
The description of the trip.
Immerse yourself in Italy’s iconic cities of Venice, Florence, etcetera.
If I tried to separate the fields here with commas Access will get terribly confused because
it would think that Venice was the end of one field and Florence was a different field
altogether and it would just get totally confused.
So in this case because none of the actual data contains tab characters I’m using tab
separators.
So this is a Tab Delimited file.
Now if you decided that you wanted to use a different separator, such as commas and
there are many other separators that you can use, you can to some extent overcome the problems
that I’ve just talked about by enclosing text in double quotes.
So for instance you could take the whole of that description, Immerse yourself in Italy’s
iconic cities onwards, the whole of that field and put it in double quotes and Access would
recognize that it was all one field.
So that would be one way of getting round the problem I’ve just described.
But on this occasion using tab delimiters should be fine.
So let’s close that and let’s import the contents of this file into the Esprit de Tour
database.
I should point out that in the course files folder I’m importing this into version two
of the Esprit de Tour database.
The start of this is just like the Excel version.
So we go up to External Data.
On this occasion we choose Text File and that starts the Get External Data Text File Wizard.
We browse to that text file in the course files folder.
We’re going to use the second option, Append a copy of the records to the table tblTrip.
Click on OK.
We now have a choice.
We can either say it’s a delimited file which of course it is.
You can actually present the data in the form of fixed width file.
So we could have a fixed number of characters for the code, a fixed number for the trip
name and so on.
But on this occasion we’ve chosen delimited so we go with delimited.
Click on Next.
One thing I need to make clear to Access is that the first row actually contains the field
names.
The first name is not one of the trips.
So there’s a checkbox here, First row contains field names.
I check that.
Now Access will know what the field names are, which data corresponds to which field
name.
And note that Access has already taken a look at this file and has suggested that tab is
the delimiter, which of course is correct.
Now at this point it’s a good idea to scroll through and just check that it seems to have
sorted everything out okay.
Well certainly all of those numbers at the end look right to me and the descriptions
look fine.
So let’s click on Next.
There’s a checkbox there, I would like a wizard to analyze my table after importing
the data.
If I was importing this into either a new table which is one of the options at the beginning
or to a table where I hadn’t really set it up properly up yet the wizard would actually
look at the data after importing it and suggest settings for the fields.
Now we’ve already spent quite a bit of time in defining the properties of the fields in
this table so we’re not going to check that box.
We’re going to click on Finish.
And Access 2016 thinks that it has successfully imported the data.
If I was going to get a lot more data in this form that I might want to add to that table
I could check this box here and save the import steps.
And one of the options at the left-hand end of the External Data tab is to use a saved
import.
So to save me making all these same selections again I could select a saved import and basically
it will perform the same operation on a different text file for me subsequently.
I’m not going to save these import steps.
I’m just going to click on Close.
And let’s open up the trip table and see what we’ve got.
Well a first look at that suggests to me that that’s pretty good actually.
Now of course you can’t see the whole of the descriptions because they’re too long
to show in the available space but that doesn’t mean to say that the data is not there.
Let me just drag that over to the right.
That’s all there.
We’ll talk about other ways of looking at the data later on.
But apart from the first record which is of course our sort of dummy one that we tested
things out on everything else looks fine.
That’s the end of this section.
I’ll see you in the next one.