Desktop Client: How do I ensure my external Excel data is read correctly by a simulation Generator?

This article applies to iGrafx Process and iGrafx Process for Six Sigma Client tools

You can configure your Generator(s) for a simulation model in iGrafx to read data from an Excel spreadsheet, using the External Data tab of the Generators dialog box. You may need to ensure you have 32-bit Microsoft Office data access drivers installed if you are using 64-bit Office; see the article on Desktop Client: Requirements for importing data from Microsoft Excel (Generator won't read 64-bit Office Excel) xlsx files if needed.

There may be cases, however, where the data read in from Excel does not match your expectations based on how the data visually looks in Excel. In addition, how the data is treated is NOT affected by how you format the type of data in Excel; e.g. as a number or as text.

The following are the broad classes of issues you may run into due to Excel behavior and how iGrafx must programmatically read data using the Excel Driver.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

Data Type Errors

You may receive a runtime error indicating a string was found where a number was expected, or vice-versa. For example, you may receive an error such as "String where a numeric value was expected" or "Numeric argument to function expecting a string value".

Excel has the concept of a "column of numbers" vs. a "column of text" when you are accessing a spreadsheet. It considers a column that has more numbers than strings (other than the in the first row, which is taken to be the column header) to be a column of numbers, else it considers it to be a column of text. This determination is NOT affected by how you choose to format a column, or even a given cell.

 The issue is how iGrafx is able to read values from Excel programmatically using the Excel Driver. Excel will attempt to 'guess' whether the values in a column of data are strings or numbers based on a set of rules setup by Microsoft. The following article outlines the behavior of Excel; in particular, you may want to pay attention to the 'rows to scan' description:

http://msdn.microsoft.com/en-us/library/ms714545(VS.85).aspx

A valid number (or numerical string) is a string of digits which may contain special characters if used correctly to format the value. The valid special characters are: decimal symbol, digit grouping character, leading plus/minus sign, leading currency symbol, and the character for scientific (exponential) format. For example, "729", "3,271.65", "-752", "$143.64", and "1.425E02" are all valid numerical strings.

A text string is a string of legal alphanumeric characters that is not a valid numerical string.

If you want to ensure a string is read in, you can force a value to be read in as a text string from Excel if it is prefixed with one or two single quotation characters; i.e. using the ' character. This is an Excel convention. The first single quotation character is for Excel to take the remaining characters of this string as a text string. This indicates that the numerical string will NOT be converted to a numerical value.

The second single quotation character, if it exists, forces keeping the remaining characters of this string as a text string. Without the second single quotation character, iGrafx will try to match the text string with defined Type members. If it is matched with a type member, the text string is then converted to the enumerated value. Otherwise, it takes the string as a text string. For example, [''True] is read in as a text string [True] and not the type member True, which is changed to the enumerated numerical value of 1, as it is second member of the Type that has members {False,True} and iGrafx starts indexing of types at zero (0).

Do not mix numerical and text strings within a column of a data sheet. When the simulator opens the spreadsheet, the Excel API determines upfront if a column consists entirely of numbers, or entirely of strings. If all of the values of a column are formatted as valid numbers as interpreted by the rules above, nothing special needs to be done. If, however, a value that should be interpreted as a string (should NOT be interpreted as a number) contains any of the digits from 0-9, for example, a part number of the form 123ABC or a Type member of the form Type1, then single quotation characters should be used as described above to guarantee that the values are correctly interpreted.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

Blank Lines

When opening a spreadsheet in Excel, it shows blank rows at the bottom of the data sheet. In some cases, those rows are read in and treated as regular data. The simulator does not know which row in this data sheet is the last data row, and the simulator cannot assume a blank row is the sign of the end of data. This problem is not likely if the data in the spreadsheet are manually typed in.

To verify that no blank row is brought in as regular data, scroll down in the data grid displayed in the Generators dialog (be sure to click Reread Now button to refresh the grid) and check if there is any unwanted blank row. This will only work if your data is 500 rows or less, as only the first 500 rows display in the Generators dialog box (though more rows will be read and used). If there are unwanted blank rows at the bottom of this data sheet, you can delete them in Excel as follows:

1. Close the Generators dialog.
2. Open the data sheet in Excel.
3. Select the row under the last row of desired data, by clicking its row number.
4. Scroll to the end of the sheet, by dragging the vertical slider bar to the bottom of the scroll bar window.
5. Hold down the shift key while selecting the last row showing in the window. This will select all the rows from the first selection to the row you selected at the bottom of the window.
6. From the Edit menu, choose Delete.
7. Save and close the Excel file. (Do not leave the file open in Excel; there may be problems with connecting to an external data file while it is still open in Excel.)
8. Open Generators dialog and re-assign (and re-read) this data sheet to the generator. Now, the grid will end at the last data row.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

Non-Equal Amounts of Data (Columns with Different Numbers of Rows)

iGrafx assumes that all columns of data in a spreadsheet are of the same length; that they all have the same number of rows. Again, iGrafx cannot assume a blank value is the end of the data, and cannot create data in blank rows.

If you have columns of data that have different numbers of rows of data, then you may want to use one of the following techniques to ensure the proper data is read in:

1. Delete the longer columns of data to have the same number of rows of as the shortest column.
2. Stop reading data based on the shortest column (the one with the least number of rows). This may be possible by setting the “Max Transactions” on the Setup tab of the generator to correspond to the maximum number of rows to be read.
3. Separate the rows into different worksheets, to ensure that all rows in a given worksheet are of the same length.