Home

Data Import and Export

 

Microsoft Access and Microsoft Excel

 

Introduction

As a spreadsheet application with various analysis tools, Microsoft Excel provides some features that are not available in Microsoft Access or some results can be difficult to get in the database. Therefore, it would not be unusual that you want to use the data of a table in a spreadsheet. You have various options.

Probably the easiest way to transfer data from Microsoft Access to a spreadsheet involves copying and pasting. To start, in Microsoft Access, you can open a table in Datasheet View then select one, some, or all records. After selecting the record(s), you can copy them, open Microsoft Excel, click the cell that would host the top-left value, and paste.

If you use the copy and paste technique, you would have to open the table first. Another technique consists of exporting, and you can do it without first opening the table:

  • Before exporting a table, in the Navigation Pane, you can first click the table. Then, on the Ribbon, click External Data. In the Export section, click the Excel button Excel
  • In the Navigation Pane, right-click the desired table, position the mouse on Export, and click Excel

This would open the Export - Excel Spreadsheet dialog box with the path where the file will be saved. The default folder is the My Documents:

The dialog box provides various options. For example, if you have Microsoft Office Excel 2007, you can accept the format with .xlsx extension. If you have a previous version, click the arrow of the File Format combo box and select the version. After making the selections, you can click OK. When the exporting is over, a message box would let you know.

Practical Learning:  Using a Spreadsheet

  1. Start Microsoft Access
  2. Open the Clarksville Ice Cream database you created in Lesson 2 and continued in Lesson 4

Importing a Microsoft Excel Spreadsheet

If you had worked on a spreadsheet to create a list of items and you want to convert that list into a table useful in a database, you can. Microsoft Access allows you to import a spreadsheet from Microsoft Excel, Corel Paradox, Lotus 1-2-3, or one of many other spreadsheet applications on the market. Before importing the spreadsheet, you should prepare it and you have many options. When creating the spreadsheet, you may have created a list anywhere on the document, without a title, and only the list of values. Here is an example:

When creating the spreadsheet, you may have created a list anywhere on the document, without a title, and only the list of values. In this case, the spreadsheet is ready. When you import such a spreadsheet, Microsoft Access would be able to figure out where the list starts and where it ends. Microsoft Access can even identify the column names and their records (where the records start and where they end)

In this case, the spreadsheet is ready. When you import such a spreadsheet, Microsoft Access would be able to figure out where the list starts and where it ends. Microsoft Access can even identify the column names and their records (where the records start and where they end). The database engine can also identify the data type of each column, based on the values used in their fields.

On the other hand, you may have created a spreadsheet that includes sections other than the list you want to use in your database. Here is an example:

You may have created a spreadsheet that includes sections other than the list you want to use in your database

If you try to directly import such a spreadsheet, the database engine may get confused. It would not know where the actual list starts and where it ends. If you insist, you can still import the spreadsheet but you would get unpredictable results. If you create a spreadsheet in Microsoft Excel, Corel Paradox, or Lotus 1-2-3, and if the spreadsheet contains a mix of the desired list and other items, you can create a "name" for the list. To create a name in Microsoft Excel, select the list:

If you create a spreadsheet in Microsoft Excel, Corel Paradox, or Lotus 1-2-3, and if the spreadsheet contains a mix of the desired list and other items, you can create a name.

Then, on the Ribbon, click Formulas. In the Defined Names section, click the Define Name... button. In the New Name dialog box, specify the name and click OK. To create a name in the other applications, check their documentation.

After creating, preparing and saving the spreadsheet, you can import it. To do this in Microsoft Access, start a database:

  • On the Ribbon, click External Data. In the Import section, click the Excel button Excel
  • If you have created a table that will receive the data, in the Navigation Pane, right-click that table, position the mouse on Import, and click Excel

This would start the Get External Data - Excel Spreadsheet wizard. You would then have to specify the file that holds the spreadsheet, and click OK. In the second page of the wizard, you would be asked to identify the section (sheet) where the table exists. You would also have the opportunity to provide some details about the data being imported.

Instead of manually importing a spreadsheet, you can select the values in a spreadsheet, copy them, and paste them in a table in Microsoft Access.

Practical Learning:  Importing a Microsoft Excel Spreadsheet

If you don't have Microsoft Office Excel 2010, skip this section

  1. From the resources that accompany these lessons, open the Clarksville Ice Cream.xlsx file.
    Notice a spreadsheet named Employees
  2. Select cells from C5 to F10
  3. On the Ribbon, click Home and, in the Clipboard section, click Copy
  4. Return to Microsoft Access and click the Home tab on the Ribbon
  5. On the table, right-click the intersection of the column headers and row header Intersection in the Clipboard section of the Home tab of the Ribbon, click the Paste button
     
    Employees
  6. When you receive a message box letting you know that a few records would be created, click Yes
  7. Double-click ID, type EmployeeID and press Enter
  8. To save the table, on the title bar, click the Save button
  9. Type Employees as the name of the new table and click OK
  10. Close the table

Whether you have Microsoft Office Excel 2010 or not, continue the lesson here

  1. On the Ribbon, click External Data
  2. In the Import & Link section, click Excel
  3. In the Get External Data - Excel Spreadsheet, click Browse...
  4. Locate the folder that contains the resources for our lessons and select it
  5. Select the Clarksville Ice Cream.xlsx file and click Open
  6. In the Get External Data - Excel Spreadsheet, click OK
  7. In the first page of the wizard, click the Show Named Ranges radio button and, in the list, click PayrollInformation
     
    Import Spreadsheet Wizard
  8. Click Next
  9. In the second page of the wizard, click the First Row Contains Column Headings check box
     
    Import Spreadsheet Wizard
  10. Click Next
  11. In the third page of the wizard, accept all defaults and click Next
  12. In the fourth page of the wizard, accept all defaults and click Next
  13. Set the name of the table to Payroll and click Finish
  14. In the last page of the wizard, click Close
  15. In the Navigation Pane, double-click the Payroll table to open it
  16. Double-click ID, type PayrollID and press Enter
  17. Close the Payroll table

If you don't have Microsoft Office Excel 2010, follow the same steps to import the Employees spreadsheet.

Microsoft Access and Text Files

 

Importing a Text File

You can create a table using data from a text file. If you intend to import a text document, format it so that Microsoft Access can recognize where a field starts and where it ends. The file can be created using Notepad. If the file is from another type of application, you can first convert its data to text, save it as a text file, and then import it.

If you are creating the (text) file in Notepad, the delimitation of a field is usually done by pressing Tab after creating the fields' content. Instead of the Tab key, you can also use a comma or a semi-colon to separate two fields. Since a field is usually made of more than one word (such as an address), you can enclose the content of each field in double quotes, as in "1600 Pennsylvania Avenue". A file that contains the data to be imported must also indicate where a record starts and where it ends. If you are creating the file in a text editor, the delimitation of a record is done by pressing Enter at the end of each record.

To import a text file in Microsoft Access:

  • On the Ribbon, click External Data and, in the Import section, click the Text File button
  • If you had previously created a table that would receive the values, in the Navigation Pane, right-click that table, position the mouse on Import, and click Text File

Practical Learning:  Importing a Text Document

  1. To start a new database, click File and click New
  2. Set the name to Red Oak High School
  3. Click Create
  4. On the Ribbon, click External Data
  5. In the Import & Link section, click Text File
  6. In the Get External Data - Text File dialog box, click the Browse button
  7. Locate the resources that accompany these lessons and select it
  8. Click Students.txt and click Open
  9. On the dialog box, click OK
  10. On the first page of the Import Text Wizard, accept that the text be Delimited and click Next
     
    Import Text Wizard
  11. In the second page, accept that the delimiter be set to Comma and check the box stating: First Row Contains Field Names
     
  12. Click Next
  13. Accept the defaults of the third page of the wizard and click Next
  14. Accept to contents of the fourth page of the wizard and click Next
  15. Accept to import to table Students and click Finish
  16. You receive a confirmation message when the table has been imported.
    Click Close
  17. In the Navigation Pane, double-click the Students table to open it
  18. Double-click ID, type StudentID and press Enter
  19. Save and close the Students table

Exporting a Text File

The easiest way to use data from a Microsoft Access database to an external application is to save it as plain text. The reason is that almost every application that deals with databases can import text and convert it into a spreadsheet or a database table. This is only possible if the text file is appropriately formatted. Fortunately, if you ask Microsoft Access to save a table to text, it would take care of formatting it.

To save a table as text:

  • In the Navigation Pane, click the table you want to export. On the Ribbon, click External Data and, in the Export section, click the Text File button
  • In the Navigation Pane, right-click the table that holds the data, position the mouse on Export, and click Text File

This would open the Export - Text File dialog box with the name of the file using the .txt extension. The default folder where the file would be saved is My Documents. If you want another, you can select it by clicking the Browse button. Once you are ready to export, you can click OK. This would open the Export Text Wizard that you can follow:

You would have various options to specify how you want Microsoft Access to format the document. You would also choose whether to include the column headers or not.

Microsoft Access and XML

 

Importing an XML File

An XML file is essentially a document made of at least one table. Normally, the table is very well structured because that's the essence of XML. An XML document can contain disparate data with various parents and different child nodes all over the place. Therefore, before importing an XML file to your database, you should be familiar with its structure.

A typical XML file starts with a root node:

<?xml version="1.0" standalone="yes"?>

Under it, the global parent node starts and closes itself:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
</FunFurniture>

This global parent node is not the table. Inside that node, you should have a node that would represent a table and it can repeat itself as many times as necessary:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
  </Employees>
  <Employees>
  </Employees>
</FunFurniture>

Notice that, in our example, what we refer to as a record is in plural. This is not required. It is just our choice. In this example, the Employees node is our table. Put it another way, each Employees node represents the table we want.

Inside each table, you should have the name of each column followed by its value:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
    <EmployeeNumber>924795</EmployeeNumber>
    <FirstName>Donald</FirstName>
    <LastName>Tripleton</LastName>
    <Title>Sales Manager</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>274957</EmployeeNumber>
    <FirstName>Jeanne</FirstName>
    <LastName>Wooley</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>684078</EmployeeNumber>
    <FirstName>Irene</FirstName>
    <LastName>Polsen</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>297149</EmployeeNumber>
    <FirstName>Monica</FirstName>
    <LastName>Jackson</LastName>
    <Title>Sales Associate</Title>
  </Employees>
</FunFurniture>

The group of nodes inside the table represents the columns and their values. Of course an XML document can be more than that but this is a typical structure of a normal XML document that can be imported.

After creating and saving an XML file, you can import it in Microsoft Access. To do this, in the External Data tab of the Ribbon and in the Import section, click the XML File button XML File. This would display the Get External Data - XML File dialog box. After selecting the file and clicking OK, a dialog would come up to help you identify the table to use in the document. It would display the node(s) that indicate(s) a table.

Practical Learning: Importing an XML File

  1. To start a new database, click File and click New
  2. Set the name to Fun Furniture
  3. On the Ribbon, click External Data
  4. In the Import & Link section, click XML File XML File
  5. In the Get External Data - XML File dialog box, click the Browse button
  6. Locate the resources that accompany these lessons and select it
  7. Click employees.xml
  8. Click Open
  9. Click OK
  10. In the list, click the Employees node and accept to import both the structure and the data of the table
     
    Import XML
  11. Click OK
  12. You receive a confirmation message. Click Close
  13. In the Navigation Pane, double-click the Employees table to open it

Exporting to XML

If you have created a (complete or semi-complete) database that you want to use outside of Microsoft Access, one way you can transfer it is to change its format into XML. Fortunately Microsoft Access can take care of the whole process for you. In fact, besides exporting a table, you can also ask Microsoft Access to create both a schema file and a style sheet for the table.

To export a table to XML format, in the Navigation Pane:

  • Right-click the table that holds the data, position the mouse on Export, and click XML File
  • Click the table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> XML File

The Export - XML File dialog box that comes up allows you to verify the name of the file that will be created and the path where it will go. Once you are ready, you can click OK. A dialog box would come up asking which one(s) of the three files you want to have created:

Export XML

If you want to create a more elaborate XML application with advanced options, you can click the More Options button. This would close the previous dialog box and open another one:

This dialog box allows you to specify more details on how the table should be exported. For example, you can change the encoding scheme you want. The two options available are UTF-8 (which should be enough for characters in US English) and UTF-16 (if you are planning to use Unicode or international characters) for characters referred to as wide characters. By default, when you ask Microsoft Access to create an extensible style sheet (XSL), it would create the file using the same name as the table. If you want a different name, you can click the Transforms button. This allows you either to select a different file or to create the file with a different name. Also, by default, if you ask Microsoft Access to create a schema, it creates an XSD file using the same name as the table. If you want a different name, you can click the Schema tab and specify another name for the file.

After making your selections, you can click OK. The file(s) you specified would be created in the folder that was indicated.

 
 
 

Microsoft Access and the Web

 

Importing an HTML File

If you have a table on a web page and the table is well structured for a database, you can use it as a table of your database. That is, you can import it in Microsoft Access. Like a normal text file, a typical HTML document can contain anything. This means that you should not attempt to import just any HTML file into your database. When in doubt, you should first check it. A good candidate to be imported should have all the necessary tags of an HTML table: table, tr (for the records), and td (for the columns). As long as the table is well created inside of the document, Microsoft Access can identify, analyze, and decide whether it is ready to be imported.

To import an HTML file in Microsoft Access, on the Ribbon, click External Data and, in the Import section, click the More button and click HTML Document. The steps to follow are the same for a text document.

Practical Learning:  Importing a Text Document

  1. To start a new database, click File and click New
  2. Set the name to Contributor Fundraising
  3. On the Ribbon, click External Data
  4. In the Import section, click More and click HTML Document
  5. In the Get External Data - Text File dialog box, click the Browse button
  6. Locate the resources that accompany these lessons and select it
  7. Click fundraising.htm and click Open
  8. Click OK
  9. In the first page of the Import HTML Wizard, click First Row Contains Field Names
     
    Import HTML
  10. Click Next
     
    Import HTML
  11. Accept the defaults of the second page of the wizard and click Next
  12. Accept to contents of the third page of the wizard and click Next
  13. Accept the suggested name of the table as Fundraising and click Finish
  14. You receive a confirmation message. Click Close
  15. In the Navigation Pane, double-click the Employees table to open it
  16. Double-click ID, type FundraisingID and press Enter
  17. Close the Fundraising table

A Microsoft Access Table on a Web Page

You can transfer a Microsoft Access table and its data to a web page and you have many options. If you are using an application such as Microsoft FrontPage or Microsoft Expression Web, you can copy and paste. To do this:

  • If you want to copy all columns and all records, in the Navigation Pane of Microsoft Access, right-click the table and click Copy. In the web page of the application (such as Microsoft FrontPage or Microsoft Expression Web), right-click the section you want on the web page and click Paste. Here is an example:

    Employees
    Employees
    Employee ID First Name Last Name Title HourlySalary
    1 John Simson Regional Manager $42.75
    2 Darius Sandt Sales Representative $18.60
    3 Ernestine Krazucki Public Relations Manager $28.15
    4 Paul Motto Sales Representative $16.45

  • If you want to copy all columns and all records, open the table in Microsoft Access, press Ctrl + A to select everything. Press Ctrl + C to copy. In the web page of the application, click the section you want on the web page and press Ctrl + V to paste 
  • If you want to copy only some columns and/or some records, open the table in Microsoft Access. Make the selection. Right-click the selection and click Copy. In the web application, right-click the section you want on the web page and click Paste or click the section and press Ctrl + V 

Some other applications do not support copy and paste. An alternative is to export the table to HTML. To save a table as HTML, in the Navigation Pane:

  • Right-click the table that holds the data, position the mouse on Export, and click HTML Document
  • Click the table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> HTML Document

This would open the Export - HTML Document dialog box indicating the folder where the file will be saved, followed by the name of the file itself and its .html extension. Eventually, when the file has been saved, it would be complete with all the normal HTML tags. If you want the file to directly be part of a web site or a web project, you can change the path to point to your web folder. If you are only interested in the table, let the file be saved. Then, open the code of the document, which you can do with any text editor such as Notepad, copy only the code of the table and paste it in the desired section of your actual web page.

A Microsoft Access Database on the Web

Microsoft Access does not provide its own native means of connecting a database to the Internet, but it can be used as a server database accessed from the web. To make this happen, you can do everything manually or you would use an external application such as Microsoft Visual Studio, CodeGear Delphi.NET, Microsoft Expression Web, or many other applications. You can create an ASP application, a PHP web site, an ASP.NET project, etc.

Microsoft Access and Microsoft Word

 

Introduction

There are various ways you can use Microsoft Word with a Microsoft Access database. The simplest way consists of copying and pasting. To transfer a database table, in the Navigation Pane of Microsoft Access, you can right-click a table and click Copy. In a Microsoft Word document, you can paste in the desired section. The whole table, including its columns and all the records, would be created in the document. Instead of the whole object, first open a table in Microsoft Access, select only some columns and/or some records, copy them, and then paste them in a Microsoft Word document.

As opposed to copying from a database table and pasting to a table, you may want the reverse. That is, you can copy a table from a Microsoft Word document. To do this, in Microsoft Word, select the table in a document and copy it. Start a table in Microsoft Access and paste in the Add New Field box.

Mail Merge

Mail merging allows you to use data on your database to create letters, labels, envelopes, and other documents that require external data originating from another document. When performing a mail merge, you usually do not need all the fields that are part of a table.

To start a mail merge, in the Navigation Pane:

  • Click the table that holds the information you want to use. On the Ribbon, click External Data. In the Export section, click Word Merge
  • Right-click the table, position the mouse on Export and click Word Merge

This would open the Microsoft Word Mail Merge Wizard:

Mail Merge

And click OK. Microsoft Word would open. In the Mail Merge window, if you want to create a letter to be sent out, accept the Letters radio button and click Next. In the second page of the wizard, you will have the choice of creating a new Microsoft Word document or using an existing document. If you want to create a new document, click or accept Use the Current Document. If you click Start From Existing Document, you will be asked to specify the document, in which case you should click Open, select the document, and click Open:

Mail Merge

After this, the Mail Merge window would come back to the Use Current Document option. Click Next: Select Recipients.

To insert other types of items, in the Mail Merge window, you can click the Address Block link, the Insert Address Block dialog box would come up. The Insert Address Block dialog box is made of various sections. It allows you to specify a type of greeting and other pieces of information to be inserted in the document. After making the selection(s), you can click OK.

After creating the document and adding the necessary fields to it, you can preview and review it. To do this, in the Mail Merge window, click the Next: Preview Your Letters link. When you do this, the letter appears with the value(s) of the first record. To review the document with the other values, in the Mail Merge window, you can click the previous or the next buttons.

After reviewing the document, in the Mail Merge window, you can click the Next: Complete The Merge link. You can then save, print, and manage the document. In the same way, you can create labels or envelopes. 

Practical Learning:  Creating a Mail Merge

If you don't have Microsoft Office Word 2010, skip this section

  1. Start Microsoft Word
  2. From the resources that accompany these lessons, open Promotion.docx
  3. Press Ctrl + A to select everything
  4. Press Ctrl + C to copy
  5. Close Microsoft Word and return to Microsoft Access
  6. From the resources that accompany these lessons, open the Bethesda Car Rental1 database
  7. In the Navigation Pane, in the Tables section, click Customers
  8. To start a mail merge, on the Ribbon, click External Data
  9. In the Export section, click Word Merge
  10. Click the second radio button:
     
    Mail Merge
  11. Click OK.
    Microsoft Word starts.
  12. In Microsoft Word, press Ctrl + V to paste the Promotion.docx document (this would be the equivalent of creating or designing the document)
     
    Mail Marge
  13. In the Mail Merge window, to create a letter to be sent out, accept the Letters radio button
     
    Mail Merge
     
    Click Next: Starting Document
  14. In the Select Starting Document section, make sure Use the Current Document is selected Click Next: Select Recipients
     
    Mail Merge
  15. Click Next: Write Your Letter
     
    Mail Merge
  16. In the Microsoft Word document, double-click Date and press Delete
  17. On the Ribbon of Microsoft Word, click Insert
  18. In the Text section, click Date & Time
  19. In the Available Formats list box of the Date & Time dialog box, click the second option (day, month, and year)
     
    Date and Time
  20. Click OK
  21. Press Enter
  22. Click the right side of Dear
  23. In the Mail Merge window, click More Items
  24. In the Fields list, click FirstName
     
    Insert Merge Field
  25. Click Insert
  26. Click Close
  27. Press the Space bar
  28. In the Mail Merge window, click More Items...
  29. In the Fields name, double-click LastName
  30. Click Close
  31. In the Mail Merge window, click Next: Preview Your Letters
     
    Mail Marge
  32. In the Mail Merge window, click the next recipient button Next Recipient and observe the name in the document
  33. Click the next recipient button 4 times and observe the names in the document
  34. Click the previous recipient button 2 times and observe the names in the document
  35. In the Mail Merge window, click Next: Complete the Merge
  36. Close Microsoft Word
  37. When asked whether you want to save, click Save
  38. Set the file name as Bethesda Car Rental Promotional Campaign1
  39. Click Save

Microsoft Access and Microsoft Outlook

 

Introduction

There are unlimited types of files you can import in a Microsoft Access database, including files created from known Microsoft Office applications and files from any other type of application, as long as the document has been prepared appropriately.

To import a dBase table, a Paradox table, or a Lotus 1-2-3 spreadsheet, in the Import section of the External Data tab of the Ribbon, click More and click the type of file you want to import:

Import

Depending on the type of file, a wizard would come up and guide you.

Microsoft Office Outlook

If you had created an address book in Microsoft Office Outlook, you can use it to create a table in your database. You would import it. When you do this, Microsoft Access is able to recognize all the fields you would have used in the address book and create the table columns. To import an address book, on the Ribbon, you can click External Data and, in the Import section, click More -> Outlook Folder. This would open the Get External Data - Outlook Folder dialog box with the top radio button selected as the default. In most cases, you can accept it and click OK. This would start the Import Exchange/Outlook Wizard with some of the folders from your Microsoft Outlook installation. You can expand a node and select the desired object:

After selecting the object, the file, or the address book, click Next. The next pages of the wizard would assist you in identifying the columns of the list and complete the table.

Lesson Summary

  

Exercises 

 

Yugo National Bank

  1. Open the Yugo National Bank1
  2. From the resources that accompany these lessons, import the yugo_national_bank_employees.htm file (it is an HTML document). Specify that the top row will provide the columns names. Let the wizard create a primary key and accept all defaults. After importing, rename the table as Employees. Open the table in Datasheet View and change the name of the first column from ID to EmployeeID
  3. From the resources that accompany these lessons, import the yugo_national_bank_customers.txt file (it is a text file). Specify that the top row will provide the columns names. Let the wizard create a primary key and accept all defaults. After importing, rename the table as Customers. Open the table in Datasheet View and change the name of the first column from ID to CustomerID

Watts A Loan

  1. Open the Watts A Loan1 database
  2. Use the Table button of the Tables section of the Ribbon to create a new table. Replace ID with EmployeeID. Use the Add New Field column to create the following additional columns  FirstName, LastName, Title, WorkPhone, Address, City, State, ZIPCode, Country, and HomePhone. Save the table as Employees and add the records to it
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Next