Create a Connection in Construct

A connection to a Microsoft Access database can be made in Construct. Connections point to either a database or a folder. In this case, an Access connection will connect to a database containing your data tables.

1. Open the Create New Connection window either from the main Workspace, or from within a job.
2. Select Access or the appropriate driver from the available options as the Data Source. Later you will be able to select the specific tables to work with.

There is no Access data provider included in Construct, however Construct can utilize an Access ODBC, OLE DB, or ADO.NET provider. If these are already installed on your machine then you should see those available as data sources. If they are not already installed, you will need to install them. These should be free installs with your license of Microsoft Office.

All that is required in order to configure an Access connection is the required driver, database credentials, and the file name. Unlike other connection types, you will be required to specify the file name at this stage.

An important note is that the connection name should be unique and specific to the database that the connection is pointing to. Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the tables you wish to incorporate into your job. Select only those you plan to work with.

If you do not see an Access option in the Data Source field, it may be because you have the 64 bit version of Construct and a 32 bit version of Microsoft including Access. One option is to switch to the 32 bit version of Construct. In many cases this is can be a seamless transition, however in some cases if you are using 64 bit drivers for other connections, switching will be detrimental to those other connections. You will likely need to contact RI-Support@eab.com to get instructions and installation information for switching. Another option is to perform a “silent” install 64 bit Microsoft.

Also note that there are two Access file types to choose from the Office 2007 version (.accdb) and the legacy version (.mdb). The correct file type must be selected for the intended data source to appear as an option.

Data from Navigate is exported into an S3 bucket which can then be connected to in Construct. Specific credentials are required to access the S3 bucket and create the connection in Construct. These credentials can be generated by Navigate App Admins within the platform. To learn more about generating data export credentials, visit this article, or contact NavigateTechSupport@eab.com to inquire about this feature.

1. Open the Create New Connection window either from the main Workspace, or from within a job.
2. Select EAB Navigate as the Data Source.
   
3. Generate your "Data Export Credentials" from the Administration page of the Navigate platform.

The EAB Navigate connection data source is included in all versions of Construct 5.2.23009.0 and beyond. If you do not see EAB Navigate as a connection option, please update to version 5.2.23009.0 or later, or contact RI-Support@eab.com.

An AWS Key, AWS Secret, and Bucket Name are required to configure a Navigate connection. Visit this article or contact NavigateTechSupport@eab.com for more information on generating these parameters.

1. Leave the AWS Region as the default value "us-east-1".
2. Enter your Access Key ID as the AWS Key.
3. Enter your Secret Access Key as the AWS Secret.
4. Enter everything between the first two slashes of the Default Remote Directory as the Bucket Name.

   Example:
   Default Remote Directory = /navigate-data-test-1a2b3c4d5e6f7g8h9i/exports/
   Bucket Name = navigate-data-test-1a2b3c4d5e6f7g8h9i

5. Test your connection, then Save.

Upon saving the connection, you will be prompted to select which of the 12 export files you wish to incorporate into your job.

If you do not see an EAB Navigate option in the Data Source field, it may be because you are not on a version of Construct that includes/supports this connection. The EAB Navigate connection is available in version 5.2.23009.0 and later. If you need to update to a later version, visit this article for instructions or contact RI-Support@eab.com.

If you receive a Bucket not found error, it is likely that the Bucket Name was entered incorrectly. Remember that the Bucket Name is everything between the first two slashes of the Default Remote Directory (See Required parameters section for an example). Try re-entering the Bucket Name or contact RI-Support@eab.com for additional assistance.

If there are no files available when you attempt to Select Tables, it is most likely the case that the AWS Key or the Secret Key was entered incorrectly, even though the connection may have tested successfully. Try re-entering both keys or contact RI-Support@eab.com for additional assistance.

Please note the Secret Access Key will only be visible when the credentials are first generated. If you lose or forget your Secret Access Key, you will need to revoke the credentials and generate new credentials. Additionally, credentials automatically expire after 90 days. Credentials can be rotated at any time by clicking the Revoke Credentials button, then clicking the Generate Credentials button within the Administration page of the Navigate platform.

If you are unable to generate Data Export Credentials on the Administration page of the Navigate Platform, please contact NavigateTechSupport@eab.com to gain access to this feature.

A connection to an Excel file (.xls, .xlsx, .xlsb) can be made in Construct. Connections point to either a database or a folder. In this case, an Excel connection will connect to a folder containing your Excel files.

There are two different ways to connect to flat files (Excel, Text, etc.).

1. Locate the file in a Windows File Explorer and then drag and drop the file into the top left connections window, in the job. This will automatically create the connection for you and bypass the following instructions.
2. Create a connection by opening the Create New Connection window either from the main Workspace, or from within a job. Select Excel File as the Data Source, and then browse to the folder containing the desired files. Later you will be able to select the specific workbook files and worksheets to work with.

Oftentimes, Microsoft Excel Input data drivers attempt to determine a column's data type using the first eight records. As a result, numeric values are often passed as nulls. Formatting of the corresponding column in Excel has no effect on this behavior.

To mitigate this, Rapid Insight has developed its own Excel data driver for use when connecting to data sources and destinations. It appears in the driver list as “Excel File (*.xls, *.xlsx, *.xlsb)” and is the default driver selected when the Excel icon is clicked. The driver actively reads the formatting of the original worksheet cells to develop its columnar data types. When this information is not available, it will attempt to infer this information from the row values in a column. The number of rows used to make this educated guess is specified in the Type Guess Sample Size field under the Options menu. As an output driver, it takes the Construct column types and matches them to corresponding Excel format types when writing to a worksheet.

All that is required in order to configure an Excel connection is the folder in which the Excel files are located. As mentioned above, in the Folder window, click the ellipsis button to navigate to the folder containing the desired files.

An important note is that the connection name should be unique and specific to the folder that the connection is pointing to. For example, you may call this connection ‘Documents_Excel’, if the Excel files you are connecting to are in your Documents folder.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the files you wish to incorporate into your job. Select only those you plan to work with.

If you do not see a file you are looking for, it may be because that file is currently open on your machine or another machine. Save and close the file in Excel and try adding the files again.

If the data types are not reading in properly, attempt to format the columns within Excel and then refresh the connection using the refresh option (bottom right) in the input node.

A connection to an Oracle database can be made in Construct. Connections point to either a database or a folder. In this case, an Oracle connection will connect to a database containing your data tables.

1. Open the Create New Connection window either from the main Workspace, or from within a job.Select the appropriate driver from the available options as the Data Source. We recommend the Oracle OLEDB, ADO.NET, or the Managed driver. Later you will be able to select the specific tables to work with.

If you are currently connecting to the Oracle server from your machine, then you will most likely have an ODBC or OLE DB provider as an available option within Construct. In addition we ship a version of the managed driver for Oracle as well, which will be the default provider selected.

All that is required in order to configure an Oracle connection is the name of the Database and valid access credentials on the Oracle Database. Depending on the provider that you have chosen, you may additionally need to have a tnsnames.ora file accessible to your machine and an environment variable pointing to the location of that file. It is likely you already have a tnsnames.ora file available, but if not you can use the Oracle Net Configuration (or comparable) tool, to generate a “tnsnames.ora” file.

An important note is that the connection name should be unique and specific to the database that the connection is pointing to.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the tables you wish to incorporate into your job. Select only those you plan to work with.

1. If you get the error "Could not find file '%TNS_ADMIN%\tnsnames.ora'." when using the Databases drop down list, this means that the computer needs help locating the tnsnames.ora file. To resolve the issue, confirm that the TNS_ADMIN environment variable (found under Control Panel > System > Advanced Systems Settings > Environment Variables) points to the tnsnames.ora file folder. Your IT department may need to configure this.
2. If you get the error that there is no TNS Listener, it could be related to the issue above. Ensure that the tnsnames.ora file is accessible on the machine and that there is an environment variable pointing towards the location of the file. If all of that is correctly installed, then it may be that the formatting of the tnsnames.ora file is incompatible with Construct. To resolve the issue, try inputting the full tnsnames.ora file entry in the database entry window,. It will be required to be on a single line of text, so you will need to edit the entry prior to pasting it into that window.

A connection to a SAS file (.sas) can be made in Construct. Connections point to either a database or a folder. In this case, a SAS connection will connect to a folder containing your SAS files.

1. Open the Create New Connection window either from the main Workspace or from within a job.
2. Select SAS Local Data Provider as the Data Source, and then browse to the folder containing the desired files. Later you will be able to select the specific files to work with.

There is no SAS data provider included in Construct, however Construct can utilize any ODBC, OLE DB, or ADO.NET provider.

All that is required in order to configure a SAS connection is the folder in which the SAS files are located. As mentioned above, in the Full Folder Name window, click the ellipsis button to navigate to the folder containing the desired files.

An important note is that the connection name should be unique and specific to the folder that the connection is pointing to. For example, you may call this connection ‘Documents_SAS’, if the files you are connecting to are in your Documents folder.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the files you wish to incorporate into your job. Select only those you plan to work with.

If you do not see a SAS option in the Data Source field, it may be because the driver is not installed. See the data providers section for a download link.

Another reason you may not see it is because you have the 64 bit version of Construct and a 32 bit version of the provider. The bitness of providers and the application need to match. In order to see the bitness of the application go to Help > About.

A connection to SPSS files (.sav) can be made in Construct. Connections point to either a database or a folder. In this case, an SPSS connection will connect to a folder containing your SPSS files. To create a connection open the Create New Connection window either from the main Workspace, or from within a job.

Construct can utilize an SPSS ODBC provider, or the IBM SPSS Statistics 19 Data File Driver. If these are already installed on your machine then you should see those available as data sources. If they are not already installed, you will need to install them. These should be free with your license of SPSS.

All that is required in order to configure an SPSS connection is an appropriate data provider, mentioned above, and the folder in which the SPSS (.sav) files are located. In the Full Folder Name window, click on the ellipsis button to navigate to the desired folder.

An important note is that the connection name should be unique and specific to the folder that the connection is pointing to. For example, you may call this connection ‘Documents_SPSS’, if the SPSS files you were connecting to were in your Documents folder.

Once the connection is tested and successful, click Ok. Upon saving the connection, you will be prompted to select the files you wish to incorporate into your job. Select only those that you plan to work with.

If you do not see a data source of SPSS available in your list, you may need to download an appropriate driver. Please refer to the Data Providers section above.

If you are unable to see the SPSS files in the folder, then the version of the files may be incompatible with the driver. Make sure that your files are saved as .sav files.

If you are able to access the files and connect to them in Construct, but see some variations in column names, this is a limitation of the providers. There is a modification to a configuration file that can resolve this, but you will want to reach out to RI-Support@eab.com for assistance.

A connection to a SQL database can be made in Construct. Connections point to either a database or a folder. In this case, a SQL connection will connect to a database containing data tables.

1. Open the Create New Connection window either from the main Workspace or from within a job.
2. Select an available SQL Server provider from the Data Source list (Microsoft ADO.NET Provider for SQL Server, Microsoft OLE DB Provider for SQL Server, etc.).
3. Select the Server and Database, as well as providing your credentials. Later you will be able to select the specific tables to work with.

There is no SQL data provider included in the application, however Construct can use a SQL ODBC, OLE DB, or ADO.NET provider. If these are already installed on your machine then you should see those available as data sources. If they are not already installed, you will need to install them. These installers are freely available.

All that is required in order to configure an SQL connection is an appropriate data provider, the server location, connection credentials (typically your Windows authenticated credentials), database name and table schema. An important note is that the connection name should be unique and specific to the database that the connection is pointing to.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the tables you wish to incorporate into your job. Select only those you plan to work with.

If you do not see a SQL option in the Data Source field, it may be because you have the 64 bit version of Construct and a 32 bit version of the SQL provider. There are two options to troubleshoot the issue.

1. Switch to the 32 bit version of Construct. In many cases this can be a seamless transition, however in some cases if you are using 64 bit drivers for other connections, switching will be detrimental to those other connections. You will likely need to contact RI-Support@eab.com to get instructions and installation information for switching.
2. Install the correct bitness version of your preferred SQL provider, so that it matches the bitness of the Construct application. Once installed, you should be able to restart the application and see this provider as an available option.

If the provider is working, but testing the connection is not successful, it could be a number of issues. Here are a few things to check:

• Check to make sure that the server, database and table schema are all correctly spelled (table schema can be left as All which gives you full access to all tables in the database you have been given permissions to.
• Check to make sure that you have the correct credentials. Typically these are your Windows authenticated credentials, but you may need to provide credentials.
• Check to make sure you have permissions to access the database. Construct will limit access based on your DBA's permission settings.
• Make sure your computer is able to communicate with the SQL Server. You can try pinging the SQL Server in order to confirm communication is possible.

A connection to a text file (.txt, .asc, .csv, .tab) can be made in Construct. Connections point to either a database or a folder. In this case, a text connection will connect to a folder containing your text files.

There are two different ways to connect to flat files (Excel, Text, etc.).

1. Locate the file in a Windows File Explorer, then drag and drop the file into the top left connections window in the job. This will automatically create the connection and bypass the following instructions.
2. Open the Create New Connection window either from the main Workspace or from within a job. Select Text File as the Data Source, then browse to the folder containing the desired files. Later you will be able to select the specific files to work with.

There is a text provider available within Construct labeled 'Text File (Source)'. Choose this provider whenever connecting to .txt, .asc, .csv, or .tab files.

All that is required in order to configure a text connection is the folder in which the text files are located. As mentioned above, in the Full Folder window, click the ellipsis button to navigate to the folder containing the desired files.

An important note is that the connection name should be unique and specific to the folder that the connection is pointing to. For example, you may call this connection ‘Documents_Text’, if the text files you are connecting are in your Documents folder.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the files you wish to incorporate into your job. Select only those you plan to work with.

When selecting files, often specifically with text files, Construct will usually, but not always, set up the file schema upon connecting to the data source. If the schema has not been automatically configured, the Save button may appear grayed out and may be unclickable. If the Has Properties column contains a check, then the table schema is set up by default and nothing more is needed. If the check is missing, additional steps will be needed to set up the schema of the dataset. 

To define the text properties, select Properties... Schemas are typically categorized as either a Delimited file if the data is separated with commas, tabs, or another character; or a Fixed Width file if the data values have required lengths that are uniform.

Delimited

To establish the properties for a delimited file:

1. Select Delimited in the File Properties window.   
2. Define the file's general formatting and evaluate the Sample From File output. If the default options are not correct and the Sample From File does not look correct, generally a Row Delimiter, Text Qualifier, or Delimited/Fixed Width selection will need to be adjusted. When the settings are configured so that the file looks as expected, select Next.  
    
3. On the second window, define what character(s) are used as field delimiters (what character is separating each value in the dataset, typically a comma). Check that the sample output looks correct. An external schema file can also be imported on this page, however this is typically used for fixed width files. Select Next.   
4. On the last window, define the data types and labels for each column. Default values are assigned based on the field names and type guess, however if a column's data type cannot be guessed, users will be prompted to manually define this property. When this is done select Finish. 

Fixed With

To establish the properties for a fixed width file:

1. Select Fixed Width in the File Properties window. Select Next.   
2. Define the width of each value and the total number of columns in the file. This can be done by importing a schema file using the Import button. Schema files should be formatted as Column name, Data type, Column width, Column starting position. Individual column widths can also be defined by clicking the tick marks in the data preview. To define a column, click the tick at the desired starting position/first character of the column. The next tick clicked will be the start of the next column. Once all columns are defined, select Next.   
3. On the last window, define the data types and labels for each column. Default values are assigned based on the field names and type guess, however if a column's data type cannot be guessed, users will be prompted to manually define this property. When this is done select Finish.   

To edit a file schema after a connection has been made, right-click the table under the connections sections, and select Text Properties.

1) To create a connection to a folder containing your XML files, open the Create New Connection window either from the main Workspace or from within a job.

2) Select XML File as the Data Source, then browse to the folder containing the desired files. Later you will be able to select the specific files to work with.

Construct uses an integrate driver to both read and write XML formatted data. Users should be aware of the following conditions that may affect their use of this data type.

• Read the records from any XML formatted data file.
• Parse incoming data records using schema imbedded in the XML file.
• Parse incoming data records using schema contained in a collocated XSD (XML Schema Definitions) file if it is named the same as the XML file.
• Ignore any attribute information incorporated into data records. Such records should be parsed by the user outside of Construct into a more conventional format prior to use.
• Write files which imbed any needed schema information into file itself.

All that is required in order to configure an XML connection is the folder in which the XML files are located. As mentioned above, in the XML file folder window, click the ellipsis button to navigate to the folder containing the desired files.

An important note is that the connection name should be unique and specific to the folder that the connection is pointing to. For example, you may call this connection ‘Documents_XML’ if the XML files you are connecting are in your Documents folder.

Once the connection is tested and successful, click Save. Upon saving the connection, you will be prompted to select the files you wish to incorporate into your job. Select only those you plan to work with.

Depending on the format of the XML file, this particular provider may need some modifications to its configuration or an entirely new driver may be required. In that case, contact RI-Support@eab.com.