Excel Spreadsheet

 

 

 

Excel Spreadsheet

 

The Excel Spreadsheet source connection will all a user to select a specific range or worksheet within an Excel workbook as the source of General Line data.  The connection uses the Microsoft Jet Engine to load and process the document.  As a result, the document can not be open by the user with write permissions when the application attempts to open the document.  An button is provided on the first page to open the workbook in read-only mode.

 

Data Region Page

Data Region Name: On this page, you must select the worksheet or range from which the source data will be imported.  Expanding the drop down box will query the pages on the Excel workbook and list them for selection.  As explained on the page, the data region name can include a specific range within the worksheet.

oSheet1$: reads all data from the range of defined cells in the worksheet.

oRegionName:  Reads all data from the workbook level named range "RegionName"

oSheet1$RegionName:  Reads all data from the worksheet level named range "RegionName"

oSheet1$B5:G10:  Reads data from Sheet1 in the range B5 through G10.

First Row Has Column Names:  Selecting this option will tell pasTransfer whether column names are included within the first row of the specified data region.  If column names exist, they will be used for mapping macros on the next page.

Preview:  This will display the region specified in Data Region Name as a table to verify the correct range has been selected.  It will not display all rows, but only a sample size.

Open In Excel: Open the selected Excel workbook in read-only mode.  No changes can be made to the document, but the workbook can remain open while selecting a data region and macros.

Data Record Column Macros Page

 

The Data Record Column Macros Page will display the columns within the specified data region and provide a means of mapping columns to a General Line.  Clicking and dragging an item from the list of columns to a macro-enabled text box or grid will create a macro to reference the column.

Preview:  This will display the region specified in Data Region Name as a table to verify the correct range has been selected.  It will not display all rows, but only a sample size.

Reset:  Clears the contents of all controls which contain column macros.

Attributes Grid

 

Any values from the source file can be made into attributes and mapped later on the destination transformation.  Click and drag a column to the attribute data grid.  The default name of the attribute will be the name of the column.  To delete a row, select the row-header and press the delete button. At least one attribute is required.

 


Copyright © 2023 pasUNITY, Inc.

 

Send comments on this topic.