{"id":14553,"date":"2021-08-05T16:33:28","date_gmt":"2021-08-05T20:33:28","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=14553"},"modified":"2021-08-10T11:05:15","modified_gmt":"2021-08-10T15:05:15","slug":"sortcl-excel-processing-examples","status":"publish","type":"post","link":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/","title":{"rendered":"SortCL-Compatible Excel Data Processing Examples"},"content":{"rendered":"

This article is a continuation of the previous article<\/a>, which introduced IRI support for XLS and XLSX file formats in the SortCL<\/a> program used for moving and manipulating spreadsheet data in both the IRI Voracity data management (ETL, data quality, etc.) platform, and its included fit-for-purpose products: IRI CoSort, NextForm, FieldShield and RowGen.<\/p>\n

These job samples help demonstrate the breadth of what these metadata-compatible applications can now do with data directly to\/from Excel sheets in either XLS or XLSX formats.<\/p>\n

Sample Job Scripts<\/strong><\/h6>\n

Note: To view XLS and XLSX files from within IRI Workbench, simply double click on the file in Project Explorer.<\/p>\n

Example #1:<\/b> Synthesizing Test Data<\/p>\n

This script demonstrates the use of an IRI RowGen job script for generating a small amount of random test data to an XLSX file, and an XLS file with a header.<\/i><\/p>\n

\/INFILE=simple.in\r\n\u00a0\u00a0\u00a0\u00a0\/INCOLLECT=5\r\n\u00a0\u00a0\u00a0\u00a0\/PROCESS=RANDOM\r\n\u00a0\u00a0\u00a0\u00a0\/FIELD=(code,POSITION=1,SEPARATOR=\"\\t\",SIZE=5,TYPE=ALPHA_DIGIT)\r\n\u00a0\u00a0\u00a0\u00a0\/FIELD=(value,POSITION=2,SEPARATOR=\"\\t\",SIZE=8,TYPE=WHOLE_NUMBER)\r\n\u00a0\u00a0\u00a0\u00a0\/INCLUDE WHERE value >10\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\"'Sheet1'!A1:B5;randomnums.xlsx\"\r\n    \/PROCESS=XLSX\r\n    \/FIELD=(code,POSITION=1,SEPARATOR=\"\\t\",TYPE=ALPHA_DIGIT,SDEF=\"=A\")\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n    #format value field with a prepended string\r\n    \/FIELD=(valuef=format_strings(\"H2N3%s\",value),POSITION=2,SIZE=12,SEPARATOR=\"\\t\",TYPE=ASCII,SDEF=\"=B\")\r\n\/OUTFILE=\"'Sheet1'!BB1:BC6,HEADER;randomnums.xls\"\r\n    \/PROCESS=XLS\r\n    \/FIELD=(code,POSITION=1, SEPARATOR=\",\",SDEF=\"Code=BB\")\r\n    \/FIELD=(value,POSITION=2,SEPARATOR=\",\",SDEF=\"Value=BC\")<\/pre>\n
XLSX Output:<\/p>\n
\"\"<\/a><\/p>\n
XLS Output:<\/p>\n
\"\"<\/a><\/p>\n
Example #2:<\/strong> Synthesizing Random Data with encrypted fields<\/p>\n
This hybrid RowGen-FieldSheld job demonstrates random selection of names and email domains from set files and the subsequent masking of those names with format-preserving AES256 encryption with an encrypted passphrase. An email address is generated using the first letter of the first name plus the last name. The output is to a specific range of an XLS sheet.<\/i><\/p>\n
\/INFILES=random_file_placeholder\r\n\u00a0\u00a0\u00a0 \/PROCESS=RANDOM\r\n\u00a0\u00a0\u00a0 \/INCOLLECT=100\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRSTNAME, TYPE=ALPHA_DIGIT, POSITION=1, SEPARATOR=\"\\t\", SET=\"C:\/IRI\/cosort100\/sets\/names\/names_first.set\" SELECT=ANY)\r\n\u00a0\u00a0\u00a0 \/FIELD=(LASTNAME, TYPE=ALPHA_DIGIT, POSITION=2, SEPARATOR=\"\\t\", SET=\"C:\/IRI\/cosort100\/sets\/names\/names_last.set\" SELECT=ANY)\r\n\u00a0\u00a0\u00a0 \/FIELD=(EMAIL, TYPE=ALPHA_DIGIT, POSITION=3, SEPARATOR=\"\\t\", SET=\"C:\/IRI\/cosort100\/sets\/free_email_domains.set\" SELECT=ANY)\r\n\u00a0\u00a0\u00a0 \/INREC\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRSTNAME, TYPE=ALPHA_DIGIT, POSITION=1, SEPARATOR=\"\\t\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LASTNAME, TYPE=ALPHA_DIGIT, POSITION=2, SEPARATOR=\"\\t\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(EMAIL, TYPE=ALPHA_DIGIT, POSITION=3, SEPARATOR=\"\\t\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRSTINITIAL=sub_string(FIRSTNAME,1,1),TYPE=ALPHA_DIGIT, POSITION=4, SEPARATOR=\"\\t\")\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\"'Sheet1'!B1:D100;PERINFO.xls\"\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLS\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRSTNAMEENC=enc_fp_aes256_alphanum(FIRSTNAME, \"epass:mfvndoTjj8PnGCVCB9pU0Q==\"), TYPE=ALPHA_DIGIT, POSITION=1, SEPARATOR=\"\\t\", SDEF=\"=B\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LASTNAMEENC=enc_fp_aes256_alphanum(LASTNAME, \"epass:mfvndoTjj8PnGCVCB9pU0Q==\"), TYPE=ALPHA_DIGIT, POSITION=2, SEPARATOR=\"\\t\", SDEF=\"=C\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(EMAIL_FORMATTED= format_strings(\"%s%s@%s\",FIRSTINITIAL,LASTNAME,EMAIL), TYPE=ALPHA_DIGIT, POSITION=3, SEPARATOR=\"\\t\", SDEF=\"=D\")<\/pre>\n
Set files:<\/p>\n
\"\"<\/a><\/p>\n
Output:<\/p>\n
\"\"<\/a><\/p>\n
Example #3:<\/strong> Move flat, fixed data to a specific section of an Excel sheet<\/p>\n
This NextForm data migration script reads a flat file with fixed position field data and maps up to 44 rows to an XLS sheet between the BA and BD column range. The header row of column names is derived from the first half of the SDEF attribute in the \/OUTFILE field statement. It also demonstrates that the SDEF argument is not case sensitive.<\/i><\/p>\n
# Three character columns are not supported by XLS- the maximum column value is IV\r\n# Header row value specified as the first part of the SDEF. After the equals sign is the column letter.\r\n\/INFILE=chiefs\r\n    \/PROCESS=RECORD\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=1,SIZE=27)\r\n\u00a0\u00a0\u00a0 \/FIELD=(year,POSITION=28,SIZE=12)\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=40,SIZE=5)\r\n\u00a0\u00a0\u00a0 \/FIELD=(state,POSITION=45,SIZE=2)\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\u201d'Sheet1'!BA1:BD44,HEADER;chiefsAllXLSeasy.xls\u201d\r\n\/PROCESS=XLS\r\n\/FIELD=(name,POSITION=1,SEPARATOR=\u201d\\t\u201d,SDEF=\"NAME=BA\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(year,POSITION=2, SEPARATOR=\u201d\\t\u201d,SDef=\"YEAR=BB\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=3,SEPARATOR=\u201d\\t\u201d,sdef=\"PARTY=BC\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(state,POSITION=4,SEPARATOR=\u201d\\t\u201d,sDEF=\"STATE=BD\")<\/pre>\n
Input File:<\/p>\n
\"\"<\/a><\/p>\n
Output Sheet:<\/p>\n
\"\"<\/a><\/p>\n
Example #4:<\/strong>\u00a0 Sort data from specific range of XLS sheet, select only one field to actually be output<\/p>\n
# CoSort SortCL Example Job\r\n# Extracts & Sorts Selected Sheet Columns, Outputs to Pipe<\/i>\r\n\/INFILE=\u201d'Sheet1'!B1:E43;chiefsALLXLS.xls\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLS\r\n\u00a0\u00a0\u00a0 # select only two input columns from a dataset that spans more than those columns.\r\n\u00a0\u00a0\u00a0 \/FIELD=(year,POSITION=1,SEPARATOR=\",\",SDEF=\"=C\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=2,SEPARATOR=\",\",SDEF=\"=D\")\r\n\r\n\/SORT\r\n\u00a0\u00a0\u00a0 \/KEY=(year)\r\n\r\n# sort by year field, but only output party field, with a maximum size of 5 characters.\r\n\/OUTFILE=stdout\r\n    \/PROCESS=RECORD\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=1,SIZE=5)<\/pre>\n
Result:<\/p>\n
\"\"<\/a><\/p>\n
Example #5:<\/strong> Generates Sorted Test Data and Populates Excel in a \u201cvertical\u201d or inverted fashion.<\/p>\n
\/INFILES=random_file_placeholder\r\n\u00a0\u00a0\u00a0 \/PROCESS=RANDOM\r\n\u00a0\u00a0\u00a0 \/INCOLLECT=10\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, TYPE=ALPHA_DIGIT, POSITION=1, SIZE=6, SEPARATOR=\",\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, TYPE=ASCII, POSITION=2, SIZE=7, SEPARATOR=\",\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, TYPE=UPPERCASE, POSITION=3, SIZE=5, SEPARATOR=\",\")\r\n\r\n\/SORT\r\n\u00a0\u00a0\u00a0 \/KEY=(FIELD1)\r\n\r\n\/OUTFILE=\u201d'Sheet1'!A1:K3,HEADER,V;rgen.xls\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLS\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, TYPE=ALPHA_DIGIT, POSITION=1, SIZE=6, SEPARATOR=\",\",SDEF=\"FIELD1=1\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, TYPE=ASCII, POSITION=2, SIZE=7, SEPARATOR=\",\",SDEF=\"FIELD2=2\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, TYPE=UPPERCASE, POSITION=3, SIZE=5, SEPARATOR=\",\",SDEF=\"FIELD3=3\")<\/pre>\n
Result:<\/p>\n
\"\"<\/a><\/p>\n
Example #6:<\/strong> \u00a0Takes PII from a text tab separated file, writes an encrypted\/pseudonymized version to one .xlsx file, and an unmodified version to another .xlsx file. The epass, or encrypted password, obscures the clear text encryption passphrase in the script.<\/p>\n
\/INFILE=personal_info\r\n\u00a0\u00a0\u00a0 \/FIELD=(credit_card,POSITION=1,SEPARATOR='\\t')\r\n\u00a0\u00a0\u00a0 \/FIELD=(driv_lic,POSITION=2,SEPARATOR='\\t')\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=3,SEPARATOR='\\t')\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\u201d'Sheet1'!MMM1:MMO10;personal_info_encrypted.xlsx\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(credit_card1=enc_fp_aes256_alphanum(credit_card,\"epass:Gg87jCTR15Jro4AGE44ENw==\"),POSITION=1,SEPARATOR='\\t',SDEF=\"=MMM\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(driv_lic1=enc_fp_aes256_alphanum(driv_lic,\"epass:Gg87jCTR15Jro4AGE44ENw==\"),POSITION=2,SEPARATOR='\\t',SDEF=\"=MMN\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=3,SEPARATOR='\\t',SDEF=\"=MMO\",SET=C:\\IRI\\cosort100\\sets\\names\\names_first_last.set)\r\n\r\n\/OUTFILE=\u201d'Sheet1'!M1:O10;personal_info_unencrypted.xlsx\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(credit_card,POSITION=1,SEPARATOR='\\t',SDEF=\"=M\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(driv_lic,POSITION=2,SEPARATOR='\\t',SDEF=\"=N\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=3,SEPARATOR='\\t',SDEF=\"=O\")<\/pre>\n
Input data (tab-separated text file):<\/p>\n
9654-4338-8732-8128\u00a0\u00a0\u00a0 W389-324-33-473-Q\u00a0\u00a0\u00a0 Jessica Steffani\r\n2312-7218-4829-0111\u00a0\u00a0\u00a0 H583-832-87-178-P\u00a0\u00a0\u00a0 Cody Blagg\r\n8940-8391-9147-8291\u00a0\u00a0\u00a0 E372-273-92-893-G\u00a0\u00a0\u00a0 Jacob Blagg\r\n6438-8932-2284-6262\u00a0\u00a0\u00a0 L556-731-91-842-J\u00a0\u00a0\u00a0 Just Rushlo\r\n8291-7381-8291-7489\u00a0\u00a0\u00a0 G803-389-53-934-J\u00a0\u00a0\u00a0 Maria Sheldon\r\n7828-8391-7737-0822\u00a0\u00a0\u00a0 K991-892-02-578-O\u00a0\u00a0\u00a0 Keenan Ross\r\n7834-5445-7823-7843\u00a0\u00a0\u00a0 F894-895-10-215-N\u00a0\u00a0\u00a0 Francesca Leonie\r\n8383-9745-1230-4820\u00a0\u00a0\u00a0 M352-811-49-765-N\u00a0\u00a0\u00a0 Nadia Elyse\r\n3129-3648-3589-0848\u00a0\u00a0\u00a0 S891-915-48-653-E\u00a0\u00a0\u00a0 Gordon Cade\r\n0583-7290-7492-8375\u00a0\u00a0\u00a0 Z538-482-61-543-M\u00a0\u00a0\u00a0 Hanna Fay<\/pre>\n
Output:<\/p>\n
Encrypted\/Pseudonymized:<\/p>\n
\"\"<\/a><\/p>\n
Unencrypted:<\/p>\n
\"\"<\/a><\/p>\n
See this article<\/a> to compare this FieldShield-specific approach with other IRI tools for masking data in Excel.<\/p>\n
Example #7:<\/strong> This IRI RowGen example demonstrates the synthesis of 5,000 rows of test data with a header, targeting both XLS and XLSX files based on random value lookups into static inline sets and set files.<\/p>\n
\/INFILES=random_file_placeholder\r\n\u00a0\u00a0\u00a0 \/PROCESS=RANDOM\r\n\u00a0\u00a0\u00a0 \/INCOLLECT=5000\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, TYPE=ASCII, POSITION=1, SEPARATOR=\"\\t\",SET={Dolphin,Fish,Sloth})\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, TYPE=ASCII, POSITION=2, SEPARATOR=\"\\t\",SET=\u201dmoreAnimals.txt\u201d)\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, TYPE=ASCII, POSITION=3, SEPARATOR=\"\\t\",SET={Red,Orange,Yellow,Blue})\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\u201d'Sheet1'!A1:C5001,H;rgen.xlsx\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, TYPE=ASCII, POSITION=1, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Animal=A\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, TYPE=ASCII, POSITION=2, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Another Animal=B\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, TYPE=ASCII, POSITION=3, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Color=C\")\r\n\r\n\/OUTFILE=\u201d'Sheet1'!A1:C5001,H;rgen.xls\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLS\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, TYPE=ASCII, POSITION=1, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Animal=A\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, TYPE=ASCII, POSITION=2, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Another Animal=B\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, TYPE=ASCII, POSITION=3, FRAME='\"',SEPARATOR=\"\\t\",SDEF=\"Color=C\")<\/pre>\n
Example #8:<\/strong> Unicode Example with no file line arguments and no SDEFs<\/p>\n
This IRI CoSort job script demonstrates sorting Unicode data in a flat file and outputting it into an XLSX sheet with no arguments in the file line and no SDEFs:<\/i><\/p>\n
\/INFILE=chiefs10utf8.txt\r\n\u00a0\u00a0\u00a0 \/PROCESS=DELIMITED\r\n\u00a0\u00a0\u00a0 \/FIELD=(president,POSITION=1,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(term,POSITION=2,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=3,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(state,POSITION=4,SEPARATOR=\"|\",type=UTF8)\r\n\r\n\/SORT\r\n\u00a0\u00a0\u00a0 \/KEY=state\r\n\r\n\/OUTFILE=chiefs10XLSeasyunicode.xlsx\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(president,POSITION=1,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(term,POSITION=2,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(party,POSITION=3,SEPARATOR=\"|\",type=UTF8)\r\n\u00a0\u00a0\u00a0 \/FIELD=(state,POSITION=4,SEPARATOR=\"|\",type=UTF8)<\/pre>\n
Input:<\/p>\n
\"\"<\/a><\/p>\n
Output:<\/p>\n
\"\"<\/a><\/p>\n
Example #9:<\/b> Sorted US Presidents in Arabic<\/p>\n
This script sorts the Arabic translation of U.S. president names and outputs to a specific column in an XLSX spreadsheet, with an English header of \u201cNAME\u201d.<\/i><\/p>\n
\/INFILE=chiefs_arabic\r\n\/PROCESS=RECORD\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=1,SIZE=100,TYPE=UTF8)\r\n\r\n\/SORT\r\n\u00a0\u00a0\u00a0 \/KEY=name\r\n\r\n\/OUTFILE=\u201d'Sheet1'!BA1:BD45,HEADER;chiefsAllXLSeasy.xlsx\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(name,POSITION=1,SIZE=100,SEPARATOR=\"\\t\",SDEF=\"NAME=BA\",TYPE=UTF8)<\/pre>\n
Input data:<\/p>\n
\"\"<\/a><\/p>\n
Output sample:<\/p>\n
\"\"<\/a><\/p>\n
Example #10:<\/b> Shift data between different sheet names and ranges in XLSX and XLS, filter one field<\/p>\n
\/INFILE=\"'data'!WZX1092:XAD2092;dfdfdf.xlsx\"\r\n \u00a0\u00a0 \/PROCESS=XLSX\r\n \u00a0\u00a0 \/FIELD=(SSN, POSITION=1, SEPARATOR='\\t',SDEF=\"=WZX\")\r\n \u00a0\u00a0 \/FIELD=(FIRST_NAME, POSITION=2, SEPARATOR='\\t',SDEF=\"=WZY\")\r\n \u00a0\u00a0 \/FIELD=(LAST_NAME, POSITION=3, SEPARATOR='\\t',SDEF=\"=WZZ\")\r\n \u00a0\u00a0 \/FIELD=(EMAIL, POSITION=4, SEPARATOR='\\t',SDEF=\"=XAA\")\r\n \u00a0\u00a0 \/FIELD=(GENDER, POSITION=5, SEPARATOR='\\t',SDEF=\"=XAB\")\r\n \u00a0\u00a0 \/FIELD=(IP_ADDRESS, POSITION=6, SEPARATOR='\\t',SDEF=\"=XAC\")\r\n \u00a0\u00a0 \/FIELD=(URL, POSITION=7, SEPARATOR='\\t',SDEF=\"=XAD\")\r\n\/INCLUDE WHERE GENDER eq \"Male\"\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\"'Verylongsheetname'!EX1092:FD2092;outr.xls\"\r\n \u00a0\u00a0 \/PROCESS=XLS\r\n \u00a0\u00a0 \/FIELD=(SSN, POSITION=1, SEPARATOR='\\t',SDEF=\"=EX\")\r\n \u00a0\u00a0 # \/FIELD=(FIRST_NAME, POSITION=2, SEPARATOR='\\t',SDEF=\"=EY\")\r\n \u00a0\u00a0 # don\u2019t include commented field in output\r\n \u00a0\u00a0 \/FIELD=(LAST_NAME, POSITION=3, SEPARATOR='\\t',SDEF=\"=EZ\")\r\n \u00a0\u00a0 \/FIELD=(EMAIL, POSITION=4, SEPARATOR='\\t',SDEF=\"=FA\")\r\n \u00a0\u00a0 \/FIELD=(GENDER, POSITION=5, SEPARATOR='\\t',SDEF=\"=FB\")\r\n \u00a0\u00a0 \/FIELD=(IP_ADDRESS, POSITION=6, SEPARATOR='\\t',SDEF=\"=FC\")\r\n \u00a0\u00a0 \/FIELD=(URL, POSITION=7, SEPARATOR='\\t',SDEF=\"=FD\")<\/pre>\n
Resulting Output:<\/p>\n
\"\"<\/a><\/p>\n
Example # 11:<\/strong> This script demonstrates writing additional data to the same XLSX file as the data was read from, with an added header in the appended report. NOTE: This functionality is only available with the XLSX format and not the XLS format.<\/p>\n
# write a report to the same file as data was read from\r\n\/INFILE=test.xlsx\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, POSITION=1, SEPARATOR='\\t',SDEF=\"=A\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, POSITION=2, SEPARATOR='\\t',SDEF=\"=B\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, POSITION=3, SEPARATOR='\\t',SDEF=\"=C\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD4, POSITION=4, SEPARATOR='\\t',SDEF=\"=D\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD5, POSITION=5, SEPARATOR='\\t',SDEF=\"=E\")\r\n\/INCLUDE WHERE FIELD3 eq \"December\"\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\u201dG1:K351,HEADER;test.xlsx\u201d\r\n\u00a0\u00a0\u00a0 \/APPEND\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD1, POSITION=1, SEPARATOR='\\t',SDEF=\"Day=G\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD2, POSITION=2, SEPARATOR='\\t',SDEF=\"SSN=H\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD3, POSITION=3, SEPARATOR='\\t',SDEF=\"Month (December Only)=I\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD4, POSITION=4, SEPARATOR='\\t',SDEF=\"Integer=J\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIELD5, POSITION=5, SEPARATOR='\\t',SDEF=\"Decimal=K\")<\/pre>\n
Input:<\/p>\n
\"\"<\/a><\/p>\n
Output:<\/p>\n
\"\"<\/a><\/p>\n
These examples demonstrate just an inkling of what is now possible against XLS and XLSX sources and targets using SortCL. In the next article<\/a>, we will examine the xls2ddf<\/em> utility, an executable that automatically builds SortCL data definition file (DDF) metadata files (field layouts) from existing spreadsheet headers.<\/p>\n","protected":false},"excerpt":{"rendered":"
This article is a continuation of the previous article, which introduced IRI support for XLS and XLSX file formats in the SortCL program used for moving and manipulating spreadsheet data in both the IRI Voracity data management (ETL, data quality, etc.) platform, and its included fit-for-purpose products: IRI CoSort, NextForm, FieldShield and RowGen. These job<\/p>\n
Read More<\/a><\/div>\n","protected":false},"author":119,"featured_media":14538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[32,8,31,363,1,776,91,29],"tags":[100,1414,546,520,553,526,1204,598,88,620,621],"class_list":["post-14553","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-data-protection","category-data-migration","category-data-quality","category-data-transformation2","category-etl","category-iri-workbench","category-test-data","tag-etl","tag-excel","tag-iri-cosort","tag-iri-fieldshield","tag-iri-nextform","tag-iri-rowgen","tag-iri-sortcl","tag-microsoft-excel","tag-test-data-2","tag-xls","tag-xlsx"],"acf":[],"yoast_head":"\nSortCL-Compatible Excel Data Processing Examples - IRI<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SortCL-Compatible Excel Data Processing Examples - IRI\" \/>\n<meta property=\"og:description\" content=\"This article is a continuation of the previous article, which introduced IRI support for XLS and XLSX file formats in the SortCL program used for moving and manipulating spreadsheet data in both the IRI Voracity data management (ETL, data quality, etc.) platform, and its included fit-for-purpose products: IRI CoSort, NextForm, FieldShield and RowGen. These jobRead More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2021-08-05T20:33:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-08-10T15:05:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1600\" \/>\n\t<meta property=\"og:image:height\" content=\"867\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Devon Kozenieski\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Devon Kozenieski\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\"},\"author\":{\"name\":\"Devon Kozenieski\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\"},\"headline\":\"SortCL-Compatible Excel Data Processing Examples\",\"datePublished\":\"2021-08-05T20:33:28+00:00\",\"dateModified\":\"2021-08-10T15:05:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\"},\"wordCount\":618,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"keywords\":[\"ETL\",\"Excel\",\"IRI CoSort\",\"IRI FieldShield\",\"IRI NextForm\",\"IRI RowGen\",\"IRI SortCL\",\"Microsoft Excel\",\"test data\",\"xls\",\"xlsx\"],\"articleSection\":[\"Business Intelligence (BI)\",\"Data Masking\/Protection\",\"Data Migration\",\"Data Quality (DQ)\",\"Data Transformation\",\"ETL\",\"IRI Workbench\",\"Test Data\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\",\"url\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\",\"name\":\"SortCL-Compatible Excel Data Processing Examples - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"datePublished\":\"2021-08-05T20:33:28+00:00\",\"dateModified\":\"2021-08-10T15:05:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage\",\"url\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"contentUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"width\":1600,\"height\":867},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/beta.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SortCL-Compatible Excel Data Processing Examples\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#website\",\"url\":\"https:\/\/beta.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/beta.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/beta.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"width\":750,\"height\":206,\"caption\":\"IRI\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\",\"name\":\"Devon Kozenieski\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/19bf36cedac5d586ea168f58f96eb2f1?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/19bf36cedac5d586ea168f58f96eb2f1?s=96&d=blank&r=g\",\"caption\":\"Devon Kozenieski\"},\"url\":\"https:\/\/beta.iri.com\/blog\/author\/devonk\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SortCL-Compatible Excel Data Processing Examples - IRI","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/","og_locale":"en_US","og_type":"article","og_title":"SortCL-Compatible Excel Data Processing Examples - IRI","og_description":"This article is a continuation of the previous article, which introduced IRI support for XLS and XLSX file formats in the SortCL program used for moving and manipulating spreadsheet data in both the IRI Voracity data management (ETL, data quality, etc.) platform, and its included fit-for-purpose products: IRI CoSort, NextForm, FieldShield and RowGen. These jobRead More","og_url":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/","og_site_name":"IRI","article_published_time":"2021-08-05T20:33:28+00:00","article_modified_time":"2021-08-10T15:05:15+00:00","og_image":[{"width":1600,"height":867,"url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","type":"image\/png"}],"author":"Devon Kozenieski","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Devon Kozenieski","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#article","isPartOf":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/"},"author":{"name":"Devon Kozenieski","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1"},"headline":"SortCL-Compatible Excel Data Processing Examples","datePublished":"2021-08-05T20:33:28+00:00","dateModified":"2021-08-10T15:05:15+00:00","mainEntityOfPage":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/"},"wordCount":618,"commentCount":0,"publisher":{"@id":"https:\/\/beta.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage"},"thumbnailUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","keywords":["ETL","Excel","IRI CoSort","IRI FieldShield","IRI NextForm","IRI RowGen","IRI SortCL","Microsoft Excel","test data","xls","xlsx"],"articleSection":["Business Intelligence (BI)","Data Masking\/Protection","Data Migration","Data Quality (DQ)","Data Transformation","ETL","IRI Workbench","Test Data"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/","url":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/","name":"SortCL-Compatible Excel Data Processing Examples - IRI","isPartOf":{"@id":"https:\/\/beta.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage"},"thumbnailUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","datePublished":"2021-08-05T20:33:28+00:00","dateModified":"2021-08-10T15:05:15+00:00","breadcrumb":{"@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#primaryimage","url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","contentUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","width":1600,"height":867},{"@type":"BreadcrumbList","@id":"https:\/\/beta.iri.com\/blog\/etl\/sortcl-excel-processing-examples\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/beta.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SortCL-Compatible Excel Data Processing Examples"}]},{"@type":"WebSite","@id":"https:\/\/beta.iri.com\/blog\/#website","url":"https:\/\/beta.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/beta.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/beta.iri.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/beta.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/beta.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","width":750,"height":206,"caption":"IRI"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1","name":"Devon Kozenieski","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/19bf36cedac5d586ea168f58f96eb2f1?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/19bf36cedac5d586ea168f58f96eb2f1?s=96&d=blank&r=g","caption":"Devon Kozenieski"},"url":"https:\/\/beta.iri.com\/blog\/author\/devonk\/"}]}},"jetpack_featured_media_url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","_links":{"self":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553"}],"collection":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/users\/119"}],"replies":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=14553"}],"version-history":[{"count":20,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553\/revisions"}],"predecessor-version":[{"id":14647,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553\/revisions\/14647"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/media\/14538"}],"wp:attachment":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=14553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=14553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=14553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}