SAP Lumira, discovery edition can acquire data from one or multiple Microsoft Excel files.
Supported versions are Microsoft Excel 2007 (.xls file extension), 2010, 2013, and 2016 (.xslx file extension). SAP Lumira, discovery edition can retrieve data from one sheet or all sheets of a spreadsheet. Also, the range of rows and columns that need to be imported can be defined.
If you want to acquire data from multiple files, then the rows from all files are appended in the same dataset. A new column named Source file is added to the dataset, which states the title of the source file for each record. The first file that you add is used as a reference template for the column definitions. This means that the header is taken from this file if you use the Set first row as column names option.
It is important that the other files have the same setup as the initial file. If another file has fewer columns, or the data types of its columns do not match the ones from the initial file, SAP Lumira, discovery edition will not allow the file to be imported. In case another file has more columns, the extra columns are not added to the dataset.
To create a new dataset from one or multiple Microsoft Excel files, follow these steps:
- Select Microsoft Excel from the Data Source menu on the home page of SAP Lumira, discovery edition. If you want to add a data source to an SAP Lumira document, use the New Dataset option in the Data menu.
- Select your Microsoft Excel file or files in the Open dialog.
- Click Open to close the dialog window. A new popup window is shown. Here we see a preview of the dataset and further information and settings for this acquisition (see the figure below). If you added multiple files, the additional Source file column is added at the end. The Sheet dropdown menu contains only sheets from the initial Microsoft Excel file.
- Change the Dataset Name.
- To add more files, use the Add Files button to open the Open dialog window again and browse for additional files. To remove files, you can delete the file location in the Files box or click the Clear Data button to remove all files.
- If your Microsoft Excel file contains multiple sheets, select the correct sheet in the Sheet dropdown menu.
- Select the Append all sheets option to include all the data from all the sheets. An additional column Source sheet is added to the dataset.
- Select Set first row as column names to use the values that are in the first row as the labels for each column in SAP Lumira, discovery edition. If you disable this option, the column names are displayed as Column_<n>, where <n> is the number of the column.
- Depending on the setup of the data in the spreadsheet, select the correct Table Header Type. In most cases, a Standard Table (No Transformations) is used. In such a table, all the values are organized in rows, as shown in the left table of the figure below. The right table of the figure below demonstrates how a cross table uses headers in both the columns and rows to organize the values.
- Click Show Record Count to display the total number of columns and records in the dataset.
- Deselect the columns that you don’t want to import. You can check Use Select All to include all the columns again.
- Below the preview table, click Advanced Options. Here you can enable or disable the retrieval of data from hidden columns and rows (Show Hidden Columns or Show Hidden Rows).
- Select Detect Merged Cells. With this option, all cells of a merged cell are filled with the value of the merged cell. If you don’t check this option, only the first cell is filled with the value.
- In case you selected Standard Table (No Transformations) as the Table Header Type, you can alter the Range Selection to a Custom Range. Enter the coordinates of the top-left and bottom-right cells that contain the data that needs to be acquired (for example, B2:K76) and click Apply. In addition, you can change the number of rows that need to be used as the header.
- If you choose Cross Table as the Table Header Type, you can change the number of Columns and Rows that must be used as headers (see the figure below).
- Click Visualize to finish the data acquisition and import the data into your SAP Lumira document.
Microsoft Excel spreadsheets are wildly popular, and this won’t change for a long time. These spreadsheets are a simple, convenient way to store, present, and analyze data, as there is no need to set up a complicated database infrastructure to create and use them.
Of course, problems arise as soon as spreadsheets need to be up-scaled, to include more and more data, and to be used by more than a single user. For those running SAP Lumira, discovery edition can import Excel data in order to make their stories better. This blog post showed you how.
Editor’s note: This post has been adapted from a section of the book SAP Lumira, Discovery Edition: The Comprehensive Guide by Xavier Hacking and Martijn van Foeken.