The "Excel Source" allows users to connect to an existing Excel file and extract its data.
Excel Source is available for SQL versions 2012 and higher.
• 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.
• 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.