Excel

Parent Previous Next


Excel Source


The "Excel Source" allows users to connect to an existing Excel file and extract its data.


Important Note

Excel Source is available for SQL versions 2012 and higher.


File Format



 Excel Connection Manager - Users can create a new or select an existing connection to an Excel worksheet. For more information, see the Excel Connection Manager.

 Choose Worksheet - For Excel files that use many worksheets, users can choose which one to extract data from.

 First Row Has Header Names - This option is selected if the first row contains headers. If the first row contains data, remove this option and configure the columns in the Columns tab.

 Region / Range Settings - Users can choose from the following:

o  Region - Use the Row and Column values below to define the data range.

o  Named Range - Users can select from named ranges created in an Excel file. When Named Range is selected, a dropdown box appears. Users can then choose from the available named ranges within the document.

 Rows - Determines the starting and ending row. "-1" denotes using all available rows. (Note: when the first row contains headers, it will count as the first row when starting your range at row 1.)

 Columns - Determines the number of columns in the output. "-1" denotes using all available columns.

 Output Format -

o  Formatted - Users should select this option if the Excel file's data  is formatted and you want to keep it in the output.

o  Raw Value - Ignores formatted data and the original "raw" values will be output.

 Show Preview - Selecting this option will enables and displays the preview based on the component's configuration.


Columns



 Code Page - Identifies character encoding.

 Column Name In File - Identifies the name of the column coming from the Excel file.

 DataType - All datatypes default to DT_WSTR but can be edited for output to be integers, boolean, strings, etc.

 Excel Column Index - Defines the numeric order of the column.

 Include In Output - Selected by default, users can determine whether or not the column is included in the output.

 Length - Defines the number of characters for string and wstring datatypes.

 Output Column Name - Defines the name of the column in the output. Column names can be edited here.

 Precision - Defines the number of digits for numeric datatypes.

 Scale - Defines the number of digits after the decimal for numeric datatypes.

 Refresh Excel Columns - Refreshes the column information if the Excel document is changed while the component is open.


Please see the Error Row Handling page for more information about this functionality.