Excel Spreadsheet

 

 

 

Excel Spreadsheet

 

The Excel Spreadsheet source connection will allow a user to select a specific range or worksheet within an Excel workbook as the source of Vendor Line data.  The connection uses the Microsoft Jet Engine to load and process the document.  As a result, the document cannot 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 Vendor 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.

General Tab

Vendor Code:  The vendor code for the vendor.  This is a macro-enabled field.

Vendor Name:  The name of the vendor.  This is a macro-enabled field.

Description:  The description of the vendor.  This is a macro-enabled field.

Active:  The active status of the vendor, typically a true/false or boolean (1/0) value.  This is a macro-enabled field.

Status:  The actual status of the vendor.  This is a macro-enabled field.

Details Tab

Site/Property:  The site or property of the vendor.  This is a macro-enabled field.

Email Address:  The email address of the vendor.  This is a macro-enabled field.

Web Site URL:  The website of the vendor.  This is a macro-enabled field.

Currency:  The currency of the vendor.  This is a macro-enabled field.

Payment Terms:  The payment terms of the vendor.  This is a macro-enabled field.

Tax ID:  The tax ID of the vendor.  This is a macro-enabled field.

Address Tab

 

The Address tab will allow you to specify multiple addresses for the vendor.  Drag and drop a column name representing an address line 1 into the grid.  You will manually need to enter Line 2, Line 3, City, Region/State, Postal Code, and Country.

 

Phone Tab

 

The Phone tab will allow you to specify multiple phone numbers for the vendor.  Drag and column name representing a phone number into the grid.  You will manually need to enter the extension.

 

Attributes Tab

 

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.

 


Copyright © 2023 pasUNITY, Inc.

 

Send comments on this topic.