Explains how to retrieve data from a lookup file.
After you Load Your Data into a Relational Database, you are ready to retrieve data from your lookup file. You have been provided a second text file containing a list of cities, states, and postal codes that you will now use to look up the postal codes for all of the records where they were missing (the ‘false’ branch of your Filter rows step). First, you will use a Text file input step to read from the source file, then you will use a Stream lookup step to bring the resolved Postal Codes into the stream.
- Add a new Text File Input step to your transformation. In this step you will retrieve the records from your lookup file. Do not add a hop yet.
- Open the Text File Input step window, then enter Read Postal Codes in the Step name window.
- Click Browse to locate the source file, Zipssortedbycitystate.csv, located at
- Click Add. The path to the file appears under Selected files.
- To look at the contents of the sample file:
- Click the Content tab, then set the Format field to Unix.
- Click the File tab again and click the Show file content near the bottom of the window.
- The Nr of lines to view window appears. Click the OK button to accept the default.
- The Content of first file window displays the file. Examine the file to see how that input file is delimited, what enclosure character is used, and whether or not a header row is present. In the example, the input file is comma (,) delimited, the enclosure character being a quotation mark (“) and it contains a single header row containing field names.
- Click the Close button to close the window.
- In the Content tab, change the separator character to a comma (,). and confirm that the enclosure setting is a quotation mark ("). Make sure the Header option is selected.
- Under the Fields tab, click Get Fields to retrieve the data from your .csv file.
- The Nr of lines to sample window appears. Enter 0 in the field, then click OK.
- If the Scan Result window displays, click OK to close it.
- Click Preview rows to make sure your entries are correct. When prompted to enter the preview size, click OK. Review the information in the window, then click Close.
- Click OK to exit the Text File input window.
- Save the transformation.