How to import SAP R/3 data into Microsoft Access
After the XLS file resides on your local system or an accessible file share, you need to import this file into Microsoft Access (the following steps assume Access has been installed on your system; given that Access is not included with all versions of Microsoft Office, this might not be the case by default):
- Launch Microsoft Access on your system.
- From this initial window, select the Blank Database option, and then click OK. You are prompted to create a name and to select a location for your database. In this example, I selected the C:\My Documents directory and named the database MySAP.mdb
- Click the Create button; you then see the main Microsoft Access window
- To bring the SAP data into Microsoft Access, use the Microsoft Access menu path File, Get External Data, Import. You are then prompted with a window similar to the one shown in Figure 22.10. This is where you have to input the location and filename of the output file you saved earlier. By default, the Files of Type box lists Microsoft Access (*.mdb). You have to change this to Microsoft Excel (*.xls).
- After changing the Files of Type box and selecting your file, click Import. Just as in the Microsoft Excel import, in Access you are presented with an Import Spreadsheet Wizard
- On the first screen of the Import Spreadsheet Wizard, click the Next button to continue. On the second screen, it asks whether you want to create a new table or add the data to an existing table. To create a new Access database table containing your SAP data, click Next. The next window, gives you an opportunity to name each of your fields.
- By selecting each column (use your mouse to do so), you can type a field name for each. After you have named all your fields, click Next.
- The following screen enables you to assign a unique identifying number for each of your records, to be used as a primary key (primary keys are discussed in Hour 3, “Database Basics”). Click the Next button to continue.
- The last screen asks you to provide a name for your table. Type MySAP and click Finish. Microsoft Access then presents you with a confirmation window similar to that shown in Figure 22.13.
- Click OK in the final Import Spreadsheet Wizard confirmation window; you are returned to the Microsoft Access main window, and your new table is now listed under the Table tab.
- To take a look at your table, select it and then click the Open button. Your SAP list now appears as a Microsoft Access table; it includes an additional primary key field as well.
This process is certainly a few steps longer than exporting your SAP data into Microsoft Excel. However, Microsoft Access is a sound reporting tool used by a large number of SAP customers as their primary reporting toolespecially when other applications or tools like SAP Business Warehouse, Strategic Enterprise Management, Cognos, or Crystal Reports are unavailable. Its prevalence also speaks to the fact that Microsoft Access is popular across many companies regardless of size.
Popularity: 18% [?]
