In this task, we will open a set of data in an Excel file. This can be either in .xls or .xlsx format. We will set options to help with reading the data in the Excel file as the correct type of data.
Preparation for this task is very simple. We only need a set of data saved in an Excel workbook. Any worksheet is suitable, but be aware that the Open Worksheet… command will open every worksheet in an Excel workbook at the same time. The formatting options that we will use here are applied across the entire workbook and cannot have separate format options for every worksheet. Minitab worksheets have a maximum limit of 4000 columns and a practical limit of 10 million rows.
We follow an example here using the Pulse workbook.xlx worksheet.
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
The following instructions detail the steps for importing data from Excel by using the open worksheet command:
Within Minitab, go to the File menu and click on Open Worksheet.
Change the Files of type field to Excel, and navigate to the folder containing the Excel file.
Select the Excel file by clicking on the workbook.
Click on Preview to see the file structure, make a note of the row in which column names appear, and the data appears. In the following example, the column names are in the third row and the data starts from the fourth row.
Click on OK and then select Options.
Select Variable Names to indicate the row of the column names. In this example we will use row 3. The first row of data can be set to row 4. The automatic setting will pick the next row with any data in it for the first row of data. Click on OK.
Click on Preview. We can check if this has helped with identifying the type of data. Further alterations to data type can be made. If further alterations need to be made, either change the data type from the drop-down list under each column name or return to Options to see what further changes need to be made.
Click on Open.
The Preview screen will display the first 100 rows in the dataset. This can be a useful tool in seeing how the file is going to be opened and then deciding what needs to be changed in options.
Excel files can come in many different formats, and while options cannot correct everything, it is an important first step.
If a dataset contains summarized data rows such as means or standard deviations at the end of the worksheet, it is best to exclude them. This can be performed by limiting the number of rows that Minitab will open.
Another option that is useful is to ignore blank data rows. Any row that is completely empty will be left out as it is unnecessary to include them in a Minitab worksheet.
Text files, CSV files, XML files, and more can be opened using the Open Worksheet option. While opening text files, column separators can be identified by using the field definition.