Editing Table Load Options

In the course of your data prep work, you may find that the table you selected from a set of tables in a Microsoft Access file does not contain the information you need. Monarch Data Prep Studio enables you to easily change this table via its Edit Load Option functionality. This functionality allows you to:

  • Select a different table/worksheet from the source Access/Excess file 
  • Modify input options (e.g., delimiter, text qualifier, number of lines to skip) for delimited text files
  • Modify input options (e.g., Excel import connector, named ranges, number of lines to skip) for Excel worksheets
  • Modify input options (e.g., which row has column names, number of lines to skip) for HTML tables 

Note: If load options for a data source with a missing/invalid password are to be modified, the correct password must be supplied before load options can be accessed. Otherwise, load options for this data source cannot be modified. 

 

  1. In the Prepare Window, click the drop-down arrow beside the Excel, CSV, HTML, or Access table of interest and then select Edit Load Options.

     

    You can also:

  • Click Edit Load Options at the bottom of the Change History panel.

  • Click Load Options in the Column Information panel when a table is previewed.

  1. Implement the changes necessary using the following dialogs and tables as guides:

    FOR MICROSOFT ACCESS DATA SOURCES

     

    Field

    Description

    Table

    Specifies which table in the Access file to load into the workspace.

     

    FOR MICROSOFT EXCEL DATA SOURCES

     

    Field

    Description

    Sheet

    Specifies the name of the sheet to load into the workspace.

    Lines to skip

    Specifies the number of rows to skip, starting from the first line, if these lines do not contain data.

    First Row Has Column Names

    When selected, indicates that the first row of the sheet includes the column names.

    Ignore Blank Rows

    When selected, instructs the application to ignore blank rows and load the table without these rows.

    Import columns as Text

    When selected, instructs the application to import all columns with the data type text regardless of the original data type.   

    Automatically sync columns with source data on load or refresh

    When selected, instructs the application to sync the columns of the loaded table with the columns of the source table upon load or refresh.

    This functionality is useful in instances where the columns in your source table change over time. Note that when this mode is applied, your Change History items may yield an error state if the columns the sync is applied to are renamed or deleted from the source file.

    Input Connector

    Specifies the connector type (i.e., Monarch Excel Table or Monarch ACE OLE DB (Legacy) to apply when the table is loaded.   

    Scan and Define Columns

    When selected, instructs the application to return data types to their original values (as determined by the Monarch Excel Table connector).

    This option is deactivated if the input connector is Monarch ACE OLE DB (Legacy) or Automatically sync columns with source data on load or refresh is selected.

    Bulk Type Edit

    Specifies a single ("bulk") data type to apply to all fields in your data source.

    This option is deactivated if the input connector is Monarch ACE OLE DB (Legacy) or Automatically sync columns with source data on load or refresh is selected.

    NOTE: Specific data types can be assigned to each of the columns in your Excel data source using the Type drop-down lists provided to the right of each column.

     

    FOR HTML DATA SOURCES

     

    Field

    Description

    First Row Has Column Names

    When selected, indicates that the first row of the sheet includes the column names.

    Lines to skip

    Specifies the number of rows to skip, starting from the first line, if these lines do not contain data.

     

    FOR DELIMITED TEXT FILES

     

    Field

    Description

    INPUT OPTIONS

    Delimiter

    Specifies the type of delimiter used to separate fields in a delimited text file. The most common delimiters are:

    • Comma
    • Tab
    • Semicolon
    • Space
    • Pipe

    If the delimiter you are looking for is not on this list (e.g., ^), simply type it into the field.

    Lines to skip

    Specifies the number of rows to skip, starting from the first line, if these lines do not contain data.

    Text Qualifier

    Specifies the text qualifier used to mark the start and end of a column entry. The most common text qualifiers are:

    • Double quote
    • Single quote
    • None

    Rows to scan for columns

    Specify how many rows to scan in your data source before Monarch Data Prep Studio decides on a data type for the column.

    Note that setting this number to a very high value may negatively impact the application's load performance depending on the number of rows and columns in your input source.

    First Row Has Column Names

    When selected, indicates that the first row of the sheet includes the column names.

    Ignore Blank Rows

    When selected, instructs the application to ignore blank rows and load the table without these rows.

    Allow Embedded Line Breaks 

    When selected, instructs the application to preserve embedded line breaks when importing columns.   

    Automatically sync columns with source data on load or refresh

    When selected, instructs the application to sync the columns of the loaded table with the columns of the source table upon load or refresh.

    This functionality is useful in instances where the columns in your source table change over time. Note that when this mode is applied, your Change History items may yield an error state if the columns the sync is applied to are renamed or deleted from the source file.

    Encoding

    Specifies the encoding type to apply when loading the table into your workspace. The encoding types enabled for delimited-text files include:

    • Code Page
    • UTF-8
    • UTF-16LE
    • UTF-16BE  

    Code Page 

    When the Encoding type selected is Code page, this setting instructs the application to apply the selected code page to the table upon loading into your workspace.

    Bulk Type Edit

    Specifies a single ("bulk") data type to apply to all fields in your data source.

    NOTE: Specific data types can be assigned for each of the columns in your delimited text file data source using the Type drop-down lists provided to the right of each column.

    This option is deactivated if Automatically sync columns with source data on load or refresh is selected.

    CONVERSION OPTIONS

    From text to numeric

    Decimal Separator - Specifies the character to use as a decimal separator when converting text into a numeric value. You can select:

    • Period
    • Comma

    From text to date

    Date Format - Specifies the format of dates used when converting text into a date value. You can select:

    • M/D/Y
    • D/M/Y
    • Y/M/D

    First Year of Century - Specifies the first year of the century (100-year range) to use when converting two-digit years into four-digit years. The default value is 50 (i.e., 1950-2049).

    Extraction Pattern - Specifies the pattern applied to a text string when converted into a date/time value. You can select:

    • Date
    • Date, then Time
    • Time
    • Time, then Date
       

     

  2. Select OK to accept and save your changes. The dialog closes, and your settings are applied to the corresponding table.

 

 

 

Related Links

 

 

© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support