{"id":1260,"date":"2021-02-28T16:39:13","date_gmt":"2021-02-28T08:39:13","guid":{"rendered":"https:\/\/www.yohz.com\/blogs\/?p=1260"},"modified":"2021-02-28T16:46:33","modified_gmt":"2021-02-28T08:46:33","slug":"importing-data-in-easy-excel-analysis-sample-walk-through","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2021\/02\/28\/importing-data-in-easy-excel-analysis-sample-walk-through\/","title":{"rendered":"Importing data in Easy Excel Analysis – sample walk-through"},"content":{"rendered":"

In this walk-through, we will be importing data from 13 Excel files containing financial transactions.\u00a0 The end-result will allow us to compute summary values for a single grouped column e.g.<\/p>\n

\"\"<\/p>\n

and for multiple levels of grouping e.g.<\/p>\n

\"\"<\/p>\n

or<\/p>\n

\"\"<\/p>\n

Prerequisites<\/h2>\n

Install Easy Excel Analysis using the installer located here<\/a>.<\/p>\n

The source Excel files<\/h2>\n

Our source files consist of 13 Excel files, each containing data for one month, except for February which consists of 2 files.<\/p>\n

\"\"<\/p>\n

Note that the each file identifies the month the data belongs to in its naming convention e.g. OBE9940 01 2020.xlsx<\/strong> indicates that the data in this file is for January, and OBE9940 12 2020.xlsx<\/strong> is for December.<\/p>\n

Each Excel file contains 2 worksheets.\u00a0 We will be importing data from the first worksheet.<\/p>\n

\"\"<\/p>\n

Importing the data<\/h2>\n

Open Easy Excel Analysis<\/a>.\u00a0 Click on the Import Excel\/CSV file<\/strong> box.<\/p>\n

\"\"<\/p>\n

Select the first Excel file to import.\u00a0 This will be our primary source file.\u00a0 Easy Excel Analysis<\/a> opens the file and displays the first 50 rows.<\/p>\n

\"\"<\/p>\n

The list of available sheets are displayed on the left, and the first sheet is selected by default.\u00a0 Our source file contains some header details, and the actual data only starts from the 6th row.<\/p>\n

Thus, we need to set the Skip first<\/strong> value to 5.<\/p>\n

\"\"<\/p>\n

Our source file also contains a total at the end of our data block.<\/p>\n

\"\"<\/p>\n

As we do not require this row, we need to set the Skip last<\/strong> value to 1 so that Easy Excel Analysis<\/a> ignores this row.<\/p>\n

\"\"<\/p>\n

Click Next<\/strong> and ensure that the column types have been identified correctly.<\/p>\n

\"\"<\/p>\n

Click Next<\/strong>.\u00a0 Here, we can set up derived values from the date columns in our data set.\u00a0 We will skip this step.<\/p>\n

\"\"<\/p>\n

Click Next<\/strong>.\u00a0 Here, we can enter the other files we want to import.\u00a0 Click on the Add<\/strong> button.<\/p>\n

\"\"<\/p>\n

Select the files we want to import.\u00a0 You can use the CTRL and SHIFT keys when clicking on the file names to select multiple files.\u00a0 Click Open<\/strong> once you have selected the files.<\/p>\n

\"\"<\/p>\n

The selected files are listed, and the default option is to import the data from the sheet of the same name as our primary source file.<\/p>\n

\"\"<\/p>\n

Unfortunately, our source files do not have a consistent naming convention with regards to the sheet names.\u00a0 Thus, we will need to change the import option to import only from sheet 1<\/strong>.<\/p>\n

\"\"<\/p>\n

Our source files do not contain any values that identify the month each transaction.\u00a0 Thus, if we import the data now, we cannot identify which month\u00a0 the transaction belongs to.\u00a0 As this information is important to us, we need to add an additional column to our combined data.<\/p>\n

We can either open each worksheet and add a column containing the month value, or add a new column using Easy Excel Analysis.\u00a0 To do that, click on the Add<\/strong> button in the additional columns section.<\/p>\n

\"\"<\/p>\n

This brings up the Add additional column<\/strong> window.<\/p>\n

\"\"<\/p>\n

We will name this column MONTH<\/strong>.\u00a0 As we will be getting the value for this column from our file name (recall that our file names contain the numeric month value), we will specify a number<\/strong> type.\u00a0 The month value in our files names is the 2nd value, so we select the filename 2nd value<\/strong> as the source for this column.<\/p>\n

\"\"<\/p>\n

Easy Excel Analysis<\/a> recognizes spaces, dashes, and underscores as separators for values, so any of these file names in our example would provide the correct month value.<\/p>\n