Completing Connection Properties for Excel Spreadsheets

Complete these settings when the source business data is in a well-formed Excel spreadsheet.

As well as making these settings in the Business Adapter Studio, consider the registry settings documented below.

The following properties can be set when importing from an Excel spreadsheet.

Property	Notes
File name	The full path to the Excel file to import. Tip: Click the ellipsis button (...) at the right of the text box to use Windows Explorer to locate the file.
Worksheet	The specific worksheet to import from within the spreadsheet. Only one worksheet can be imported using each adapter.
Auto-generate SQL Query	Selecting this check box causes Business Adapter Studio to automatically generate the SQL query for the worksheet (recommended). Alternatively, you may clear this check box and manually specify the query.
Query	The query to run against the Excel spreadsheet and extract data from the chosen worksheet.
Read “Intermixed” data columns as text	Selecting this check box causes the OLE-DB driver to resolve ambiguous columns as text. The driver uses the first several rows (default 8) to determine the data type of each column, and favors numeric when confused. A numeric setting causes the import to fail for any records containing text in such a column. Clear this check box to rely on the OLE-DB driver to determine the column type.
First row contains column names	Select this check box if the first row in the spreadsheet is a header row with the names of the columns, rather than a data row. Conversely, clear the check box if the first row contains values that should be imported.

You may also want to consider adjusting the following registry entries on the computer where the business adapter runs:

In disconnected mode, on the inventory beacon
In connected mode, possibly on the application server.

These registry entries are found under the following registry key:

For 32-bit operating systems:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]

For 64-bit operating systems:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel]

Entry Notes

Entry	Notes
`TypeGuessRow`	The format for each column is automatically assigned, based on a sampling of 8 rows. This may generate issues in some scenarios. For instance, if the 8 first rows of specific column include only numeric values, the column will be considered as numeric when string values may exist in other rows below. Depending on the scenario, this may cause import errors or values may be discarded. One way to solve the problem is to change the number of rows considered by Excel. To modify this behavior, adjust the entry `[TypeGuessRow]`. This value defines the number of rows to read to determine the format of a column. A value of zero indicates the full Excel worksheet will be read; this value may impact performances.
`ImportMixedType`	Depending on the quality of the data and different scenarios that may occur, there may be mixed data types in the same column (for instance numeric and string). In this case, data should always be considered as a string. To ensure this occurs, set the `[ImportMixedType]` entry to `[Text]`. Make sure that you also select the Read “Intermixed” data columns as text field on the Properties page for the business adapter, as described above.

TypeGuessRow

The format for each column is automatically assigned, based on a sampling of 8 rows. This may generate issues in some scenarios. For instance, if the 8 first rows of specific column include only numeric values, the column will be considered as numeric when string values may exist in other rows below.

Depending on the scenario, this may cause import errors or values may be discarded. One way to solve the problem is to change the number of rows considered by Excel.

To modify this behavior, adjust the entry [TypeGuessRow].

This value defines the number of rows to read to determine the format of a column. A value of zero indicates the full Excel worksheet will be read; this value may impact performances.

ImportMixedType

Depending on the quality of the data and different scenarios that may occur, there may be mixed data types in the same column (for instance numeric and string). In this case, data should always be considered as a string. To ensure this occurs, set the [ImportMixedType] entry to [Text].

Make sure that you also select the Read “Intermixed” data columns as text field on the Properties page for the business adapter, as described above.

Tip: If your Excel file to import includes multiple worksheets, the Business Adapter Studio needs further assistance with your import. You can take either of the following approaches:

Make a copy of the Excel file and remove all the worksheets except the one you wish to import.
Leave your spreadsheet unchanged; and modify the Business Adapter Studio configuration to control how this import is processed.

To make this configuration change:

In the Business Adapter Studio, from the Tools menu, choose Options.
In the Options dialog, change the Show advanced options setting to Yes, and click OK.
In your adapter definition, set the option to use physical databases to true, and specify a name for your database staging table.

This adds the following two attributes to your adapter definition in the XML file:

 <Import
        Name="FromMultiWorksheets"
        ...
        UsePhysicalTables="True"
        DataTableName="MyTableName"
        ...
/>

FlexNet Manager Suite (On-Premises)

2024 R1