Importing Data from an Excel spreadsheet and a Text File

Locked lesson.

Upgrade

Quick reference

Importing Data from an Excel spreadsheet and a Text File

Import data into Access from an Excel spreadsheet and a Text file and append it to the bottom of the Ticket table. 

When to use

Import data from an external application when you want to add a large number of records quickly. 

Instructions

Currently, we have two trips stored in the Ticket table. However, TechGurus have a lot more tickets that need to be added to the table. Instead of manually adding each record in datasheet view, we can save ourselves time by importing the records in bulk.  

In our example, we have the full list of tickets stored in an Excel spreadsheet and a Text file. 

Importing from an Excel Spreadsheet

  • Download the file 'TechSalesTickets.xlsx' from the course files folder. 
  • Open the Excel file and review the column headings. 

It's very important that the column headings in the Excel file match the field names in the Access table. If they do not match or there is a spelling discrepancy, the import will not work correctly as Access will not be able to map the fields. 

  • From the External Data tab, in the Import and Link group, select New Data Source. 
  • Hover the mouse over From File and select Excel from the menu. 

The Get External Data window will open. This is a wizard-style dialog box that will guide us through the import process. 

First, we need to select the Excel file to import. 

  • Click the Browse button and navigate to the folder where the Excel file is stored. 
  • Select the file and click Open. 

Next, we need to specify where we want to import this data into the database. We want to append the new records to the end of the current table. 

  • Select Append a copy of the records to the table 'tblTicket'. 
  • Click OK. 
  • If prompted, select Yes to close the current table. 
  • From the Import Spreadsheet Wizard, click Next. 
  • Choose the table to import the records to, 'tblTicket'. 
  • Click Finish to import the records. 

Importing from a Text File

Let's now look at how to import and append the same data from a text file. 

  • Download the file 'TechSalesTicketing.txt' from the course files folder. 
  • Open the text file in Notepad. 
  • Notice how each field is separated using a tab delimiter. 
  • Close the text file. 
  • From the External Data tab, in the Import and Link group, select New Data Source. 
  • Hover the mouse over From File and select Text File from the menu. 

The Get External Data window will open. This is a wizard-style dialog box that will guide us through the import process. 

First, we need to select the text file to import. 

  • Click the Browse button and navigate to the folder where the text file is stored. 
  • Select the file and click Open. 

Next, we need to specify where we want to import this data into the database. We want to append the new records to the end of the current table. 

  • Select Append a copy of the records to the table 'tblTicket'. 
  • Click OK. 
  • If prompted, select Yes to close the current table. 

The difference here is that we need to tell Access, which delimiter is used to separate each field in the text file. 

  • Select Delimited and click Next. 
  • Select Tab as the delimiter. 
  • Select First Row Contains Field Names. 
  • Click Next. 
  • Choose the table to import the records to, 'tblTicket'. 
  • Click Finish.
  • Click Close to import the records. 

Reopen the Ticket table to view the appended records.

Hints & tips

  • Data stored in a text file could be separated by any type of delimiter. If the delimiter is not listed during the import process, choose 'Other' and type in the delimiter into the blank field. 
  • It is possible to save the import steps. This means that if we import another file, we can skip straight to the end of the process. 
Login to download
  • 00:04 So we're making pretty good progress on our tickets table.
  • 00:09 But what we're lacking at the moment are records.
  • 00:12 Currently we only have two, and
  • 00:14 we really need to have a lot more records to progress through this course.
  • 00:19 Now, of course you could enter all of your records manually.
  • 00:22 And it might be if you're adding these over time you might be adding a few a day,
  • 00:26 which is absolutely fine.
  • 00:28 But there might come a time where you want to import records into your table.
  • 00:32 So in this lesson I'm going to show you an example of how you can import records from
  • 00:37 an Excel file.
  • 00:38 And also how you can import records from a text file.
  • 00:42 So let's deal with Excel files first of all, and
  • 00:44 let's take a look at the file that we're going to import.
  • 00:48 So this is my Excel file, and if you want to use this
  • 00:51 file you're going to find it in the Course Files folder.
  • 00:54 And it just has a few more customers in here.
  • 00:57 It's not a huge list, but it's enough for us to work with in this course.
  • 01:01 Now one thing to bear in mind if you want to do an import.
  • 01:05 You must ensure that the column headings that you have in your Excel
  • 01:08 spreadsheet exactly match the column headings that you have in your table.
  • 01:13 So for example, my fields in my table, I don't use any spaces in those field names.
  • 01:19 And so I haven't used any spaces in the column headings just here.
  • 01:24 If there is a discrepancy between your column headings and
  • 01:27 the fields in your table, it's not going to be able to map them correctly and
  • 01:31 know where to place this data.
  • 01:34 So that is really the most important thing when it comes to importing data.
  • 01:39 So now that we've seen what our data looks like, let's import this file in.
  • 01:44 So for this we need to jump up to the External Data tab.
  • 01:48 And in the first group, Import and Link, we have new data source.
  • 01:53 And there are loads of different data sources that we can effectively
  • 01:57 connect to to import data in.
  • 01:59 So it might be that you have data stored in a SharePoint list,
  • 02:03 or maybe you have it in an Outlook folder.
  • 02:06 Or maybe you have it in another Access database,
  • 02:09 or simply in an Excel file or a text file.
  • 02:13 Wherever it is you have an option on here to import it in.
  • 02:17 So we're going to import from an Excel file.
  • 02:20 And again, this is going to pop up a wizard style dialog box that will walk you
  • 02:24 through the process of importing.
  • 02:26 And the first thing you need to do is select the file that you want to import.
  • 02:30 So let's click on Browse, find your file in the folder, select it and
  • 02:35 click on Open.
  • 02:37 Now we need to tell access how we want to import that data.
  • 02:42 So do we want to import the source data into a new table?
  • 02:46 Do we want to append a copy of these records onto the current table?
  • 02:52 Or do I want to link to a data source by creating a linked table?
  • 02:56 Well, I want to append these records to the bottom of the records that I already
  • 03:01 have in tblTicket.
  • 03:03 So let's say append a copy, and then I've only got one table so
  • 03:07 it's the only one I'm getting in the list, tblTicket.
  • 03:11 Click on OK.
  • 03:13 Now notice here it says the table that you're trying to append records to is
  • 03:16 currently open.
  • 03:18 So we can't actually run through this process whilst we have the table open.
  • 03:22 So if I click Yes, it's going to close the table down and
  • 03:25 take me straight to the Import Spreadsheet wizard.
  • 03:28 Now because my data is in Excel and Excel has a columns and rows structure,
  • 03:34 it's very easy for access to work out how to break up this data.
  • 03:38 It's simply using the columns.
  • 03:41 So I could look here and say Yes, that all looks brilliant.
  • 03:44 Let's click on Next, and then I can import that data in.
  • 03:48 All I would do here is click Finish and
  • 03:50 then it's going to import straight into this table.
  • 03:53 Now I'm not going to do that with this Excel spreadsheet.
  • 03:55 Because I want to show you how to import the same data, but
  • 03:59 when it's stored in a text file.
  • 04:01 So we're going to cancel on here and we're going to import a text file instead.
  • 04:06 Before we do,
  • 04:07 let's take a look at how the text file is different from the Excel file.
  • 04:12 So the text file contains exactly the same data.
  • 04:15 But one thing to note with the text file is that in general your data looks a lot
  • 04:20 less organized.
  • 04:21 Because it doesn't go by the whole rows and columns principle.
  • 04:25 Instead, what is separating each of our columns is a tab delimiter,
  • 04:30 and that is the terminology that you need to remember.
  • 04:34 Now it might be that depending on what text file you're importing that you have
  • 04:38 your different fields separated with something else.
  • 04:41 So it might be a comma, it might be a semicolon, it might be a dash symbol.
  • 04:48 So it's always worth, before you do the import,
  • 04:51 opening up the text file and taking a look and seeing what delimiter is in use.
  • 04:57 So for me, that is a tab.
  • 04:59 Let's remember that for our import.
  • 05:02 This time we're going to go up to New Data Source from File and
  • 05:05 we can choose Text File.
  • 05:08 We need to do the same process.
  • 05:09 Let's browse.
  • 05:10 There is my text file.
  • 05:12 Let's click on Open.
  • 05:14 We want to do exactly the same thing.
  • 05:16 We want to append it to the bottom of tblTicket.
  • 05:19 Let's click on OK.
  • 05:21 But this time we get something slightly different.
  • 05:23 Access hasn't quite been able to work out at this stage how to
  • 05:27 separate those fields.
  • 05:29 And we have a choice at the top, delimited or fixed width.
  • 05:34 Now delimited just means that your fields are separated by some kind of character.
  • 05:38 So that is the one that we want to choose.
  • 05:41 Let's click on Next.
  • 05:42 And this is where we get to define what is separating those fields.
  • 05:47 Is it a tab, a semicolon?
  • 05:48 A comma, a space?
  • 05:50 Or is it something else entirely?
  • 05:52 So whichever option applies to you, you would select that.
  • 05:55 For me it is tab, which was selected by default.
  • 05:59 And if I take a look at the preview underneath I can see that yes,
  • 06:02 it now knows where those fields are separated.
  • 06:06 Another important thing here.
  • 06:08 Make sure that you select First Row Contains Field Names if you have column
  • 06:13 headings in there.
  • 06:15 So let's click on Next.
  • 06:17 I'm importing to tblTicket, Finish.
  • 06:21 And at this stage I get a chance to save my import steps.
  • 06:25 So if this was a process that I found myself doing all the time,
  • 06:29 I could save a lot of the settings that I've just selected and
  • 06:33 jump straight to this last stage the next time I import a file.
  • 06:38 Now I'm not going to save.
  • 06:39 Let's just click on Close.
  • 06:41 So now this is where the magic happens.
  • 06:43 Let's double click to open the table.
  • 06:45 And would you look at that, we now have a much longer records list.

Lesson notes are only available for subscribers.

Gift this course