Creating Excel Import and Export Files

In order to import data from an Excel file into a model, you need to set up the Excel file so that the data is properly formatted. To export data from a model to an Excel file, you can use a blank Excel file. The export process will automatically overwrite any information in the worksheet and set it up in the correct format.

Notes: To import data from an Excel file, you must save the Excel file. Unsaved changes in an Excel file will not be imported.

If you are sharing a model that has a persistent import or export link to an Excel file, create a folder called "Data" within the folder that contains the model file. This ensures that the connection between the model and the Excel file stays active, regardless of the computer that you are running the model on.

The import file format and export file format are described in detail below.

Import file format

To import values from an Excel file into a model, the Excel file must contain the names of the variables in the model into which you want to import values, and the values that you want to import.

The variable names in the Excel file must exactly match the variable names used in the model. You do not need to include all model variables in your Excel file; include only the variables whose values you want to import to the model. Model variables that do not appear in the Excel file are not affected during the import.

Note: If any of the variables in the Excel file do not match those in the model, you will receive a warning message during the import to tell you what data could not be imported.

You can enter the variable names as column headings or row headings (you will indicate the format you choose when you set up the link to your model). For example, the following Excel worksheet defines the initial values for population (a stock), birth rate (a converter), and death rate (a graphical function), with the variable names in the column headings:

The name of each variable appears as a column heading and the values to import for each variable appear immediately beneath it. All values for the graphical function (death rate), ordered from minimum x to maximum x, appear under the variable heading. If there are not enough values for the graphical function, the last value will be repeated to fill it out. If there are too many values, the excess values will be ignored.

The following example shows the same data with the variables as row headings:

Note that the data in the spreadsheet do not have to start at row 1, column A. The import process searches the first 20 rows and 10 columns (200 cells) for a variable name, so you can begin entering data anywhere within that area. This allows you to enter header data or other documentation in the file that will not affect the import process. You can also enter descriptive information between columns (if you are using a column format) or between rows (if you are using a row format).

Between each variable, you can have up to five columns (or rows, if you are using row format) that have a blank header line. Data can appear in any other row (or column, in row format) than the header row and these columns (or rows) will be ignored during the import.

Import file format for arrays

One-dimensional arrays. To import values in an Excel file into a one-dimensional array in a model, the Excel file must contain one column (or row) of data for the array. The column (or row) heading specifies the array name. All subsequent columns (or rows) contain the data.

The following example shows a one-dimensional array ("OneD array") set up with a column heading, and with seven specified values.

Two-dimensional arrays. To import values in an Excel file into a two-dimensional array in a model, the Excel file must contain more than one column (or row) of data for the array. The first column (or row) specifies the array name. All subsequent columns or rows for the array contain an ellipsis ("...").

In the following example, there are three variables for the array "TwoD array", set up with column headings. For each variable, the Excel spreadsheet provides three values for each variable.

If there are not enough values for the array, the last value will be repeated to fill it out. If there are too many values, the excess values will be ignored.

Note: For two-dimensional arrays, the data in Excel rows always map to rows in the model array, and the data in Excel columns always map to columns in the model array, regardless of whether there are row headings or column headings in the Excel file. The following example shows the same data as in the "TwoD array" example above, with row headings rather than column headings. Notice that the data is in the same order in both examples.


One-dimensional graphical functions. Format the data in the same way as you would for a two-dimensional array, with an ellipsis ("...") in subsequent rows or columns. For one-dimensional graphical functions, however, your specification of row vs. column headings determines how the values are read into the graphical function.

The following example shows how you would format the Excel file for a one-dimensional graphical function (called "OneD gf array") with four elements (in column headings).

If there are not enough values for the elements, the last value will be repeated to fill it out. If there are too many values, the excess values will be ignored.

Two-dimensional graphical functions. To import values in an Excel file into a two-dimensional graphical function, format the values in row-major order (values are imported from the first row, moving from left to right, then from the second row, left to right, and so on). Succeeding columns in a row start with an ellipsis ("..."). To indicate the start of a new row, use "***" in the first column of the row.

The following example shows the format for a 4x3 array (called "TwoD gf array") of graphical functions (1 column for each cell in row-major order).

Import file format for conveyors and queues

To import values in an Excel file into a conveyor or queue, enter all values for the conveyor or queue as a comma-separated list in a single cell of the Excel file. For conveyors, you specify one value per unit time in the conveyor. For queues, you specify one value per element in the queue.

The following example shows the format for specifying three values to import for a conveyor.

Export file format

If the One set of values option is selected as the interval for the export link, data exported from the model to the Excel file appears in the same format as described for the import file. This allows you to use the exported values as import values at another time. If one of the other interval options is selected for the export link, data exported from the model matches is in the same format as table output. If you are using the One set of values option with a table, do not include more than one element from each array as every occurrence of an array element will cause the entire array to be exported.

As with the import file, you decide whether you want the variables to be listed as column headings or as row headings when you set up the export link.

When you export data, the contents of the Excel worksheet are always erased before the export starts. Any text or formatting specified in the worksheet is lost when the export happens.

Related Topics