Editing addresses against a Melissa Data data file

This Job uses the tFixedFlowInput component to generate the address data to be analyzed, the tMelissaDataAddress component to analyze the input schema and validate, correct, and standardize the US addresses generated by the tFixedFlowInput component and a tLogRow component to output the correct formatted addresses on the console.

Important: tMelissaDataAddress works with data from a Melissa Data installer of 2023-05 or before. If you installed data from 2023-06 onwards, the Job fails.

This scenario applies only to Talend Data Management Platform, Talend Big Data Platform, Talend Real-Time Big Data Platform, Talend MDM Platform, Talend Data Services Platform, Talend MDM Platform and Talend Data Fabric.

Before you begin

You must:

Retrieve longitude and latitude data and the GeoCode result codes:
- Purchase a GeoCode or a GeoPoint license.
- Install Melissa Data with the GeoPoint and GeoCode data files.
Add the path to the folder containing the mdAddr library to the system environment variables.
- When No Melissa GeoCoder License Was Purchased is selected, the required libraries are:
  - mdAddr.dll
  - mdAddrJavaWrapper.dll
  - mdRightFielder.dll. This library is required when the only input column is Address.
  - mdRightFielderJavaWrapper.dll. This library is required when the only input column is Address.
- When No Melissa GeoCoder License Was Purchased or The Melissa GeoCoder License Was Purchased is selected, the required libraries are:
  - mdGeo.dll
  - mdGeoJavaWrapper.dll
  - mdAddr.dll
  - mdAddrJavaWrapper.dll
  - mdRightFielder.dll. This library is required when the only input column is Address.
  - mdRightFielderJavaWrapper.dll. This library is required when the only input column is Address.
  On Windows, the variables must be added in the Environment variables window.
When the system environment variable is not set correctly, the following error is to be expected:
```
java.lang.Error: java.lang.UnsatisfiedLinkError
```
On Linux, restart your computer after setting your system environment variables to take the changes into account.

Setting up the Job

Procedure

Drop the following components from the Palette onto the design workspace: tFixedFlowInput, tMelissaDataAddress and tLogRow.
Connect the three components together using Row > Main connections.

Configuring the input component

Procedure

Double-click tFixedFlowInput to open its Basic settings view in the Component tab.
Click Edit schema to make changes to the schema.
Click the [+] button to add the columns that will hold the address data to your input schema.
For this example, add:
- input_company
- input_address1
- input_address2
- input_city
- input_state
- input_postal
Click OK.
In the Number of rows field, set the number of rows as 1.
In the Mode area, select the Use Inline Content (delimited file) option, and set the row and field separators in the corresponding fields.

In the Content table, enter the address data you want to analyze.

For example:

Talend Inc.|5150 El Camino Real|Suite C-31|Los Altos||94022|
Talend Inc.|6 Executive Circle|Suite 200|Irvine|California|92614|
Talend Inc.|220 White Plains Road|Suite 390|Tarrytown|New York|10591|
Talend Inc.|8 New England Executive Park|Suite 170|Burlington|Massachusetts|01803|

Configuring the tMelissaDataAddress component

Procedure

Double-click tMelissaDataAddress to display the Basic settings view and define the component properties.
Click Sync columns to retrieve the schema from the preceding component.
Click the Edit schema button to view the input and output schema and edit the output schema, if necessary.
Read-only columns are added the output schema:
- COMPANY_STANDARDIZED returns the standard company name.
- ADDRESLINE1_STANDARDIZED returns the first line of the street address.
- ADDRESLINE2_STANDARDIZED returns the second line of the street address.
- CITY_STANDARDIZED returns the standard city name.
- STATE_STANDARDIZED returns a two-letter abbreviation for the state name.
- POSTAL_STANDARDIZED returns the postal ZIP code.
- COUNTRY_STANDARDIZED returns a two-letter abbreviation for the country name.
- RESULT_CODES returns verification codes.
Click OK to close the dialog box.
In the Input Address table:
1. Use the [+] button to add lines in the table.
2. Click in the Address Field column and select from the predefined list the fields that hold the input address data.
  The component will map the values of these fields to the input columns you set in this table.
3. Click in the Input Column column and select from the list the columns from the input schema that hold the input address data you want to parse.
In the Output Address table, you can define additional address fields:
1. Use the [+] button to add lines in the table.
  These lines will hold the extra information you want to retrieve from Melissa Data, such as the Address Key, the country name or longitude and latitude data.
2. Click in the Address Field column and select from the predefined list the fields that hold the output address data.
  
  The component will map the values of these fields to the output columns you set in this table.
3. Click in the Output Column column and select from the list the columns from the output schema that will hold the extra information.
  
  If you click Sync Columns after adding columns to the output schema, they are removed.
In the Specify your MelissaData license field, set your license key provided by Melissa Data when you order the Data Quality Suite or the Address Object API.

If the license key you entered is not correct, you can use GeoCoder in demo mode.
In the Specify your MelissaData DataFile folder field, set the path to the Melissa Data data folder provided by Melissa Data.
In the Advanced settings view of the component, select the license you purchased.

If you have not purchased a GeoPoint or a GeoCode license, select No Melissa GeoCoder License Was Purchased to run the Job. Note that you will not be able to retrieve latitude and longitude data and GeoCode result codes.

Saving and executing the Job

Procedure

Save your Job and press F6 to execute it.

Results

In addition to verifying and standardizing an address, tMelissaDataAddress will also match street names against a ZIP code, match geographic data to ZIP code and city information and finally parse street addresses and return all these results via different output columns. This example shows only some of the output columns written by the tMelissaDataAddress component:

GetAddressKey returns the Address Key.
GetCountyName returns the county names.
GetTimeZone returns the time zone.
GetLongitude returns the longitude data.
GetLatitude returns the latitude data.
GeoCodeResult returns the GeoCode result codes.
The output standard columns return the standard company name, up to two street address lines, the standard city name, two-letter abbreviation for the state name, the postal ZIP code, and two-letter abbreviation for the country name.
The RESULTS_CODE output column returns verification codes for each of the processed address rows. These codes are written in comma-delimited lists. Each code consists of two letters followed by two numbers. These codes indicate different statuses and errors. For example, the AC02 code means that the state name is corrected based on the combination of city name and ZIP code, and the AS01 code means that the street address is valid and deliverable.

For a complete list of the result codes and for further information about all the output columns,visit http://www.melissadata.com/.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here