Section 19.3. Import and Export Operations

19.3. Import and Export Operations

Although the clipboard cut-and-paste approach is neat, it doesn't always work out. If you need to export data to a file and you don't have the corresponding program installed on your computer (or you just don't want to bother running it), then you need a different way to transfer your information. Similarly, if you're downloading data from the Web or fetching information from a program that doesn't support Windows cut-and-paste, you need the full-fledged Access import feature.

When Microsoft designed Access 2007, they spent a fair bit of time making the import and export features clearer and more straightforward. Nowadays, you can do all the importing and exporting you want from a single ribbon tab, which is named External Data (Figure 19-3).

Figure 19-3. The External Data tab's Import section lets you pipe data into Access using a variety of formats. The Export section does the reverseit takes your table, and exports it in a bunch of different flavors.

Note: The Import and Export sections have easy-to-access buttons for the most popular file formats. If you don't see what you want, then click the More button to see an expanded list of choices.

Whether you're importing or exporting data, the process is essentially the same. You answer a few questions about what file you want to use and how you want to make the conversion, and then Access does your bidding.

Once you finish performing an import or export operation, Access gives you the option of saving all your steps. If you do, you can reuse them later on (see Section 19.3.7). This method's a great way to save time if you need to perform the same export or import process again (like if you need to import some data every day, or export a summary at the end of every month).

19.3.1. Importable File Types

Most of the time, you'll import data that's in one of these five common formats:

  • Access . When you use this option, you aren't performing a conversion. Instead, you're taking a database object from another Access database file, and copying it into the current database. You used this option in Chapter 18 (Section 18.2.4) when building a front-end database.

  • Excel . Pulls the data from an Excel spreadsheet.

  • SharePoint List . Pulls the data from a list that's hosted on a SharePoint server. You don't need to import SharePoint information in order to work with it. Youcan also edit SharePoint lists directly in Access. Chapter 21 has much more about getting Access and SharePoint to work together.

  • Text File . Pulls the data out of a plain text file. Typically, plain text files use some sort of character (like a comma) to separate field values. This universally understood format's supported by many programs, including just about every piece of spreadsheet software ever written. When using this option, Access takes a look at the text file as it tries to figure out how it's organized. However, you get the chance to confirm or correct the hunch before you import any data, as described in Section 19.3.4.

  • XML File . Pulls the data out of a structured XML file. XML is a cross-platform format used to represent any type of information. However, you can't successfully import all XML filesfor the import feature to have any chance of success, the XML file must use a table-like structure. You'll learn more about this option in Section 19.4.6.

Using the More button, you'll find several other, more exotic import choices:

  • ODBC Database . Grabs information from just about any database product, provided it has an ODBC driver. This option works particularly well if you need to get data out of a high-end server-side database like Oracle, SQL Server, or MySQL.

  • HTML Document . Extracts information from a list or a table in an HTML Web page. Since HTML's a standard that's notoriously loose (and at times down-right sloppy ), you should try to avoid this option. You're likely to have importing problems.

  • Outlook Folder . Pulls information out of a folder in Outlook or Outlook Express.

  • dBase File, Paradox File, and Lotus 1-2-3 File . Pulls information out of a file created with one of these Paleolithic programs.

19.3.2. Importing Data

No matter what type of data you want to import, you'll go through the same basic steps. Here's an overview:

  1. In the ribbon's External Data Import section, click the button that corresponds to the type of file you want to import .

    When you choose a format, Access launches the Import wizard (Figure 19-4).

  2. Enter the name of the file you want to import .

    If you don't remember the file path (or you just don't want to type it in by hand), then click Browse, and then navigate to the right place in the File Open window. Once you find the file, double-click it.

    Figure 19-4. No matter what format you choose, the Import wizard's more or less the same, although certain options may be restricted. In this first step, you choose the source file name, and the way Access inserts the information into your database.

  3. Choose where to place the imported content in your database .

    You have three possible choices for placing your data. Depending on the file format you're using, all these may not be available.

    • Create a new table . This option creates a fresh new table for the data you're importing, which saves you the headache of worrying about conflicting records. However, if a table of the same name already exists in the Access database, then this option wipes it out.

    • Append to an existing table . This option takes the rows you're importing and adds them to an existing table. In order for this option to work, the structure of the data you're importing must match the structure of the table you're using. For example, the field names much match exactly. However, the data you're importing can leave out fields that aren't required (Section 4.1.1) or have default values (Section 4.1.2).

    • Create a linked table . If you use this approach, then Access doesn't actually transfer the information into your database. Instead, every time you view the linked table, Access checks the original file to get the most recent information. The neat thing here's that your linked table always shows the most recent information. With any other option, the imported table's left untouched if you change the original file. However, linked tables are also risky, because you don't have any guarantee that the file won't travel to another location on your hard drive (where Access can't find it). You used linked tables to create a split database in Chapter 18.

    Note: Linked tables are a good way to bridge the gap between different Access databases or other databases (like SQL Server). However, they don't work well with other more limited formats, like text files.
  4. Click OK .

    A wizard launches that collects the remaining information that Access needs. If you're importing an Excel file, then Access asks you which worksheet to use. If you're importing a text file, then Access asks you how the fields are separated.

  5. Answer all questions in the wizard to tell Access what it needs to know about the structure of the data you're importing .

    Once you're finished with this stage, Access asks you its final questionwhether or not you want to save your import steps.

  6. If you want to perform this import again later on, then select "Save import steps". Then, click Close .

    Section 19.3.7 shows how to reuse a saved import.

Note: If Access finds any errors while importing your data, then it creates another table with the same name as the table you're importing to, with_ ImportErrors tacked on the end. Access adds one record to that table for each problem. If you try to import a bunch of information into a table named SalesData, and Access can't convert the values to the data type you want (for example, there's text in a column that should only hold numbers ), you get a table named SalesData_ImportErrors.

The following sections walk you through the specifics for two common data formats that need a few extra steps: Excel workbooks and text files.

19.3.3. Importing from an Excel File

In order to import from an Excel file, your data should be organized in a basic table. Ideally, you have column headings that match the fields in your database. You should trim out any data that you don't want to import (like other cells under the table that aren't a part of the table). You should also remove values calculated using Excel formulas. (As you learned in Section 2.5.5, you shouldn't store calculated values in a table, because they introduce the risk of inconsistent data.)

Note: Earlier in this chapter, you learned how to take Excel data, and cut and paste your way to an Access table. However, when you perform a full-fledged import, you get the opportunity to change field names, fine-tune data types, and use indexing.

Once you have a cleaned-up table of data in an Excel file, you're ready to start the import process:

  1. Choose External Data Import Excel, choose your Excel file, and then specify how you want to add the imported information to your database. Then, click OK .

    You learned how to make these decisions in steps 1 to 3 in Section 19.3.2.

    The Danger of Duplicates

    If your import's adding ( otherwise known as appending ) records to an existing table, then you're in danger of every importer's worst nightmare: duplication .

    Quite simply, Access has no way of telling whether or not it's already imported the same information. If you've set Access to automatically fill in an autonumbered ID value for each record, then it cheerily adds the same data several times, with a different ID value each time. On the other hand, if you aren't using autonumbered ID values and the data you're importing contains the primary key, then Access can't import the new data at all. Obviously, neither outcome's ideal.

    If you're in the import business for the long term , then the only solution's to be very careful. Here are some tips:

    • If you want to reuse a file after you've imported the data it contains, then make sure you delete all the information you've already imported right away.

    • If you suspect you might have imported the same information twice, then use a query to check. You can create your own, or you can use the Find Duplicates query that the Query wizard creates (Section 6.2.2).

    • Perform small updates frequently, rather than less frequent large updates. That way, you'll catch mistakes faster, and have an easier time tracking them down.

    • If you really need a more robust solution, then you need to build it yourself. You can use Visual Basic code to control exactly how Access transfers data (which is a lot more work).

  2. Choose the worksheet that houses your data (Figure 19-5) .

    Excel files, or workbooks , begin with three worksheets. Most people plop their data on the first one, which is initially named Sheet1. If you're an Excel expert, then you might have designated a section of a more complex worksheet as a named range . If so, you can pick that named range from the list.

  3. Click Next .

  4. If your Excel data has a row with column headings, then choose First Row Contains Column Headings .

    These headings become the starting point for your field names. If you don't choose First Row Contains Column Headings, then Excel treats the first row as an ordinary record.

  5. Click Next .

    If you're creating a new table for your imported records, then Access asks you to configure the fields you're creating. If you're appending the records to an existing table, then skip ahead to step 7.

  6. For each field, you can choose a field name, the data type, and whether or not the field should be indexed (Section 4.1.3). Then, click Next .

    Access makes some intelligent guesses based on the data that's there, but it's up to you to fine-tune the details. For example, if you have a column with whole numbers, you may want to change the data type from Double (which supports fractional numbers) to Integer, as shown in Figure 19-6.

    Figure 19-5. This Excel workbook file has the standard three worksheets: Sheet1, Sheet2, and Sheet3. When you make a selection, you see a preview of the data.

    Figure 19-6. To configure a field, select it in the preview, and then adjust the settings. If you decide you don't want to import a field at all, then you can choose "Do not import field" (circled) to ignore it altogether.

  7. Choose whether you want Access to create the primary key .

    Choose "Let Access add primary key" if you'd like Access to create an autonumbered ID field (which is generally a good idea). If the data you're importing already includes a field you want to use as a key, then select "Choose my own primary key", and then pick the right field.

  8. In the Import to Table text box, type the name of the table you want to create or add your records to .

  9. Click Finish to finalize your choices .

    Once the import's complete, you can choose whether or not to save your import steps for reuse.

You'll find some potential stumbling blocks when importing data from Excel. Blank values and fields, the commonest problems, occur when the Import wizard assumes there's data in a part of your worksheet that doesn't contain any information. (This could happen if there's a cell with just a space somewhere on your worksheet, or even if you have a cell that used to contain data but has since been deleted.) After you perform your import, you may need to clean up your table to fix problems like these by deleting empty fields and records.

19.3.4. Importing from a Text File

Text files are the lowest common denominator for data exchange. If you're using a program that creates files Access can't import, then plain text may be your only avenue.

Once again, you start by choosing your file, and then choosing how you want to add the information to your database. Then, the Import wizard takes you through a few more steps:

  1. Specify the type of text file .

    Access can import from two types of text files:

    • Delimited text files use some sort of separator to indicate where each field ends. For example, Joe,Piscapone,43 is a line of text you may find in a delimited text fileit's three field values separated by commas.

    • Fixed-width text files separates a record into separate fields by position. Each field has a certain number of characters allocated to it, and if you don't use them all up, then Access fills the remaining space (up until the next field) with space characters .

    Note: Delimited text files are commoner and more flexible than fixed-width text files (because they can accommodate data values of vastly different lengths).
  2. Click Next .

    If you're importing delimited text, Access asks you what character's the delimiter in other words, what character separates the fields (Figure 19-7). Commas and tabs are common delimiters.

    If you're importing fixed-width text, Access lets you set the field boundaries by dragging column lines to the right position in the preview window.

    Figure 19-7. In this example, fields are separated using tabs.

  3. Complete the wizard .

    The rest of the wizard unfolds in exactly the same way as it does for Excel data.

    If you're creating a new table to hold your imported data, then the next step asks you to configure the fields you want to create by setting their names, data types, and indexing options (Figure 19-6). Once you've finished this part, you can choose whether or not you want Access to create an autonumbered ID field, and then use it as the primary key.

    Finally, in the last step, you need to enter the name of the table you want to create or add to. You can then click Finish (and, optionally , choose to save your import steps for later reuse).

19.3.5. Exportable File Types

Just as you can import information from other files and pop it in your database, you can also take the existing information and ship it out to another format. You'll most often undertake this step to let some other person or program get their hands on your information without needing to go through Access.

When exporting your data, you can use all the same formats that you can use in an import operation, plus a few more. Here's a rundown of the most popular choices:

  • Access . Transfers the Access table (or a different type of object) to another Access database file. This feature isn't as powerful as importing Access objects, because you're limited to one object at a time. For that reason, people don't use it as often.

  • Excel . Puts the data into the cells of an Excel worksheet. Perfect if you want to use Excel's tools to analyze a sales trend or plot a profit chart.

  • Word . Puts the data into a Word document, separating each column with tabs and each line with a hard return. This format leaves a lot to be desired, because it's difficult to rearrange the data after the fact in Word. (A nicer export feature would put the report data into a Word table, which would make it far easier to work with.)

  • PDF or XPS . Creates a print-ready PDF file with the exact formatting and layout you'd see if you sent the table to your printer. Unlike Excel or Word documents, you can't edit a PDF fileyou're limited to reviewing the report and printing it out.

    Note: The PDF or XPS option appears only if you've installed a free add-in for Office. Section 10.2.3 describes how to get it.
  • HTML Document . Creates a web-ready HTML Web page that you can post to a Web site or a company intranet. The HTML format that Access generates looks remarkably like your real, printed report.

  • Text File . Dumps the data into a plain text file, with tabs and spaces used to arrange the data. You lose colors, fonts, borders, and other formatting details. This format isn't very usefulthink of it as a last resort to transfer data to another program if none of the other export options work.

  • XML File . Saves the data in a text .xml file, without any formatting. This option makes sense if you're using some sort of automated program that can read the exported XML file and process the data. (See Section 19.4.3 for more information about XML and detailed export steps.)

19.3.6. Exporting Data

To perform an export operation, follow these steps:

  1. In the navigation pane, select the table you want to export .

    Unfortunately, you can't export more than one table at once. However, you can export just a portion of a table. One way to do this partial export is to open the table, and then select the rows you want to export. (Once you start the export process, you see an option that lets you export just the selected rows.) You can also create a query that gets just the rows you want. You can export the query results by selecting the query in the navigation pane instead of the underlying table.

  2. Click the button that corresponds to the type of file you want to export .

    When you choose a format, Access launches the Export wizard (Figure 19-8).

    Figure 19-8. The Export wizard varies depending on the export format you're using. But the first step's always to pick your file, and then set the export options shown here.

  3. Enter the name of the file you want to create .

    Access creates this file during the export operation. In some cases, you may have a choice of file format. For example, if you're exporting to Excel you can use the newer XML-based spreadsheet format (the .xlsx standard), or the older .xls standard that supports older versions, like Excel 97.

  4. If you want to keep the formatting that's in your database, then choose "Export data with formatting and layout" .

    If you've tailored the datasheet with fancy fonts and colors (as described in Section 3.1), Access preserves these details in the exported file. Obviously, this option doesn't work for all formats. For example, simple text files can't handle any formatting.

  5. If you want to double-check your exported document, then choose "Open the destination file after the export operation is complete" .

    It's always a good idea to make sure you got the data and the formatting you expect. If you use this option, then Access launches the exported file, opening it in the program that owns it (Excel for spreadsheets, Notepad for text files, and so on). Of course, this method works only if you have that application on your computer.

  6. If you've selected only a few records in a table, then choose "Export only the selected records" .

    This way, Access exports the current selection, not the entire table or query.

  7. Click OK to perform the export .

    Access may ask you for additional details, if it needs any more information about how to create the exported file.

    Once you're finished this stage, Access asks you its final questionwhether or not you want to save your export steps.

  8. If you want to perform this import again later on, select "Save export steps". Then, click Close .

    The following section explains how to use a saved export.

Exporting Reports

Tables and queries aren't the only database objects you can export. Access also lets you export your reports. If you choose to keep the formatting and layout, then Access tries to make sure the exported file looks just like the printed report.

This choice is great if you want to pass along a report to someone who doesn't have Access. If you simply want to share the report data, then you can use Word. If you want to preserve the formatting exactly so that it can be printed later on, then the PDF format makes more sense. Section 10.2.2 discusses how to export a report in detail.

Access also lets you export a form, but you probably won't get the results you want. Access uses the formatting and layout from the Datasheet view. Most forms use a carefully laid set of controls in Form view, and rarely use the Datasheet view. However, when Access exports a form, it ignores the Form view altogether.

19.3.7. Reusing Import and Export Settings

In some situations, you'll find you need to perform regular import or export operations. You may need to dump the data from an Excel spreadsheet into your database once a week. Or maybe you need to produce a monthly PDF report with a sales summary. In these cases, it's quite time-consuming to go through the entire wizard. This is especially true if you're performing an import, because you might need to choose which columns you want to import, set the appropriate data types, and then adjust other settings the exact same way you did the first time you performed the operation.

Fortunately, Access has a solution for times like these. You can save all the settings you chose in the Import or Export wizard and store them in your current database. Then, when you need to repeat the process, you can use these settings to do it with just a couple of clicks (with no brainpower needed).

To save your steps, just turn on the "Save import steps" or "Save export steps" checkbox at the end of the process the first time you import or export your data. You'll need to choose a descriptive name for your settings, as shown in Figure 19-9, and then click Save Import.

Tip: If you're saving an import operation, think carefully about whether you choose to create a new table or append to an existing table (Section 19.3.3). If you create a new table, then every time you run the import, Access overwrites that table with a new table that has all new data. But if you append to an existing table, Access adds the new data to whatever data you've already got. (In this case, you need to be on the lookout for duplicate data; see the "The Danger of Duplicates" box in Section 19.3.3.)

Figure 19-9. Here, an import process is being saved for later use. You can fill in an optional description for this operation to help you remember what it's all about. And if you're using the popular Microsoft Outlook email program, then you can choose Create Outlook Task to create an automatic reminder that tells you when it's time to perform your import or export.

At some future point, you can rerun your import or export operation. If you want to repeat an import, then choose External Data Import Saved Imports. To repeat an export, choose External Data Import Saved Exports. Either way, you get to the Manage Data Tasks dialog box (Figure 19-10), at either the Saved Imports or Saved Exports tab. These tabs lists the import and export operations youve saved for this database.

Here's what you can do in the Manage Data Tasks dialog box:

  • Run the operation again . Select it in the list, and then click Run. Access warns you if it needs to overwrite an existing table (in an import) or file (in an export). Other than that, the whole process happens in a flash.

  • Delete your saved operation . Just select it, and then click Delete.

  • Create an Outlook Task for the operation . You can use this feature to remind yourself to perform this operation at some future scheduled time (or at regular intervals). To do so, click Create Outlook Task to create the task, and then find and configure that task in Outlook. When the reminder occurs, it includes a handy Run Import button that you can click to launch the import operation in Access right away.

    Figure 19-10. In this example, a single import operation has been saved. By clicking on the file name, you can change it to another file, but you'll have to type in the new path by hand.

  • Change some aspects of your operation . You can modify the name, the description, and the file name by clicking the appropriate detail in the Manage Data Tasks dialog box (Figure 19-10). This way, you can start out importing c:\My Documents\FancyFiles\WildExpenses.xlsx , but then use the same settings to import d:\HankSmith\EvenMoreExpenses.xlsx . You can't change any other details, like the source or destination table in Access, or the field data types.

When you're finished using the Manage Data Tasks dialog box, click Close to get back to Access.

Access 2007[c] The Missing Manual
Access 2007[c] The Missing Manual
ISBN: 596527608
Year: 2007
Pages: 153 © 2008-2017.
If you may any questions please contact us: