Import and Export data

Analytica User GuideIntegration with data and applicationsImport and Export data


Import into an Edit Table or List from a text file

To import a definition from a text file into an edit table or list:

  1. Select the definition field of the variable in either the Object window or Attribute panel definition view. If variable is a table, open the edit table.
  2. If you are importing a simple tab-delimited file, then select the cell(s) in which to import. If you are importing a data file in the format described in Model file formats, then the cell selection is not used.
  3. Select Import from the File menu. A dialog prompts you for the file name from which to import.
18.7.png

To import all the elements of a multidimensional table (including all the slices), a special text format is required (see Model file formats). This is also the format in which an edit table or result table is exported. The indexes of the table must have been previously created as nodes.

Export a result table to a text file

To export a variable’s result table to a text file, first be certain that the text file is closed.

  1. Select the variable to be exported from and open its Result window.
  2. Select Export from the File menu. A dialog prompts you for the file name to export to.

Printing to a File

Another way of exporting any Diagram window, Object window, or Result window to a file is to print to a file:

  1. Select Print from the File menu.
  2. Select Print to File and press Enter or click OK.
  3. Enter the name of the file and the format for the file in the dialog that appears.
18.8.png

Edit Table Data Import/Export Format

Multidimensional data being imported or copied into an edit table must be in a text file with the special format described in this section. This is also the format in which an edit table or result table is exported.

  • TextTable is a keyword.
  • View is the name of the view from which the data originated. If your data was exported from an edit table, this is EditTable. If it was exported from a result table, it will be Value, Mean, Statistics, Probbands, PDF, CDF or Sample.
  • Variable identifier is the identifier of the variable node where the data originated.
  • Index identifier is the identifier of the index for this variable. An index with this name must already exist in the model, with the same length as in the file. When the index is a domain index, the <Index Identifier> is of the form Domain of <identifier>.
  • Each index value and array value pair must be separated by tab characters.
    • Numeric values appear directly, in any of Analytica’s standard number or date/time formats.
    • Text values are enclosed in either double (") or single (') quotes. Tab and newline characters cannot appear between the quotes. If the text between the quotes includes a tab or newline character, it must be escaped using one of the sequences: \n, \r, \t. If a backslash or quote character appears in the text, it too must be escaped using \\, \'or\". (When the text is enclosed in double quotes, single quotes don’t have to be escaped, and vice versa).
    • Expressions (from edit table cells), which include distribution functions that might appear in edit table cells, appear without quotes as long as they single-line expressions (i.e., do not contain any newline characters), do not start and end with the same quote character (such as the expression "a" & "b"), and do not contain any tab character. Otherwise, the entire expression is enclosed between tildes, and the escape sequences \n, \r, \t, \\, \~ are used for newline, tab, backslash or tilde characters. For example, the following multi-line expression:
var s:= "~~”;
JoinText(A, I, s) & '\'
would appear as ~var s:= "\~\~”;\rJoinText(A, I, s) & '\\'~.
  • The value null may appear as Null.

One-dimensional array

The format for a one-dimensional array is: 

TextTable <View> <Variable identifier> <line break>
<Index identifier><line break>
<Index value><tab><Array value><line break>

Example

18.9.png

Two-dimensional array

The format for a two-dimensional array is: 

TextTable <View><Variable identifier><line break>
<Index1 identifier><tab><Index1 values separated by tabs>
<line break>
<Index2 identifier><line break>
<Index2 value1><tab><Array values separated by tabs><line break>
<Index2 value2><tab><Array values separated by tabs><line break>
<Index2 valueN><tab><Array values separated by tabs><line break>

Example

18.10.png

Three-dimensional array

The format for a three-dimensional array is: 

TextTable <View> <Variable identifier> <line break>
<Index1 identifier><tab><Index1 Value1><line break>
<Index2 identifier><tab><Index2 values separated by tabs><line break>
<Index3 identifier><line break>
<Index3 value1><tab><Array values separated by tabs><line break>
<Index3 value2><tab><Array values separated by tabs><line break>
<Index3 valueN><tab><Array values separated by tabs><line break>
<Index1 identifier><tab><Index1 Value2><line break>
<Index2 identifier><tab><Index2 values separated by tabs><line break>
<Index3 identifier><line break>
<Index3 value1><tab><Array values separated by tabs><line break>
<Index3 value2><tab><Array values separated by tabs><line break>
<Index3 valueN><tab><Array values separated by tabs><line break>

And so on for each value of Index1.

Example

18.11.png

Number format

Numerical data can be imported in any format recognized by Analytica (see Number formats).

Numerical data is exported in the format set for the table, with these exceptions:

  • Suffix format numbers are exported in scientific exponential format.
  • Fixed decimal point numbers of more than 9 digits are exported in scientific exponential format.
  • If a date format begins with the day of the week, e.g., “Saturday, January 1, 2000”, the weekday is suppressed: “January 1, 2000”.

Import a CSV data file

The Import... option on the File menu makes it easy to import a CSV (comma-separated values) data file . It automatically creates a new variable containing the table, along with new Column and Row indexes:

  1. Make sure you are in Edit mode with a Diagram window active.
  2. Select Import... option from the File menu.
  3. Use the file browser to find and select the .csv file you want to import and click the Open button.

It automatically creates a new variable node with title copied from the file name. The definition is an Edit table containing the values from the file with Index variables Row and Column. It creates new Index variables: Row with row numbers, and Column with column headers if the CSV's top row has column headers, otherwise column numbers. It reuses an existing index, instead of creating a new one, if one already exists with the expected values.

It automatically imports numbers as numbers, dates as dates, empty cells as Nulls, and treats anything else as a text value.

Convert relational table to array with a wizard

Data tables imported from CSV files, spreadsheets, and databases are usually in relational form -- where some rows identifies index values as well as one or more numerical values (such as revenue). In Analytica it's often useful to convert these relational tables into multidimensional arrays with Analytica index variables for each index column and each cell sums (or aggregates another way) the value column(s). You can use MDArray function for this conversion. But, it's usually easiest to use a conversion wizard.

  1. Show the result table view of the relational table window and select a body cell.
  2. From the right-click menu select Relational table to array...
  3. It shows the conversion wizard dialog. Here's how to use it:

For each source column in the relational table, it shows a Treat as menu and automatically guesses its role. Sometimes you will want to override its guess. These are the options:

  • New index: Make a new index variable whose values are the unique values from that column in numerical or alphabetic order.
  • Existing index: Use an existing index variable if it finds one that already contains the expected unique values. It shows a blue underlined link to that index if you want to review it.
  • Local index: Make a new local index whose values are the unique values from the column. This is often best unless you expect to use the index extensively in the model.
  • Value: Treat this column as containing numerical values to aggregate over (e.g. sum) in the resulting array. It assumes Value for columns containing numbers. You need to specify at least one column as a Value index.
  • Ignore: Don't use this column in the result.

The Type menu lets you specify how to aggregate over the Value index(es). The usual default method is Sum, with other options shown here. If there is no Value column, the aggregation type must be Count -- i.e. the resulting array contains a count of the number of rows whose index column values match the corresponding indexes of the result.

If you treat more than one column as a Value", it assumes that the input is a "fact table". It then lets you define a Value index with a menu with options New index, Local index, Local index, or No value index. It offers a field to specify the identifier of the new value index if you want one.




See Also


Read data from URL on internetImport and Export dataOLE linking
Comments


You are not allowed to post comments.