Jan's Working with Databases

Step-by-Step: Export Table from Access

---

What you will learn:

to export as text file to export as text file without formatting to export as a formatted Excel file to export to another Access database

---

Start with:  , resource files, worldtravel-Lastname-Firstname.accdb

World Travel Inc. needs to share its Staff table with some of its offices that have not yet started using Access. The table needs to exported as a comma-delimited text file (csv) and also as an Excel spreadsheet (xls) since different offices have been using different software. (It is a BAD PLAN for different parts of a company to use different software, but is not unusual in the real world!)

Once you have other kinds of objects in Access, you can export them similarly.

Export Table as Formatted Text File

You have a number of choices to make when exporting data as a text file. Some parts do not export unless you choose to export the formatting and layout. Some parts won't export at all, like attachments or images. First let's look at what happens when you export a table as formatted text.

1. If necessary, open the database worldtravel-Lastname-Firstname.accdb which you created earlier and have been modifying.
   
2. Select the table Staff in the Navigation Pane.
   

3. From the External Data ribbon tab in the Export tab group (NOT the Import and Link tab group!),
   click the button Text File .
   The Export -Text File dialog appears.

   Problem: The Export buttons are grayed out (not available)
   You did not select an object to export in the Navigation Pane.
   Solution: Select the Staff table. Then the Export buttons will be available.  

4. 

   Click the Browse button
   The File Save dialog opens.

5. If necessary, navigate to the folder databases project3 on your Class disk.
6. For the file name use: Staff.txt
7. For the file format, the Save as type box should show:
   Text files (*.txt, *.csv, *.tab, *.asc)
    
   Problem: File name is not Staff
   You selected the wrong table in the Navigation Pane.
   Solution: Cancel this dialog and the Export - Text File dialog and start over.
8. Click the Save button.
   The File name text box now shows the path that will be used to save the text file.

   

9. Check the boxes for 'Export data with formatting and layout' and 'Open the destination file after the Export operation is complete'.

10. Click on OK.
    A dialog opens about how to encode the file.

11. Leave the default selection in place, Windows, and click on OK.
    Two things happen:
    • The Export dialog changes to the last page, Save Export Steps
    • The file Staff.txt opens in your text editor, which is Notepad for most people.

    The dialog Export - Text File offers you the chance to save these steps. That is a good idea only if you will be doing this same export regularly.
    

12. In Notepad, if necessary, turn off Word Wrap so you can see how the columns line up nicely when you export with formatting and layout. (Format > Word Wrap)

13. Scroll to the right to see all of the columns.
    What did not export happily?

    • Only the first 18 characters show. This chops off text in the Memo and Long Text fields like Notes but also affects the EmailExtension and Address fields.
    • Some records have an apostrophe ' character in front of the EmailExtension value. That is not because of the export. Excel uses this character to make a cell treat a value as text. So that is a problem with the original data!

    If there are only a few errors, you can hand edit the text file to make corrections. We won't bother at this time.

14. Close your text editor.
15. Click on Close in the Export dialog in Access.

---

Export Table as Text File Without Formatting

When you choose NOT to export with formatting, you must tell Access more about what you want to see in the text file. The choices are similar to the ones for importing a text file. Let's try this export again.

1. Select the table Staff in the Navigation Pane.
   

2. From the External Data ribbon tab in the Export tab group (NOT the Import and Link tab group!),
   click the button Text File .
   The Export -Text File dialog appears, still set to the databases project3 folder.

3. Change the name of the file to Staff-notformatted.txt.

   
   

4. Verify that none of the check boxes are checked.
5. Click on OK.
   The Export Text Wizard opens.
   
6. Select Delimited.
7. Click on Next >.
   
8. Check the box "Include Field Names on First Row".
9. Verify that Comma is selected and the Text Qualifier is a double quote,".
10. Click on Next >.
    

11. Verify that the path is to the folder databases3 on your Class disk and that the file name is Staff-notformatted.txt.

    There is no Browse button here. If the path is not correct, you will have to type in the correct path yourself!
    

12. Click the button Advanced... at the bottom left of the dialog.
    A dialog of options appears.
    You could change some of the defaults for the export here. Especially useful is the list of field names. You can delete fields here!
13. Click on Cancel to close the dialog without making changes.
14. Click on Finish.
    The final step of the wizard appears, offering you the option to save this export sequence.
    
15. Click on Close to close the dialog without saving the export steps.
    The text file you just exported did not open automatically.
    

16. In a Windows Explorer window, navigate to your Class disk and open the file Staff-notformatted.txt in Notepad.

    Word Wrap should still be off.

17. Scroll horizontally to see if any data got cut off.
    Success!
    Looks like those longer fields came through correctly this time. This is a serious advantage to this method of export!

    Null or blank values show just the comma separators.

    This file is not easy to read for people. Without formatting, the field names do not line up with the values. But computers can read this very well and can match the correct parts.

18. Close your text editor.

---

Export as Excel Spreadsheet with Formatting

A spreadsheet is often a good choice for export. The columns make it easy to see the data laid out like it showed in an Access datasheet.

1. Again, select the table Staff in the Navigation Pane.
   

2. From the External Data ribbon tab in the Export tab group (NOT the Import and Link tab group!),
   click the button Excel.
   The Export -Excel Spreadsheet dialog appears.

3. Verify that the path for the exported file is to your Class disk in the databases project3 folder, the file name is Staff.xlsx, and the file format is Excel Workbook.
    
4. Check the boxes "Export data with formatting and layout." and "Open the destination file after the export operation is complete."
   
   Know which software version will be used:
   Versions of Excel before 2007 will not be able to open a file in the format you just chose unless the computer has a special viewer installed. Some features of the new format will not work in older programs.  Those are really old programs now, but there are many old programs still in use. When in doubt, ASK the users!
   
5. Click on the OK button.
   Again you are offered the opportunity to save the export steps.
6. Click on OK to close the export dialog.
   This time no wizard is needed. Access quietly creates the file and returns you to the Access window.
   The file automatically opens in Excel (assuming you have Excel installed!). If Excel was already open, the window does NOT pop up on top. You will have to click on it in the Task Bar to see the results.
7. Scroll the spreadsheet horizontally and inspect the data.
   The columns are all the same size but all data seems to have exported nicely!
8. Close Excel.
   

---

Export to Access Database

Sometimes it is convenient to export to another Access database instead of importing from that other database. It's easy to do from either direction.

1. Again, select the table Staff in the Navigation Pane.
   

2. From the External Data ribbon tab in the Export tab group (NOT the Import and Link tab group!),
   click the button Access.
   The Export - Access dialog appears.

3. Click the Browse button and navigate, if necessary, to your Class disk to the databases3 folder.
4. Select Projects2-Lastname-Firstname.accdb
5. Click on Save.
6. Click on OK in the Export -Access Database dialog.
   The Export dialog appears.
   
7. Change the table name to Staff-exported.
   There is already a table in the Projects database with the name Staff.
8. Verify that " Definition and Data" is selected.
9. Click on OK.
   The Export - Access Database appears to offer you the chance to save these steps.
10. Click on Close to close the Export dialog.
11. Open Projects2-Lastname-Firstname.accdb and verify that the new table is there.
12. Open the table Staff-exported and verify that the data was exported correctly to the correct fields and no data was lost.
    Success!
13. Close the Projects database.