This workflow reads from 2 excel files in order to transform the data into a single a CSV file that is compliant to the LIVES Standard.1
It uses the following transformations:
- Excel Input
- String Operations (Trim on key fields)
- Sorter for key fields (required before merge join)
- Merge Join (Left outer since a business may have no violation)
- Concat Fields (Street # with Street Name)
- Select Values (renames to schema, selects fields to write to output file)
- Text Output (force quote enclosure, comma separator, ISO8601 date format and trims all fields)
Follow these steps to get the example workflow working.
- Download the workflow and associated files using the Download link above and open it from Spoon. NOTE: You may have to right-click > Save Link As… to get it to download.
- Update the Paths to the Raw Inspection and Violation files so they point to the correct directory.2
- Update the path for the Text file output.
- Run the workflow and ensure that the file transformed_data.csv was created in the directory with current timestamp.