Go Back
Fixed Width Data Processing
Publisher
: Lingk, Inc
Run In Lingk
Description
This recipe reads data from a fixed width file with line breaks between records. Use substring and trim functions to pull out specific column values.
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - FIXED WIDTH DATA PROCESSING # Recipe ID - # Recipe URL - https://app.lingk.io/a/10932/tf/17948 # Description - # This recipe reads data from a fixed width file with line breaks between records. Use substring and trim functions to pull out specific column values. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Lingk # Connectors - AmazonS3 # Data Flows - Single Direction # Connection Type - S3 # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure HTTP credentials in your Environment before running this recipe # HTTP Setup - https://help.lingk.io/en/articles/304-http-connector-setup-guide ###### Start - S3 Bucket connectors ###### - name: s3FileReader type: s3Reader format: csv properties: s3ObjectKey: fixedwidth.txt s3BucketName: test-files # FORMATS specify how files should be processed as they are being read or written to ###### End - S3 Bucket connectors ###### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ readFormats: - name: csv type: delimited properties: delimiter: '\n' header: false # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: #******************************************************************** D I S C L A I M E R *********************************************************************************************** # * # Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe. * # For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform * # * #******************************************************************** D I S C L A I M E R *********************************************************************************************** #1. Set your fixed width record values and transformation - statement: | (outputData) => SELECT substring(_c0,1,1) record_type, substring(_c0,2,10) member_number, substring(_c0,12,3) account_number, trim(TRAILING FROM substring(_c0,15,81)) name1, trim(TRAILING FROM substring(_c0,96,81)) name2 FROM parsedRecords #- statement: print outputData # 2. insert/update/etc. # Add more statements to convert, join, aggregrate, transform, and integrate your data
Using Lookup Values in Select Statements
Escape Unicode characters for JSON, XML and more...