{"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":"2025-04-25T11:29:52","modified_gmt":"2025-04-25T15:29:52","slug":"excel","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/etl\/excel\/","title":{"rendered":"Excel Data Processing Samples"},"content":{"rendered":"<p>This article is a continuation of the <a href=\"http:\/\/www.iri.com\/blog\/etl\/processing-spreadsheet-data\/\">previous article<\/a>, which introduced IRI support for XLS and XLSX file formats in the IRI <a href=\"https:\/\/www.iri.com\/products\/cosort\/sortcl\">SortCL<\/a> 4GL and runtime program used for rapidly manipulating spreadsheet data in the <a href=\"https:\/\/www.iri.com\/products\/voracity-platform\">IRI Voracity<\/a> data management (ETL, data quality, etc.) platform, and its included fit-for-purpose SortCL-powered tools:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.iri.com\/cosort\">IRI CoSort<\/a>, for high speed data transformation, cleansing and reporting<\/li>\n<li><a href=\"https:\/\/www.iri.com\/nextform\">IRI NextForm<\/a>, for data type conversion, and flat-file and database conversion<\/li>\n<li><a href=\"https:\/\/www.iri.com\/fieldshield\">FieldShield<\/a>, for flat-file and database PII masking<\/li>\n<li><a href=\"https:\/\/www.iri.com\/products\/rowgen\">RowGen<\/a>, for synthesizing test data in structured, and semi-structured files and databases<\/li>\n<\/ul>\n<p>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<h6><strong>Sample Job Scripts<\/strong><\/h6>\n<p>Note: To view XLS and XLSX files from within IRI Workbench, simply double click on the file in Project Explorer.<\/p>\n<p><b>Example #1:<\/b> Generate Test Data in Excel<\/p>\n<p><i>This script demonstrates the use of an IRI RowGen job script for synthesizing a small amount of random test data into an XLSX file, and an XLS file with a header.<\/i><\/p>\n<pre>\/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 &gt;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<p>XLSX Output:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-14585 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx.png\" alt=\"\" width=\"301\" height=\"258\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx.png 774w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx-300x257.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx-768x659.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xlsx-350x300.png 350w\" sizes=\"(max-width: 301px) 100vw, 301px\" \/><\/a><\/p>\n<p>XLS Output:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14584 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls-1024x514.png\" alt=\"\" width=\"492\" height=\"248\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls-1024x514.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls-300x151.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls-768x386.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-xls.png 1259w\" sizes=\"(max-width: 492px) 100vw, 492px\" \/><\/a><\/p>\n<p><strong>Example #2:<\/strong> Synthesizing and Encrypting Data in Excel<\/p>\n<p><i>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<pre>\/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<p>Set files:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-set-files.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-14566 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-set-files.png\" alt=\"\" width=\"628\" height=\"415\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-set-files.png 628w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-set-files-300x198.png 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/a><\/p>\n<p>Output:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-emails.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14567 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-emails-845x1024.png\" alt=\"\" width=\"551\" height=\"668\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-emails.png 845w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-emails-248x300.png 248w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-emails-768x931.png 768w\" sizes=\"(max-width: 551px) 100vw, 551px\" \/><\/a><\/p>\n<p><strong>Example #3:<\/strong> Convert Flat File Data to Excel, and Insert It<\/p>\n<p><i>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<pre># 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<p>Input File:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cheifs-infile.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14582 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cheifs-infile.png\" alt=\"\" width=\"388\" height=\"772\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cheifs-infile.png 418w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cheifs-infile-151x300.png 151w\" sizes=\"(max-width: 388px) 100vw, 388px\" \/><\/a><\/p>\n<p>Output Sheet:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14581 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs-1024x767.png\" alt=\"\" width=\"607\" height=\"455\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs-1024x767.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs-300x225.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs-768x575.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-chiefs.png 1130w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/a><\/p>\n<p><strong>Example #4:<\/strong>\u00a0 Select and Sort Data from a Range (of an XLS Sheet)<\/p>\n<pre># CoSort SortCL Example Job\r\n# <i>Extracts &amp; 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<p>Result:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cmd-results.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14568 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cmd-results.png\" alt=\"\" width=\"110\" height=\"417\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cmd-results.png 234w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-cmd-results-79x300.png 79w\" sizes=\"(max-width: 110px) 100vw, 110px\" \/><\/a><\/p>\n<p><strong>Example #5:<\/strong> Generate and Populate Sorted Test Data into Excel, Inverted<\/p>\n<pre>\/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<p>Result:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14570 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results-1024x172.png\" alt=\"\" width=\"851\" height=\"143\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results-1024x172.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results-300x50.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results-768x129.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-test-data-results.png 1599w\" sizes=\"(max-width: 851px) 100vw, 851px\" \/><\/a><\/p>\n<p><strong>Example #6:<\/strong>\u00a0 Extract and Mask Flat-File PII into Excel<\/p>\n<p>This example takes 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 key (passphrase) in the script.<\/p>\n<pre>\/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<p>Input data (tab-separated text file):<\/p>\n<pre>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<p>Output:<\/p>\n<p>Encrypted\/Pseudonymized:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14576 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted-1024x431.png\" alt=\"\" width=\"699\" height=\"294\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted-1024x431.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted-300x126.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted-768x323.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-ppii_encrypted.png 1600w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>Unencrypted:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14577 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec-1024x387.png\" alt=\"\" width=\"699\" height=\"264\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec-1024x387.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec-300x113.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec-768x290.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unec.png 1600w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>See <a href=\"https:\/\/www.iri.com\/blog\/data-protection\/iri-data-masking-tools-for-excel\/\">this article<\/a> to compare this FieldShield-specific approach with other IRI tools for masking data in Excel.<\/p>\n<p><strong>Example #7:\u00a0<\/strong>Populate XLS and XLSX Sheets with Random Test Values<\/p>\n<p>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<pre>\/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<p><strong>Example #8:\u00a0<\/strong>Sort and Insert Unicode Flat-File Data into Excel<\/p>\n<p>Unicode Example with no file line arguments and no SDEFs<\/p>\n<p>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:<\/p>\n<pre>\/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<p>Input:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-input.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14574 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-input.png\" alt=\"\" width=\"412\" height=\"166\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-input.png 573w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-input-300x121.png 300w\" sizes=\"(max-width: 412px) 100vw, 412px\" \/><\/a><\/p>\n<p>Output:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-output.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14575 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-output.png\" alt=\"\" width=\"425\" height=\"278\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-output.png 1005w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-output-300x197.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-unicode-output-768x504.png 768w\" sizes=\"(max-width: 425px) 100vw, 425px\" \/><\/a><\/p>\n<p><b>Example #9:<\/b> Sorts US Presidents in a UTF8 Arabic File into Excel<\/p>\n<p>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.<\/p>\n<pre>\/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<p>Input data:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabic-input.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14580 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabic-input-168x1024.png\" alt=\"\" width=\"160\" height=\"975\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabic-input-168x1024.png 168w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabic-input-49x300.png 49w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabic-input.png 251w\" sizes=\"(max-width: 160px) 100vw, 160px\" \/><\/a><\/p>\n<p>Output sample:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14579 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl-727x1024.png\" alt=\"\" width=\"171\" height=\"241\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl-727x1024.png 727w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl-213x300.png 213w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl-768x1082.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-arabicsortxl.png 1042w\" sizes=\"(max-width: 171px) 100vw, 171px\" \/><\/a><\/p>\n<p><b>Example #10:\u00a0<\/b>Shifts Data Between Sheets and Ranges<\/p>\n<p>This job shifts data between different sheet names and ranges in XLSX and XLS, and filters on one field.<\/p>\n<pre>\/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<p>Resulting Output:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14573 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds-1024x537.png\" alt=\"\" width=\"701\" height=\"368\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds-1024x537.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds-300x157.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds-768x403.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-capsdds.png 1110w\" sizes=\"(max-width: 701px) 100vw, 701px\" \/><\/a><\/p>\n<p><strong>Example # 11:\u00a0<\/strong>Create Header into a Sheet from Other Sheet Data<\/p>\n<p>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<pre># 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<p>Input:<\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-input.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14571 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-input.png\" alt=\"\" width=\"400\" height=\"534\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-input.png 426w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-input-225x300.png 225w\" sizes=\"(max-width: 400px) 100vw, 400px\" \/><\/a><\/p>\n<p>Output:<\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-14572 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output-1024x641.png\" alt=\"\" width=\"701\" height=\"439\" srcset=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output-1024x641.png 1024w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output-300x188.png 300w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output-768x481.png 768w, https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/compatible-excel-append-output.png 1588w\" sizes=\"(max-width: 701px) 100vw, 701px\" \/><\/a><\/p>\n<p>These were examples of the Excel data processing tools available through the SortCL-powered Voracity platform and its component products. They showed how to convert a flat file to Excel without an import stp, mask PII in Excel files, generate test data in Excel, and more.<\/p>\n<p>They are just an inkling of what is now possible against XLS and XLSX sources and targets using these &#8220;IRI ETL tools for Excel.&#8221; In the <a href=\"https:\/\/www.iri.com\/blog\/etl\/xls2ddf-metadata-conversion-utility\/\">next article<\/a>, we will examine the <em>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":"<p>This article is a continuation of the previous article, which introduced IRI support for XLS and XLSX file formats in the IRI SortCL 4GL and runtime program used for rapidly manipulating spreadsheet data in the IRI Voracity data management (ETL, data quality, etc.) platform, and its included fit-for-purpose SortCL-powered tools: IRI CoSort, for high speed<\/p>\n<div><a class=\"btn-filled btn\" href=\"https:\/\/www.iri.com\/blog\/etl\/excel\/\" title=\"Excel Data Processing Samples\">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,2121,2123,2122,2126,2125,2120,2127,2118,2124,644,2119,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-excel-data-conversion","tag-excel-data-extraction","tag-excel-data-integration","tag-excel-data-management","tag-excel-data-manipulation","tag-excel-data-masking","tag-excel-data-processing","tag-excel-etl","tag-excel-file-conversion","tag-excel-spreadsheet","tag-excel-test-data","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":"<!-- This site is optimized with the Yoast SEO Premium plugin v23.4 (Yoast SEO v23.4) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Excel Data Processing Samples - IRI<\/title>\n<meta name=\"description\" content=\"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.iri.com\/blog\/etl\/excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel Data Processing Samples\" \/>\n<meta property=\"og:description\" content=\"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.iri.com\/blog\/etl\/excel\/\" \/>\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=\"2025-04-25T15:29:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.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=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/\"},\"author\":{\"name\":\"Devon Kozenieski\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\"},\"headline\":\"Excel Data Processing Samples\",\"datePublished\":\"2021-08-05T20:33:28+00:00\",\"dateModified\":\"2025-04-25T15:29:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/\"},\"wordCount\":744,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"keywords\":[\"ETL\",\"Excel\",\"Excel data conversion\",\"Excel data extraction\",\"Excel data integration\",\"Excel data management\",\"Excel data manipulation\",\"Excel data masking\",\"Excel data processing\",\"Excel ETL\",\"Excel file conversion\",\"Excel spreadsheet\",\"Excel test data\",\"IRI CoSort\",\"IRI FieldShield\",\"IRI NextForm\",\"IRI RowGen\",\"IRI SortCL\",\"Microsoft Excel\",\"test data\",\"xls\",\"xlsx\"],\"articleSection\":[\"Business Intelligence (BI&#041;\",\"Data Masking\/Protection\",\"Data Migration\",\"Data Quality (DQ&#041;\",\"Data Transformation\",\"ETL\",\"IRI Workbench\",\"Test Data\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.iri.com\/blog\/etl\/excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/\",\"url\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/\",\"name\":\"Excel Data Processing Samples - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"datePublished\":\"2021-08-05T20:33:28+00:00\",\"dateModified\":\"2025-04-25T15:29:52+00:00\",\"description\":\"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.iri.com\/blog\/etl\/excel\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png\",\"width\":1600,\"height\":867},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.iri.com\/blog\/etl\/excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Data Processing Samples\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.iri.com\/blog\/#website\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/www.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:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\",\"name\":\"Devon Kozenieski\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g\",\"caption\":\"Devon Kozenieski\"},\"url\":\"https:\/\/www.iri.com\/blog\/author\/devonk\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel Data Processing Samples - IRI","description":"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.","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:\/\/www.iri.com\/blog\/etl\/excel\/","og_locale":"en_US","og_type":"article","og_title":"Excel Data Processing Samples","og_description":"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.","og_url":"https:\/\/www.iri.com\/blog\/etl\/excel\/","og_site_name":"IRI","article_published_time":"2021-08-05T20:33:28+00:00","article_modified_time":"2025-04-25T15:29:52+00:00","og_image":[{"width":1600,"height":867,"url":"https:\/\/www.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":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#article","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/"},"author":{"name":"Devon Kozenieski","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1"},"headline":"Excel Data Processing Samples","datePublished":"2021-08-05T20:33:28+00:00","dateModified":"2025-04-25T15:29:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/"},"wordCount":744,"commentCount":0,"publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","keywords":["ETL","Excel","Excel data conversion","Excel data extraction","Excel data integration","Excel data management","Excel data manipulation","Excel data masking","Excel data processing","Excel ETL","Excel file conversion","Excel spreadsheet","Excel test data","IRI CoSort","IRI FieldShield","IRI NextForm","IRI RowGen","IRI SortCL","Microsoft Excel","test data","xls","xlsx"],"articleSection":["Business Intelligence (BI&#041;","Data Masking\/Protection","Data Migration","Data Quality (DQ&#041;","Data Transformation","ETL","IRI Workbench","Test Data"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.iri.com\/blog\/etl\/excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/","url":"https:\/\/www.iri.com\/blog\/etl\/excel\/","name":"Excel Data Processing Samples - IRI","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage"},"image":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","datePublished":"2021-08-05T20:33:28+00:00","dateModified":"2025-04-25T15:29:52+00:00","description":"See how you can integrate Excel data, mask data in Excel, create test data for Excel, and more using IRI Voracity data management software.","breadcrumb":{"@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.iri.com\/blog\/etl\/excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#primaryimage","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","width":1600,"height":867},{"@type":"BreadcrumbList","@id":"https:\/\/www.iri.com\/blog\/etl\/excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Excel Data Processing Samples"}]},{"@type":"WebSite","@id":"https:\/\/www.iri.com\/blog\/#website","url":"https:\/\/www.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.iri.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/www.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/www.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:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1","name":"Devon Kozenieski","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g","caption":"Devon Kozenieski"},"url":"https:\/\/www.iri.com\/blog\/author\/devonk\/"}]}},"jetpack_featured_media_url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/08\/wbex-cel-capture.png","_links":{"self":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553"}],"collection":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/users\/119"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=14553"}],"version-history":[{"count":22,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553\/revisions"}],"predecessor-version":[{"id":18406,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14553\/revisions\/18406"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media\/14538"}],"wp:attachment":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=14553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=14553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=14553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}