Astera Data Stack
Version 8
Version 8
  • Welcome to Astera Data Stack Documentation
  • Release Notes
    • Astera 8.0 - What's New, What's Fixed, and What's Improved
    • Astera 8.0 - Known Issues
    • Astera 8.1 - Release Notes
    • Astera 8.2 Release Notes
    • Astera 8.3 Release Notes
    • Astera 8.4 Release Notes
    • Astera 8.5 Release Notes
  • Getting Started
    • Astera 8 - Important Considerations
    • Astera 8 - System Requirements
    • Configuring the Server
    • Connecting to a Different Astera Server from the Lean Client
    • Connecting to an Astera Server using Lean Client
    • How to Build a Cluster Database and Create a Repository
    • How to Login from Lean Client
    • Setting up a Server Certificate (.pfx) File in a New Environment
    • Installing Client and Server Applications
    • Licensing Model in Astera 8
    • Migrating from Astera 7.x to Astera 8
    • UI Walkthrough - Astera 8.0
    • User Roles and Access Control
  • Dataflows
    • Sources
      • Data Providers and File Formats Supported in Astera
      • Setting Up Sources
      • COBOL File Source
      • Database Table Source
      • Data Model Query Source
      • Delimited File Source
      • Email Source
      • Excel Workbook Source
      • File Systems Item Source
      • Fixed Length File Source
      • PDF Form Source
      • Report Source
      • SQL Query Source
      • XML/JSON File Source
    • Transformations
      • Introducing Transformations
      • Aggregate Transformation
      • Constant Value Transformation
      • Data Cleanse Transformation
      • Denormalize Transformation
      • Distinct Transformation
      • Database Lookup Transformation
      • Expression Transformation
      • File Lookup Transformation
      • Filter Transformation
      • Join Transformation
      • List Lookup Transformation
      • Merge Transformation
      • Normalize Transformation
      • Passthru Transformation
      • Reconcile Transformation
      • Route Transformation
      • Sequence Generator Transformation
      • Sort Transformation
      • Sources as Transformations
      • Subflow Transformation
      • SQL Statement Lookup Transformation
      • Switch Transformation
      • Tree Join Transformation
      • Tree Transform
      • Union Transformation
    • Destinations
      • Setting Up Destinations
      • Database Table Destination
      • Delimited File Destination
      • Excel Workbook Destination
      • Fixed Length File Destination
      • SQL Statement Destination
      • XML/JSON File Destination
    • Data Logging and Profiling
      • Creating Data Profile
      • Creating Field Profile
      • Data Quality Mode
      • Record Level Log
      • Using Data Quality Rules in Astera
    • Database Write Strategies
      • Database Diff Processor
      • Data Driven Write Strategy
      • Dimension Loader - Database Write
      • Source Diff Processor
    • Text Processors
      • Delimited Parser
      • Delimited Serializer
      • Fixed Length Parser
      • Fixed Length Serializer
      • Language Parser
      • XML JSON Parser
      • XML JSON Serializer
    • Data Warehouse
      • Fact Table Loader
      • Dimension Table Loader
  • WORKFLOWS
    • What Are Workflows?
    • Using the Workflow Designer
    • Creating Workflows in Astera
    • Decision Task
    • EDI Acknowledgement Task
    • File System Task
    • File Transfer Task
    • OR Task
    • Run Dataflow Task
    • Run Program Task
    • Run SQL File Task
    • Run SQL Script Task
    • Run Workflow Task
    • Send Mail Task
    • Workflows with a Dynamic Destination Path
    • Customizing Workflows with Parameters
    • GPG-Integrated File Decryption in Astera
  • Subflows
    • Using Subflows in Astera
  • Functions
    • Introducing Function Transformations
    • Custom Functions
    • Logical
      • Coalesce (Any value1, Any value2)
      • IsNotNull (AnyValue)
      • IsRealNumber (AnyValue)
      • IsValidSqlDate (Date)
      • IsDate (AnyValue)
      • If (Boolean)
      • If (DateTime)
      • If (Double)
      • Exists
      • If (Int64)
      • If (String)
      • IsDate (str, strformat)
      • IsInteger (AnyValue)
      • IsNullOrWhitespace (StringValue)
      • IsNullorEmpty (StringValue)
      • IsNull (AnyValue)
      • IsNumeric (AnyValue)
    • Conversion
      • GetDateComponents (DateWithOffset)
      • ParseDate (Formats, Str)
      • GetDateComponents (Date)
      • HexToInteger (Any Value)
      • ToInteger (Any value)
      • ToDecimal (Any value)
      • ToReal (Any value)
      • ToDate (String dateStr)
      • TryParseDate (String, UnknownDate)
      • ToString (Any value)
      • ToString (DateValue)
      • ToString (Any data, String format)
    • Math
      • Abs (Double)
      • Abs (Decimal)
      • Ceiling (Real)
      • Ceiling(Decimal)
      • Floor (Decimal)
      • Floor (Real)
      • Max (Decimal)
      • Max (Date)
      • Min (Decimal)
      • Min (Date)
      • Max (Real)
      • Max (Integer)
      • Min (Real)
      • Pow (BaseExponent)
      • Min (Integer)
      • RandomReal (Int)
      • Round (Real)
      • Round (Real Integer)
      • Round (Decimal Integer)
      • Round (Decimal)
    • Financial
      • DDB
      • FV
      • IPmt
      • IPmt (FV)
      • Pmt
      • Pmt (FV)
      • PPmt
      • PPmt (FV)
      • PV (FV)
      • Rate
      • Rate (FV)
      • SLN
      • SYD
    • String
      • Center (String)
      • Chr (IntAscii)
      • Asc (String)
      • AddCDATAEnvelope
      • Concatenate (String)
      • ContainsAnyChar (String)
      • Contains (String)
      • Compact (String)
      • Find (Int64)
      • EndsWith (String)
      • FindIntStart (Int32)
      • Extract (String)
      • GetFindCount (Int64)
      • FindLast (Int64)
      • GetDigits (String)
      • GetLineFeed
      • Insert (String)
      • IsAlpha
      • GetToken
      • IndexOf
      • IsBlank
      • IsLower
      • IsUpper
      • IsSubstringOf
      • Length (String)
      • LeftOf (String)
      • Left (String)
      • IsValidName
      • Mid (String)
      • PadLeft
      • Mid (String Chars)
      • LSplit (String)
      • PadRight
      • ReplaceAllSpecialCharsWithSpace
      • RemoveChars (String str, StringCharsToRemove)
      • ReplaceLast
      • RightAlign
      • Reverse
      • Right (String)
      • RSplit (String)
      • SplitStringMultipleRecords
      • SplitStringMultipleRecords (2 Separators)
      • SplitString (3 separators)
      • SplitString
      • SplitStringMultipleRecords (3 Separators)
      • Trim
      • SubString (NoOfChars)
      • StripHtml
      • Trim (Start)
      • TrimExtraMiddleSpace
      • TrimEnd
      • PascalCaseWithSpace (String str)
      • Trim (String str)
      • ToLower(String str)
      • ToProper(String str)
      • ToUpper (String str)
      • Substring (String str, Integer startAt)
      • StartsWith (String str, String value)
      • RemoveAt (String str, Integer startAt, Integer noofChars)
      • Proper (String str)
      • Repeat (String str, Integer count)
      • ReplaceAll (String str, String lookFor, String replaceWith)
      • ReplaceFirst (String str, String lookFor, String replaceWith)
      • RightOf (String str, String lookFor)
      • RemoveChars (String str, String charsToRemove)
      • SplitString (String str, String separator1, String separator2)
    • Date Time
      • AddMinutes (DateTime)
      • AddDays (DateTimeOffset)
      • AddDays (DateTime)
      • AddHours (DateTime)
      • AddSeconds (DateTime)
      • AddMonths (DateTime)
      • AddMonths (DateTimeOffset)
      • AddMinutes (DateTimeOffset)
      • AddSeconds (DateTimeOffset)
      • AddYears (DateTimeOffset)
      • AddYears (DateTime)
      • Age (DateTime)
      • Age (DateTimeOffset)
      • CharToSeconds (Str)
      • DateDifferenceDays (DateTimeOffset)
      • DateDifferenceDays (DateTime)
      • DateDifferenceHours (DateTimeOffset)
      • DateDifferenceHours (DateTime)
      • DateDifferenceMonths (DateTimeOffset)
      • DateDifferenceMonths (DateTime)
      • DatePart (DateTimeOffset)
      • DatePart (DateTime)
      • DateDifferenceYears (DateTimeOffset)
      • DateDifferenceYears (DateTime)
      • Month (DateTime)
      • Month (DateTimeOffset)
      • Now
      • Quarter (DateTime)
      • Quarter (DateTimeOffset)
      • Second (DateTime)
      • Second (DateTimeOffset)
      • SecondsToChar (String)
      • TimeToInteger (DateTime)
      • TimeToInteger (DateTimeOffset)
      • ToDate Date (DateTime)
      • ToDate DateTime (DateTime)
      • ToDateString (DateTime)
      • ToDateTimeOffset-Date (DateTimeOffset)
      • ToDate DateTime (DateTimeOffset)
      • ToDateString (DateTimeOffset)
      • Today
      • ToLocal (DateTime)
      • ToJulianDate (DateTime)
      • ToJulianDayNumber (DateTime)
      • ToTicks (Date dateTime)
      • ToTicks (DateTimeWithOffset dateTime)
      • ToUnixEpoc (Date dateTime)
      • ToUtc (Date dateTime)
      • UnixTimeStampToDateTime (Real unixTimeStamp)
      • UtcNow ()
      • Week (Date dateTime)
      • Week (DateTimeWithOffset dateTime)
      • Year (Date dateTime)
      • Year (DateTimeWithOffset dateTime)
      • DateToJulian (Date dateTime, Integer length)
      • DateTimeOffsetUtcNow ()
      • DateTimeOffsetNow ()
      • Day (DateTimeWithOffset dateTime)
      • Day (Date dateTime)
      • DayOfWeekStr (DateTimeWithOffset dateTime)
      • DayOfWeek (DateTimeWithOffset dateTime)
      • DayOfWeek (Date dateTime)
      • DateToJulian (DateTimeWithOffset dateTime, Integer length)
      • DayOfWeekStr (Date dateTime)
      • FromJulianDate (Real julianDate)
      • DayOfYear (Date dateTime)
      • DaysInMonth(Integer year, Integer month)
      • DayOfYear (DateTimeWithOffset dateTime)
      • FromUnixEpoc
      • FromJulianDayNumber (Integer julianDayNumber)
      • FromTicksUtc(Integer ticks)
      • FromTicksLocal(Integer ticks)
      • Hour (Date dateTime)
      • Hour (DateTimeWithOffset dateTime)
      • Minute (Date dateTime)
      • JulianToDate (String julianDate)
      • Minute (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (Date dateTime)
    • Files
      • AppendTextToFile (String filePath, String text)
      • CopyFile (String sourceFilePath, String destFilePath, Boolean overWrite)
      • CreateDateTime (String filePath)
      • DeleteFile (String filePath)
      • DirectoryExists (String filePath)
      • FileExists (String filePath)
      • FileLength (String filePath)
      • FileLineCount (String filePath)
      • GetDirectory (String filePath)
      • GetEDIFileMetaData (String filePath)
      • GetExcelWorksheets (String excelFilePath)
      • GetFileExtension (String filePath)
      • GetFileInfo (String filePath)
      • GetFileName (String filePath)
      • GetFileNameWithoutExtension (String filePath)
      • LastUpdateDateTime (String filePath)
      • MoveFile (String filePath, String newDirectory)
      • ReadFileBytes (String filePath)
      • ReadFileFirstLine (String filePath)
      • ReadFileText (String filePath)
      • ReadFileText (String filePath, String codePage)
      • WriteBytesToFile (String filePath, ByteArray bytes)
      • WriteTextToFile (String filePath, String text)
    • Date Time With Offset
      • ToDateTimeOffsetFromDateTime (dateTime String)
      • ToUtc (DateTimeWithOffset)
      • ToDateTimeOffsetFromDateTime
      • ToDateTimeOffset (String dateTimeOffsetStr)
      • ToDateTimeFromDateTimeOffset
    • GUID
      • NewGuid
    • Encoding
      • ToBytes
      • FromBytes
      • UrlEncode
      • UrlDecode
    • Regular Expressions
      • ReplaceRegEx
      • ReplaceRegEx (Integer StartAt)
    • TimeSpan
      • Minutes
      • Hours
      • Days
      • Milliseconds
    • Matching
      • Soundex
      • DoubleMetaphone
      • RefinedSoundex
  • Report Model
    • User Guide
      • Report Model Tutorial
    • Report Model Interface
      • Field Properties Panel
      • Region Properties Panel
      • Report Browser
      • Report Options
    • Use Cases
      • Applying Pattern to Line
      • Auto Creating Data Regions and Fields
      • Auto-Parsing
      • Creating Multi-Column Data Regions
      • Floating Patterns and Floating Fields
      • How To Work With PDF Scaling Factor in a Report Model
      • Line Count
      • Pattern Count
      • Pattern is a Regular Expression
    • Exporting Options
      • Exporting a Report Model
      • Exporting Report Model to a Dataflow
    • Miscellaneous
      • Importing Monarch Models
      • Microsoft Word and Rich Text Format Support
      • Working With Problematic PDF Files
  • API Flows
    • API Consumption
      • Consume
        • REST API Browser
        • Making HTTP Requests Through REST API Browser
        • Using REST Client Outside of the Scope of the Project
      • Authorize
        • Authorizing ActiveCampaign API in Astera
        • Authorizing Astera Server APIs
        • Authorizing Avaza APIs in Astera
        • Authorizing Facebook APIs in Astera
        • Authorizing QuickBooks API in Astera
        • Authorizing Square API in Astera
        • Open APIs - Configuration Details
  • Project Management
    • Project Management
      • Astera's Project Explorer
      • Connecting to Source Control
      • Deployment
      • Server Monitoring and Job Management
    • Job Scheduling
      • Scheduling Jobs on the Server
      • Job Monitor
  • Use Cases
    • End-to-End Use Cases
      • Data Integration
        • Using Astera to Create and Orchestrate an ETL Process for Partner Onboarding
      • Data Warehousing
        • Building a Data Warehouse - A Step-By-Step Approach
      • Data Extraction
        • Reusing The Extraction Template for Similar Layout Files
  • Connectors
    • Connecting to Amazon Aurora Database
    • Connecting to Amazon RDS Databases
    • Connecting to Amazon Redshift Database
    • Connecting to Cloud Storage
    • Connecting to Google Cloud SQL in Astera
    • Connecting to MariaDB Database
    • Connecting to Microsoft Azure Databases
    • Connecting to MySQL Database
    • Connecting to Netezza Database
    • Connecting to Oracle Database
    • Connecting to PostgreSQL in Astera
    • Connecting to Salesforce Database
    • Connecting to Salesforce - Legacy Database
    • Connecting to SAP HANA Database
    • Connecting to Snowflake Database
    • Connecting to Vertica Database
    • Setting Up IBM DB2 iSeries Connectivity in Astera
  • Miscellaneous
    • Cloud Deployment
      • Deploying Astera on Amazon Web Services
      • Deploying Astera on Microsoft Azure Cloud
      • Deploying Astera on Oracle Cloud
    • Context Information
    • Pushdown Mode
    • Role Based Access Control in Astera
    • Safe Mode
    • Server Command Line Utility
    • SmartMatch Feature
    • Synonym Dictionary File
    • Updating Your License in Astera
    • Using Dynamic Layout/Template Mapping in Astera
    • Using Output Variables in Astera
    • Using the Data Source Browser in Astera
  • Best Practices
    • Astera Best Practices - Dataflows
    • Overview of Cardinality in Data Modeling
    • Cardinality Errors FAQs
Powered by GitBook

© Copyright 2025, Astera Software

On this page
  • Use Case
  • Working with the File Lookup transformation
  1. Dataflows
  2. Transformations

File Lookup Transformation

PreviousExpression TransformationNextFilter Transformation

Last updated 10 months ago

The File Lookup Transformation object in the Astera Data Stack is used to look up values coming from a source. It uses an Excel or delimited file that contains the lookup values as well as the output values to perform the lookup.

File lookup can be performed based on a single lookup field as well as a combination of fields. Similarly, a File Lookup Transformation object can return a single output field from a lookup table or a combination of fields. In either case, the output field or fields are returned from the records in which the lookup values match the incoming values.

Use Case

In this scenario, we have a Customers dataset from a fictitious organization stored in a database source. It contains information about customers from different countries.

We want to replace the country names with country codes in our database, by switching them with the lookup values (country codes) stored in an Excel file. To achieve this, we will use the File Lookup Transformation object.

Working with the File Lookup transformation

  1. Select the relevant source object from the Sources section in the Toolbox. In this example, we will use the Customers data stored in a database table.

Right-click on the source object’s header and select Preview Output. You can see the country names in the Country field which we want to convert into country codes.

  1. Drag and drop the File Lookup transformation object from Toolbox> Transformations> File Lookup onto the dataflow designer.

Now, let’s configure the transformation object.

  1. Right-click on the header of the File Lookup transformation object and select Properties.

  1. A File Lookup Map Properties window will open where you can see an option for Source File Type. Here, you need to specify the type of your lookup file.

You can perform the task using an Excel or Delimited lookup file.

  1. Select the Source File Type from the dropdown menu. In this example, our country codes are stored in an Excel file so we will specify the Source File Type as Excel.

  1. Click Next to proceed to the File Lookup Details window.

File Location: Here, you need to specify the File Path to the lookup source file.

You will see two options:

  • First Row Contains Header: You can check this option if your lookup file contains a header in the first row. Otherwise, you can leave it unchecked.

  • Worksheet: If your lookup file contains multiple worksheets, you can select the worksheet you want to use to perform the lookup.

  1. Click Next to proceed to the Layout Builder. Here, you can make changes to the object’s layout by modifying the existing fields or creating new fields.

Once you are done, click Next.

  1. On the next window, you will see various Lookup Options.

If multiple values are found, then opt for the Multiple Matches Found Option.

  • Multiple Matches Found Option: This option provides the flexibility to choose the output value if more than one match is found for a single value in your lookup file. The option expands into a drop-down list where you can select one from the following three options:

    • Return First: Will return the first matched value found.

    • Return Last: Will return the last value among all the matched values.

    • Return All: Will return all the values in the lookup file that match a source value.

  • If Value Is Not Found In The Lookup List: In case no lookup value is found against a source value, you can choose one of the following three options to be appended to your output.

    • No Message: There will be no message and the output will be the same as the input value.

    • Add Error: An error message will appear with the output.

    • Add Warning: A warning will appear with the output.

  • If Value Is Not Found In The Lookup List, Assign Value: If no lookup value is found against a source value, you can assign an output value of your choice.

    • Assign Source Value: Will return the source value in the output.

    • Assign Null: Will assign null to your output value.

    • This Value: You can select this option and assign any value of your choice.

  1. Click Next. This will take you to the Config Parameters window, where you can further configure and define parameters for the File Lookup transformation object.

Once you have configured the File Lookup transformation object, click OK.

  1. Map the Country field from the Source object to the Country field in the File Lookup transformation object. Now map the Code field from the transformation object to the Country field in the Database Table Destination object.

This is what your dataflow will look like:

  1. Map the remaining fields from the source object to the destination object.

  1. Right-click on the destination object’s header and select Preview Output.

  1. You can see that the country names in the database table have been successfully converted into country codes.

This concludes using the File Lookup Transformation object in Astera Data Stack.