IMPORT MULTIEXCEL command

Concept Information

Import Microsoft Excel data

Creates multiple Analytics tables by defining and importing multiple Microsoft Excel worksheets or named ranges.

Syntax

IMPORT MULTIEXCEL <TO import_folder> FROM {source_filename|source_folder} TABLE input_worksheets_or_named_ranges <PREFIX> <KEEPTITLE> <CHARMAX max_field_length>

Note

You must specify the IMPORT MULTIEXCEL parameters in exactly the same order as above, and in the table below.

Analytics cannot import from an Excel workbook if Protected View is active for the workbook. You must first enable editing in the workbook, save and close the workbook, and then perform the import.

Parameters

Name Description
TO import_folder

optional

The folder to import the data into.

To specify the folder, use an absolute file path, or a file path relative to the folder containing the Analytics project. Specify import_folder as a quoted string.

Example

TO "C:\Point of sale audit\Data\Transaction working data"
TO "Data\Transaction working data"

If you omit TO, the data is imported to the folder containing the Analytics project.

FROM source_filename | source_folder

The name of the source data file or files, or the folder containing the source data file or files.

Specify source_filename or source_folder as a quoted string.

Source data file or files in the root Analytics project folder

  • single Excel file

    Specify the compete file name and extension.

    Example

    FROM "Transactions_FY18.xlsx"
  • multiple Excel files

    To specify multiple files, use a wildcard character (*) in place of unique characters in file names. The wildcard character stands for zero (0) or more occurrences of any letter, number, or special character.

    Example

    FROM "Transactions_FY*.xlsx"

    selects:

    Transactions_FY18.xlsx

    Transactions_FY17.xlsx

    You can use a wildcard in more than one location in a file name, and in a file extension.

    Example

    FROM "Transactions_FY*.*"

    selects:

    Transactions_FY18.xlsx

    Transactions_FY17.xls

Source data file or files not in the root Analytics project folder

If the source data file or files are not located in the same folder as the Analytics project, you must use an absolute file path, or a file path relative to the folder containing the project, to specify the file location.

Example

FROM "C:\Point of sale audit\Data\Transaction master files\Transactions_FY18.xlsx"
FROM "Data\Transaction master files\Transactions_FY*.xlsx"

Folder containing source data file or files

Instead of specifying a file name, you can just specify the name of the folder containing a source data file or files.

To specify a source data folder, use an absolute file path, or a file path relative to the folder containing the Analytics project.

Example

FROM "C:\Point of sale audit\Data\Transaction master files"
FROM "Data\Transaction master files"

Note

When you specify a folder, any worksheet in any Excel file in the folder is imported if the worksheet name matches the TABLE value.
TABLE input_worksheets_or_named_ranges

The name of the worksheets or named ranges to import. A separate Analytics table is created for each imported worksheet or named range.

Specify input_worksheets_or_named_ranges as a quoted string.

Use a wildcard (*) in place of unique characters in the names of worksheets or ranges.

For example, "Trans_*$" selects the following worksheets:

  • Trans_Jan
  • Trans_Feb
  • Trans_Mar
  • and so on

Note

The wildcard character (*) stands for zero (0) or more occurrences of any letter, number, or special character.

You can use a wildcard in more than one location. For example, *Trans*$ selects:

  • Trans_Jan
  • Jan_Trans

The meaning of the dollar sign ($)

In an Excel file, worksheets are identified with a dollar sign ($) appended to the worksheet name (Trans_Jan$). The dollar sign is not visible in Excel.

Named ranges are identified by the absence of a dollar sign (Trans_Jan_commercial).

Specifying the dollar sign is not required when using IMPORT MULTIEXCEL. However, you should include it, or exclude it, in the following situations:

  • Include "$" if you want to import only worksheets, and no named ranges, include the dollar sign at the end of the worksheet name
  • Exclude "$" if you want to import named ranges, or worksheets and named ranges in a single import operation, do not include the dollar sign

PREFIX

optional

Prepend the Excel file name to the name of the Analytics tables.

Tip

If worksheets in different files have the same name, prepending the Excel file name allows you to avoid table name conflicts.
KEEPTITLE

optional

Treat the first row of data as field names instead of data. If omitted, generic field names are used.

Note

All first rows in the worksheets and named ranges that you import should use a consistent approach. First rows should be either field names, or data, across all data sets. Avoid mixing the two approaches in a single import operation.

If the data sets have an inconsistent approach to first rows, use two separate import operations.

CHARMAX max_field_length

optional

The maximum length in characters for any field in an Analytics table that originates as character data in a source data file.

Examples

The examples below assume that you have monthly transaction data for three years stored in three Excel files:

  • Transactions_FY18.xlsx
  • Transactions_FY17.xlsx
  • Transactions_FY16.xlsx

Each Excel file has 12 worksheets – one for each month of the year. The worksheets also include some named ranges identifying various subsets of transactions.

Note

A separate Analytics table is created for each worksheet or named range that you import.

Import worksheets

Import all FY18 worksheets

You want to import all 12 monthly worksheets from the FY18 Excel file, and ignore any named ranges.

  • you use the wildcard symbol (*) where the month occurs in each worksheet name
  • you include the dollar sign ($) at the end of the worksheet name so that only worksheets are selected, and no named ranges
IMPORT MULTIEXCEL FROM "Transactions_FY18.xlsx" TABLE "Trans_*$"

Import all FY18 worksheets, keep field names, and specify maximum character field length

This example is the same as the one above, but you want to keep the field names from the Excel files, and also limit the length of character fields.

  • you include KEEPTITLE to use the first row of Excel data as the field names

  • you include CHARMAX 50 so that fields that originate as character data in the Excel file are limited to 50 characters in the resulting Analytics table

IMPORT MULTIEXCEL FROM "Transactions_FY18.xlsx" TABLE "Trans_*$" KEEPTITLE CHARMAX 50

Import all worksheets from all three files

You want to import all 36 monthly worksheets from the three Excel files, and ignore any named ranges.

  • you use the wildcard symbol (*) where the month occurs in each worksheet name
  • you include the dollar sign ($) at the end of the worksheet name so that only worksheets are selected, and no named ranges
  • you use the wildcard symbol (*) where the year occurs in each Excel file name
  • as a way of reducing the chance of naming conflicts, you use PREFIX to prepend the name of the source Excel file to each Analytics table name
IMPORT MULTIEXCEL FROM "Transactions_FY*.xlsx" TABLE "Trans_*$" PREFIX

Import named ranges

Import all FY18 "Commercial_transaction" named ranges

You want to import all "Commercial_transaction" named ranges from the FY18 Excel file, and ignore worksheets, and other named ranges.

  • you use the wildcard symbol (*) where a unique identifier occurs in the names of the different ranges
  • you exclude the dollar sign ($) so that named ranges can be selected
IMPORT MULTIEXCEL FROM "Transactions_FY18.xlsx" TABLE "Commercial_transaction_*"

Import worksheets and named ranges

Import all FY18 worksheets and named ranges

You want to import all 12 monthly worksheets, and all named ranges, from the FY18 Excel file.

  • with TABLE, you use only the wildcard symbol (*) so that all worksheets and named ranges in the file are selected
  • you exclude the dollar sign ($) so that named ranges can be selected
IMPORT MULTIEXCEL FROM "Transactions_FY18.xlsx" TABLE "*"

Manage directories

Import all worksheets from all Excel files in the specified folder

You want to import all worksheets from all Excel files in the C:\Point of sale audit\Data\Transaction master files folder.

  • with TABLE, you use only the wildcard symbol (*) so that all worksheets in each file are selected, and the dollar sign ($) so that only worksheets are selected, and no named ranges
  • as a way of reducing the chance of naming conflicts, you use PREFIX to prepend the name of the source Excel file to each Analytics table name
IMPORT MULTIEXCEL FROM "C:\Point of sale audit\Data\Transaction master files" TABLE "*$" PREFIX

Import all worksheets from all Excel files in the specified folder, and save the Analytics tables to another folder

This example is the same as the one above, but instead of saving the Analytics tables in the root project folder, you want to save them in the C:\Point of sale audit\Data\Transaction working data folder.

IMPORT MULTIEXCEL TO "C:\Point of sale audit\Data\Transaction working data" FROM "C:\Point of sale audit\Data\Transaction master files" TABLE "*$" PREFIX

Remarks

Multiple IMPORT EXCEL commands

The IMPORT MULTIEXCEL command actually performs multiple individual IMPORT EXCEL commands – one for each worksheet imported. If you double-click the IMPORT MULTIEXCEL entry in the log, the individual IMPORT EXCEL commands are displayed in the display area.

Last table imported is automatically opened

IMPORT MULTIEXCEL does not support the OPEN keyword. However, after the command executes, the last table imported is automatically opened.

Combining multiple worksheets after importing them

After you import multiple worksheets into individual Analytics tables you might want to combine them into a single Analytics table. For example, you could combine the data from twelve monthly tables into a single annual table containing all the data.

For information about combining multiple Analytics tables, see APPEND command.