Category Archives: Easy Excel Analysis

Importing from multiple CSV files in Easy Excel Analysis

To import data from multiple CSV files in Easy Excel Analysis, first set up the import settings for a single file as described here.

NOTE: The layout and format of the data in all the additional files must be identical to that of the initial file. 

When you reach this page of the import wizard, you can then set up the additional files to import from.

To add files, click on the Add button below the file list window.

If you need to be able to identify each data source and your worksheets do not contain any columns that identifies the source data, you can add additional columns on this page to help identify the data source.  Click on the Add button below the additional columns list.

On this page, enter the column name, type, length, and source of the column.  There are several options available to use the filename or worksheet details.

Using an example file name of G:\data\Sales_Osaka_July2020.xlsx,

  • filename = Sales_Osaka_July2020
  • filename 1st value = Sales
  • filename 2nd value = Osaka
  • filename 3rd value = July2020

Note that the separators can be a dash symbol (e.g. Sales-Osaka-July2020.xlsx), a comma (e.g. Sales,Osaka,July2020.xlsx), or a dot (e.g. Sales.Osaka.July2020.xlsx).  You cannot mix separator symbols e.g. Sales-Osaka.July2020.xlsx.

For further details, please refer to this topic in the help file.

Importing multiple worksheets and/or Excel files in Easy Excel Analysis

To import data from multiple worksheets and/or Excel files in Easy Excel Analysis, first set up the import settings for a single worksheet as described here.

NOTE: The layout and format of the data in all the additional files and worksheets must be identical to that of the source worksheet. 

When you reach this page of the import wizard, you can then set up the additional import sources.

To add a file, click on the Add button below the file list window.  For each additional file that you want to import, select the option that best fits your needs.

If you want to import data from all the worksheets in a single file, first set up the import settings for any one of the worksheets in that file.  Then on this page, add that file again and select the Import from all worksheets option.  Easy Excel Analysis will not import from the same worksheet twice.

If you need to be able to identify each data source and your worksheets do not contain any columns that identifies the source data, you can add additional columns on this page to help identify the data source.  Click on the Add button below the additional columns list.

On this page, enter the column name, type, length, and source of the column.  There are several options available to use the filename or worksheet details.

Using an example file name of G:\data\Sales_Osaka_July2020.xlsx,

  • filename = Sales_Osaka_July2020
  • filename 1st value = Sales
  • filename 2nd value = Osaka
  • filename 3rd value = July2020

The same extraction rules apply to worksheet names.  Note that the separators can be a dash symbol (e.g. Sales-Osaka-July2020.xlsx), a comma (e.g. Sales,Osaka,July2020.xlsx), or a dot (e.g. Sales.Osaka.July2020.xlsx).  You cannot mix separator symbols e.g. Sales-Osaka.July2020.xlsx.

For further details, please refer to this topic in the help file.

Easy Excel Analysis Guide

The Easy Excel Analysis Getting Started Guide

getting started
Explains how to import an Excel or CSV file into an analysis table.

formatting date and time values in CSV files
Explains how to import date and time values from CSV files when the format differs from the local machine regional settings.

importing from multiple Excel worksheets and/or files
Explains how you can import data from multiple worksheets and/or files.

importing from multiple CSV files
Explains how you can import data from multiple CSV files.

using data files
Explains data files, and how they can be used to share data with other users without having to grant them access to your databases.

using analysis tables
Explains how to create groups, summaries, and use charts with your data sets.

working with copies of the same data sets
Explains how you can create multiple analysis and pivot tables from the same data set without having to repeatedly run the same query.

working with analysis table columns
Explains how to hide and freeze columns, and create multi-row headers in your analysis tables.

working with charts
Explains how charts can be used to get a big picture analysis of your data set, and used as a navigation tool to support the analysis table.

Getting started with Easy Excel Analysis

To create an analysis table from an Excel file using Easy Excel Analysis, here are the basic steps to follow:

Importing your Excel file

Click on the Import Excel/CSV file item, and select the Excel file you want to import.

Select the worksheet you want to import data from.

A preview of the selected worksheet is displayed on the right.  Adjust the settings accordingly if your worksheet does not have a header, or has blank rows before the data rows.  Click Next.

On the next page, adjust the column types where necessary.  Check that the date and time columns are identified correctly.  If the columns have been formatted as date and/or time columns in Excel, they will be identified correctly in Easy Excel Analysis.  Click Next when done.

On this page, you can add additional columns that extract individual elements from date and time columns.  In our example, we have chosen to add columns for the year, month, and quarter based on the Order Date column.  Click Next after you have added the elements you require.

On this page, you can choose to import data from additional Excel files and/or worksheets.  See this post for further details.

Click Next, and your Excel data will be imported into an analysis table.

Importing your CSV file

The steps to Import a CSV file is similar to importing an Excel file.  When you select a CSV file, the Import CSV File wizard displays a preview of the file.  You may need to adjust the settings to import your file correctly.  To determine if the settings are correct, click on the Preview button.

In this example, we see a strange character next to the first column description.

This is a common issue, and is fixed by selecting the Detect byte-order mark option.  Easy Excel Analysis will try to set this option automatically depending on the CSV file format.

Other common settings to change include the Field separator symbol (default is the comma symbol), and the quote symbol.

To check if the CSV file will be imported correctly, click on the Preview button to check the lines that will be imported.

The rest of the steps is identical to that for importing Excel files described above.

 

Working with copies of the same data sets in Easy Excel Analysis and SQL Data Analysis

In Easy Excel Analysis and SQL Data Analysis, you can create multiple analysis tables and/or pivot tables by clicking on the Add analysis table and Add pivot table buttons on the toolbar.

You do not need to re-import the Excel/CSV file (in Easy Excel Analysis) or re-run the query (in SQL Data Analysis).  Each analysis table and pivot table is independent, and you can customize the layout of each analysis and pivot tables to your needs.

Because each analysis/pivot table consumes memory resources, the number of tables you can create from each data set is determined by your settings.  In Easy Excel Analysis, this limit is set in the Data loading settings in the Import file wizard.

In SQL Data Analysis, the limit is set in the Data options settings.

You can increase this limit if you need to be able to create more analysis/pivot tables per data set, and your machine has the available memory capacity.

Using data files in Easy Excel Analysis and SQL Data Analysis

In Easy Excel Analysis and SQL Data Analysis, you can save data sets to an external file, which you can then use later without having to import the Excel/CSV file again (for Easy Excel Analysis) or connect to your database and running the SQL query again (for SQL Data Analysis).

Another use for data files is to be able to share data with your users, who can use Easy Excel Analysis or SQL Data Analysis to open the file.  In the case of SQL Data Analysis, you can provide them the required data to analyse without having to grant them access to the underlying database.

To save a data set to a data file, you must already be using the data set, either in an analysis table or pivot table.

Click on the Save analysis data button, and enter a file name to save the data in.

Opening a data file

To open a data file, click on the Open data file button,

or click on the Open data file item on the main page.

 

Working with analysis table columns

Once you have imported a data set into Easy Excel Analysis or SQL Data Analysis, an analysis table is created.  Here, we will described how to work with the analysis table columns.  We will be using the global superstore 2016.xlsx file for reference.

Creating summaries

To create summaries, please refer to this post on how to create group and footer summaries.

Hiding columns

To hide or display columns, open the Columns tab of the Customize panel.  Select a column to make it visible, or deselect it to hide the column.

Freezing columns

In Easy Excel Analysis or SQL Data Analysis, you can freeze columns on the left and right of the analysis table e.g. in the table below, the Order Date column is frozen on the left, and the Country column is frozen on the right.

When you scroll the table horizontally, these 2 columns will remain fixed in their position.

To freeze columns, first ensure that the LEFT BAND and/or RIGHT BAND items are selected in the Customize panel.

Then drag and drop columns onto either of those 2 items to freeze the columns in those bands.

Stacking columns

You can stack columns by dragging them to a new column row, to create layouts like this:

Notice that the Category, Sub-Category, Segment, Product ID, and Product Name columns are displayed on the second row.

To stack columns, click on a column header and drag it to below the row where you want to create a new row.  You will create a new row if you see the green horizontal indicator arrows.  In the example below, we are dragging the Category column to create a new row.

 

 

Using charts in your analysis tables

In Easy Excel Analysis and SQL Data Analysis, you can use charts to visualize the data created in analysis tables.  A pre-requisite is that you analysis tables must have groups and summary values as described here.

Each grouped column serves as a level in the chart, and each summary value is the chart’s measure.  For example, we have the following analysis table layout created based on the global superstore 2016.xlsx data.

We have 3 grouping levels, and 2 summary values.  To create a chart, click on the chart icon:

A chart is displayed on the bottom of the analysis table.

Clicking on the analysis table will update the chart accordingly, and vice versa.  For example, if I click on the Europe item in the chart:

the analysis table automatically expands the Europe grouping level, and the chart also displays the grouping level below Europe, which is the item category level.

Likewise, if we click on a grouping level within the analysis table, the chart will also drill down to the corresponding level.

 

Using analysis tables

Once you have imported a data set into Easy Excel Analysis or SQL Data Analysis, an analysis table is created.

In this example, we’ve imported the global superstore 2016.xlsx file.  What you can do now is to group the rows by one or more columns.  To add a column to the group, click on the column header and drag it to the column headers area.

Here, we have grouped our rows by Market, Category, and Sub-Category.

At this point, you can easily analyze the sales data for each level of the grouped columns.  You can add as many columns to the groups as you require, and you can also change the order of the grouped columns by arranging it in the appropriate order.

We will now create summary values for each grouping level.  To create a summary, right click on a column header in the table, and select the required summary.  In our example, we will create a group summary based on the sum of the Sales column.

Once created, the summary value is displayed on all of the grouping levels.

You can add multiple group summaries by repeating the process above.  Here, we have added another summary to display the total sales orders.

To sort the groups by the summary values, you will need to select the summary item to sort by from the Customize menu.  Here, we have chosen to sort by the Sales (sum) summary.

All grouping levels will then be sorted by that summary value in ascending order.

You can now quickly see which groups have the highest sales.  You can sort the summary values in descending order by clicking on the grouped column.  This symbol (highlighted in red below) indicates that the summary values are sorted in ascending order:

and this symbol indicates the summary values are sorted in descending order.

See also:

 

Formatting date and time values in CSV files

Identifying the columns correctly

The first step in importing a CSV file into Easy Excel Analysis is to ensure that each column is correctly identified.  In this example, we will work with the sample CSV file:

When we first open this file in Easy Excel Analysis and click on the Preview button, an error is raised regarding the number of columns.

Our CSV file encloses text in quotes, so we’ll need to select the Field values are quoted using “ option (since we’re using double quotes as the quote character), and click Preview again:

All the columns seem to be in order, so we can proceed.

Adjusting the date format

On the next page, Easy Excel Analysis has set up the date and time formats based on our computer’s settings.

The date format is different from that in our CSV file:

If we continue to the next step, our OrderDate column will be identified as a Text field, because the date format in the CSV file does not match our settings.

Thus, we’ll need to change the date format settings accordingly.  Go back to the previous step, and select the correct date format from the list of pre-defined formats, or enter the format manually.  Note that d represents the date, m for month, and yyyy for the year.

Once we have set up the date format to correctly match that in our CSV file, Easy Excel Analysis will set up the OrderDate column as a Date and time column.

If your date columns contain month names e.g.

then just enter a blank value as the date format.  Easy Excel Analysis will then try to parse the value as best as it can.

Adjusting the time format

Adjusting the time format is similar to the steps described above for date formats.  You can manually enter a format value if none of the listed formats match.