Help Manual

Contents






Sigma Magic Help Version 17

Data

Overview

The Data button can manage the data for your analysis from one central location. Instead of maintaining separate copies of data, you can define the data for your project once and then reuse that data in different tools. This button can be found within the Setup set of menus on the main menu bar. main menu

Usage

When you click the Data button, the software will create a new worksheet called the Data Sheet to store your data for analysis. However, if the datasheet has already been made, it will be the currently active worksheet. To open this sheet's dialog box, click the Analysis Setup button. An example screenshot of the dialog box is shown below. main menu
1
Available Data: The list of data available for your analysis is listed in this table. The table shows the column number, the name of the table, the name of the variable, the type of data, and the number of rows of data. If no data is available for analysis, this table will summarize how the data can be loaded for your analysis.
2
Search Data: If you have a large number of columns of data listed in your table it may be difficult to find the data you are looking for. In these cases, you can enter a search term in this box, and only those columns of data that meet the search criteria are listed in this table. Clear the search field to clear the search and view all the available data.
3
Import Tables: If your data is available in a table in another workbook, you can click on this button to import the table to your current workbook. Note that data needs to be in tables and can be located on any worksheet of the external workbook. When you import the data to your current workbook, data is added to the Data Sheet.
4
Import Range: If your data is available in a range either in the current workbook or another workbook, you can import the data from this range and convert it into a table in your current workbook. When you import the data to your current workbook, data is added to the Data Sheet.
5
Export to File: You can export data from your data file to an external workbook. Select the columns of data you would like to export before clicking on this button. If you select multiple columns of data to export to your worksheet, they will be placed in contiguous columns, so ensure there is sufficient space on your worksheet to save your data. Note that Export Data will not remove the data from your data file but will make a copy of the data and paste it into your workbook.
6
Export to Range: You can export data from your list of available data to a range. Select the columns of data you would like to export before clicking on this button. The data you selected will be posted as a range, and you will need to select the top left cell where you would like to paste the table data. Make sure there is sufficient space (rows and columns) for empty cells before you paste the data. Note that Export Data will not remove the data from your data file but will make a copy of the data and paste it into your specified location.
7
Rename Columns: You can use this button to rename the table column or the name of the column that is stored in your database. You can rename tables and variable names using direct Excel functionality, which will break any associated analysis you may have performed with this data. It would be best to use this button to rename your variables so that any prior analysis that used them is updated. Ensure your table names are unique across all the table names within a given data file. The table and column names should also not contain any special characters; otherwise, the software may reject renaming your columns. Note that you can simultaneously use the rename button to change the name for up to 10 variables.
8
Delete Columns: You can use this button to delete any data columns that you no longer need from your data file. Note that this operation cannot be reversed, so make sure that you are careful when you use this button to delete any columns you no longer need.
9
Data Cleanup: You can use this button to clean up any problems with your data set. First, select the columns you want to clean up and then click this button. Data Cleanup can be used to remove spaces at the start or end of a text string, handle capitalization issues (where the variables Dog and dog would be treated as the same variable), remove non-printable characters from the data set, and remove values that are errors from the data set.
10
List/Table View : You can click on this radio button to switch between the list view of the data and a table view of the data. The list view provides a summary of the data available, including the table's name, the column's name, and the number of rows of data. The table view provides a summary of the table name, the name of the column, and a sample of the first few data points, along with the number of rows of data. The default is the list view.
11
Help Button: Click on this button to open the help file for this topic.
11a
Refresh Button: Click on this button to refresh the contents of the table if any changes were made to the worksheet after the Data Editor was opened.
12
Cancel Button: If you want to cancel your changes, you can click on the Cancel button and the software will exit this dialog box. Note that changes may be temporarily saved in memory, so you must quit Excel without changing your workbook if you want to discard your changes.
13
OK Button: Clicking on the OK button will save your workbook changes and then exit this dialog box.
Below is an example of the datasheet stored within your workbook. When you do not use an external data file and save the data to your current workbook, Sigma Magic will create an Excel table and store the results in that table. It also determines how many rows of data exist in the column you have copied. main menu
1
Table 1: Note that this workbook has two separate tables. Table 1 and Table 2. Each table can have any number of columns. In this example, Table 1 has four columns, and Table 2 has two columns. Note that there is a one-column gap between tables.
2
Table 2: This is the second table available in this workbook. Note that each table has the same number of rows for all columns. If you have a different number of rows, you may copy your data to different tables or leave the rows blank in a particular column if there are fewer rows.

Importing Data

Excel has a lot of powerful capabilities for importing data into your workbook. You can use these features to import data for your analysis. Click the Data button to import data on the main menu bar. An example screenshot is shown below. data menu
1
Data: Click on the Data button on the main menu bar to open the Excel data import functionality.
2
Get Data: Next, click on the Get Data dropdown menu to specify how you want to import the data to your workbook. Your data may be in several different locations. A few of the common options are shown below.
OptionDescription
Excel WorkbookClick on Get Data > From File > From Excel Workbook. If your data is stored in an external Excel workbook. Excel will establish a connection to that workbook and display the list of available data in this workbook to import. You can scroll through different sheets and tables in this workbook and select the data you want to import.
Text/CSV FileClick on Get Data > From File > From Text/CSV. If your data is stored in an external text/CSV file. Excel will establish a connection to that file and display the list of available data in this file to import. Like the earlier option, you can select the data you want to import.
PDF FileClick on Get Data > From File > From PDF. If your data is stored in an external PDF file. Excel will establish a connection to that file and display the list of available data in this file to import. Like the earlier option, you can select the data you want to import.
DatabasesClick on Get Data > From Database > From SQL. If your data is stored in an external database like a SQL server. Excel will establish a connection to that server and display the list of available data in this file to import. Like the earlier option, you can select the data you want to import.
Table/RangeClick on Get Data > From Other Sources > From Table/Range. If your data is stored in an Excel table or a range. Select the table or range you want, then click the Get Data button. Excel will establish a connection to that table/range and display the list of available data in this file to import.
WebsiteClick on Get Data > From Other Sources > From Web. If your data is stored on an external website. Excel will establish a connection to that web server and display the list of available data in that location. Like the earlier option, you can select the data you want to import.
Transform Data
1
Sheets: Select the sheets where your data is located. This option is displayed when importing data from an external Excel workbook.
2a
Load Data: Click on the Load Data button to load the entire data to your workbook. Use this option if you have clean data.
2b
Transform Data: Click on the Transform Data button to open the Power Query editor to manipulate the data before loading this into your workbook. Use this option if you have to clean your data before import.
The screenshot below shows the Power Query data editor option within Microsoft Excel. Loaded Data
1
Remove Columns: Use the functionality provided in the top menu bar to clean up your data. For example, if you have unneeded columns in your data file, you can use this to delete those columns.
2a
Close & Load: Click on the Close & Load Data button to load the cleaned-up data to your Excel workbook. This will add the data table to a new worksheet in your current workbook.
2b
Close & Load To: Click on the Close & Load To Data button to load the cleaned-up data to a specific location in your Excel workbook. If you don't see this option, click the dropdown button next to Close & Load to see this option in the main menu bar. For example, you can pick the Data Sheet worksheet and specify where you want the table copied. This will add the data table to an existing worksheet in your current workbook.
Once you import the data to your workbook, you can use it for any analysis.

FAQ

This section contains some frequently asked questions. If you have other questions about this module, please get in touch with technical support.
What happens if I import the data into my worksheet and the user changes the data in the original location?
When you import your data into the current workbook, it will copy and paste it into the current workbook. Note that it does not link to the original data, so if the original data is changed at the source location, these will not be reflected in the data within your workbook. You will need to reimport the data to reflect any changes.
How do I change the location of where the database is stored?
By default, any data you import into the current workbook will be stored as tables within the Data Sheet worksheet. Note that the data must not be stored only in this worksheet. Data tables may be stored in any worksheet in your current workbook.
How do I edit the data that has been previously copied?
You can edit the data that you have copied to your workbook. Note that the analysis you may have performed with your older data set is not automatically updated. If you have updated the data in your workbook, you must click on Compute Outputs to update the analysis results. Any changes to the data are reflected in the Summary Sheet, which is an excellent source to determine if all your analyses are using the latest version of the data.