iData release notes - 1.8.2

Version - 1.8.2

• New
• Added SoapClient to allow for iData scripts to interact with SOAP API's
• Added Host.XmlTextToJson and Host.JsonToXmlText that allow converstion between Json and XML
• Fixed
• #257 Fixed inconsistent report rows order output

Version - 1.8.1

• New
• new data generation method GetRndRecord can be used to select multiple fields from a randomly selected lookup record
• values can be set using a 'Get' from another column when using SetText, SetInt etc
• RndRange transforms can now accept range parameters using a 'Get' from other columns
• values can be assigned using a general 'Set' that uses the data type of the parameter
• added RndDateTimeRange which returns a random date and time between the specified values
• AddressValidationAPI has an option to refresh the cache once the records are older than a configurable amount of days
• added request logging to AddressValidationAPI using LogRequestContaining config setting
• AddressValidationAPI cache is now encrypted
• MakeRndPersonCols when used for obfuscating guarantees a generated name will be different from original name
• Domains for generated email addresses have been updated to avoid clashing with real email addresses
• Limited SetMaxUniqueValues(int) to 100,000 to ensure stability when profiling
• Added ReplaceControlCharacters to remove control characters.
• Added ReplaceMultipleSpaces to replace multiple spaces.
• Added ReplaceBlanksWithNulls to replace blanks to Null.
• ConvertToText can now support non-numeric format specificer and culture code.
• Added data type validation to Csv readers when types are defined by the user, with functions to handle validation errors, OnReadErrorContinue() and OnReadErrorAbort()
• Allow scripts to prevent automatic failure of a row after validating an email or address and implement their own handling of the issue including retrieving the error message. See IValidateCondition functions
• Changed
• IMPORTANT: Selecting random values based on weighting was found to be inaccurate and has been updated. Random selections with weightings will generate a different selection of results after this change.
• when using Get to obtain a reference to a field a default value can supplied if the field isn't found
• RndDateRange returns dates with time set to midnight see RndDateTimeRange
• Improved the profiling logic for detecting a columns data type
• Parameters on Transformations and Conditions now accept column references
• Skipping a row now deletes it from the output
• Column name references available for more functions, updated list in iData Reference Manual
• SetTimeZoneOffset replaced with SetUTCTimeZoneOffset and SetLocalTimeZoneOffset
• TestDataSet values set with NextXXX or ForEachXXX are now set before all other transforms take place, rather than after, so their values can be used by other transforms
• AutoFixAddress verification now maps addresses containing a Company Name, Property Name, or Locality, to the street/town columns
• AutoFixAddress verification only replaces responses with a return value of 100 and 400 by default, WithAllowedFlags() can be used to set a custom list of allowed flags. More information on flags can be found in the docs.
• Removed Binary Transforms as they were not useful in their current form
• Amended ReplaceNearest to support default value
• Added RntIntBetween which generates values including the maximum value
• Generated credit cards now give an invalid checksum by default. Added EnableValidCheckDigit() to the tag settings toa allow valid checksums to be generated.
• Removed support for RemoveDuplicates transform
• Fixed
• Lookups from csv files will use column types defined using SetColumnToXXX
• Fixed infinite data generation when no row limit or foreach
• Report viewer now correctly reports invalid filenames instead of reporting missing files as invalid encryption keys
• Report viewer now correctly handles splitting of files correctly
• FormatText now works on fields that contain a NULL value
• Fixed error when using SetPatternLiteral
• Fixed 'UpdateColumnDefinition' which now correctly increases column sizes when ingesting csv data
• Miscapitalised column names used in SetColumnMapping no longer throw an error with counter group '???'
• AddressValidationAPI no longer requires external dependencies to run
• AddressValidationAPI will now create missing cache
• Stopped Abs transform displaying warning for positive numbers
• Fixed an issue when generating data that would cause the primary keys to be set to null
• Fixed NULL value handling when used with references to other fields
• CSV entries longer than 8000 characters will no longer throw an error
• Similar records no longer skips a row after matching a group

Version - 1.8.0

• New
• Reports are compressed and encrypted to a single .idr report file by default
• Added SetVirtualFolder to filemanager to direct all folder contents to a single report file
• Encryption keys can be created and set through a encryption key pass phrase in the user interface
• Installer now deploys Report Viewer to view .idr files
• Changed
• Profilers are now listed alphabetically
• Fixed
• Fixed issue when using multiple lookups in one script
• Fixed profile reports with group results
• Fixed opening reports on a network drive accessed via a UNC path
• Fixed AutoFix address output miscapitalising words
• Fixed error when address config not set
• Fixed regex match error when the db value is null

Version - 1.8

Version - 1.7.2

• New
• Added RestClient to allow for iData scripts to interact with Rest endpoints
• Added TextFileReader to allow for any text based file to be open and be used in a datastream
• Added TextFileWriter allowing transform engine to write to text files
• Added RestartLimit and RestartDisabled for Next lookup transforms
• Added SetTwoDigitYearMax to set the default century for two digit year date formats
• Added SetColumnToGuid to database readers to ensure unique identifiers are compared correctly
• Allow csv writers to append or overwrite existing files
• Changed
• Updated ETL wizard to EnableIdentityInsert when target has identity column.
• MakeRndAddressCols() can use existing country or region or filter by country and region with UseCurrentCountry UseCurrentCountryAndRegion options
• Removed VaryBy()
• Added foreign key references to wizard script
• SetColumnToDateTime now also takes optional DateFormat to override the default
• CreateTableIfMissing now uses the input column name not a normalised version
• AddRecord now uses Upsert when there are no comparisons
• Added column mapping to the source stream, a compare is no longer necessary to map columns, compare mapping is being deprecated
• AutoFix now capitalises location names, and places houses in address line 1
• Dates are now formatted to the local time before being written to reports
• Fixed
• Fixed data generation resetting identity columns
• RepeatRnd() changed to inclusive range to match Repeat() and missing ref bug fixed
• Allow updates to unique keys when source or destination have no rows
• Ensure error messages are reported for all row states
• Fixed error when uploading from excel sheets with numbers for sheet names
• Fixed TrackRowUpdates when used with existing data
• Fixed Importing csv file with only headers
• Fixed wild cards not applying tags to each column
• Fixed cardinality max value estimator not tracking past the set limit

Version - 1.7.1

• New
• DbWriter and SQLWriter now supports creating and updating schema using CreateTableIfMissing, CreateColumnIfMissing and UpdateColumnDefinition.
• Added ExcelWriter allowing transform engine to write to excel sheets
• Added support for password protected excel files
• Added ExtractRegex() transform which allows text matching a given regex pattern to be extracted and optionally replaced with other text
• Added ToCol() which allows text extracted from ExtractRegex() to be placed in another column
• Added Company, Email, and Phone number to random address generation
• Added SetPatternLiteral() to toggle between pattern generation and literal characters in RndPatterns()
• Added Single, Double, and Triple word occurance count to the profiling report
• Added AddressLine1, AddressLine2, and AddressLine3 for generic columns
• Added SetColumnMappingFrom() to allow the use of lookup tables to map compared columns
• Added EnableAddressFixLookupService to enable the use of AFD instead of LibPostal to clean addresses, alongside EnableAddressFixMachineLearning, default option is set in AppSettings.ini
• Added abort to transforms when too many writes fail in a row can limit can be set with SetWriterMaxWriteFail
• MakeCol now accepts Wildcards to select multiple columns at once
• MakeCol now accepts multiple columns to be passed at once
• Added ExcelReader allowing transform engine to read from excel sheets
• ExcelReader stops reading when blank rows found
• New Licencing system. Contact Intelligent Delievery Solutions for support.
• TrackRowUpdates() now writes comments to SQL, CSV and Excel files
• iData Address Validation API is now packaged with the iData install
• Added date and time to console logs
• Added new conditions to match profiler results. E.G. HasPunctuation
• Changed
• Lookup matches not support patterns
• Merged CardPrefix and CardPrefixRange for card definition in AppSettings
• Cards higher than 19 digits can now be validated
• AutoFixAddressFields now works on tables with joined number and street fields
• Reports no longer show [no value] when one of the rows is entirely missing. e.g. No Src/Dest or Add and Delete
• Column values can now be used as parameters in conditions such as Contains
• You can no longer edit unique key columns while comparing unless the destination table is empty
• Limited the UI log output to 1000 lines
• AutoFixAddressFields and VerifyAddressFields now work with group tags
• IsValid and NotValid has been added to functions VerifyEmailAddress, VerifyAddressFields, AutoFixAddressFields, CheckForeignKeys, and CheckUniqueKeys
• Generate more diverse set of company names
• Limited the UI log output to 1000 lines
• By default tables stop at the first empty row, and the first empty header if headers are set
• Fixed
• Prevent error when uniqueidentifier is table key and also using Rnd transforms
• Fixed system buffer version mismatch
• Fixed spelling on some Rnd... transforms from Rng...
• Fixed filter buttons sometimes not showing up on ETL reports
• Set RndPatterns() to accept punctuation.
• Report links with spaces now work
• Csv files with trialing spaces in headers now work
• Allow duplicate values in data for In and Replace operations
• Fixed some formatting issues in the documentation
• Data writes take column mappings into account and shows correct mapping in headers in report
• Fixed showing mapped columns in report when writing to empty table
• Fixed table name validation issue
• Speacial characters in excel sheet names now upload okay
• Address generation email column not defined and street address default seed

Version - 1.7.0

• New
• More detailed stack trace for errors
• Added NextGuid(), RndGuid() and EachGuid() transforms
• Added OnNthRow() transform
• Added ConvertToBigInt() which converts the value to a 64 bit integer
• More detailed stack trace for errors
• Added a UniquePrefixes profiler
• Added RndWords() transform which generates a random Lorum Ipsum paragraph
• Added ability for tags to be grouped together
• Added enhanced Mask() which allows full or partial masking of text with a given mask character
• Added enhanced MaskExcept() to mask all except a given range
• Added Repeat/RepeatRnd modifier(s) to Each and Next Transformations to repeat the value the requested number of times
• Added MakeKeys and ForEachRowFrom/NextRowFrom to setup keys values from a parent table
• Added default value option to conversion transformations
• Added the ability to set the seed for randomly generated data to an alternative name instead of the column name with UseSeedName()
• Added AllUppercase, AllLowercase and MixedCase profilers
• Added Flush() to reports to write any buffered output
• Added ability to set RequiredRows for a TestSet
• Added RepeatRnd() function which allows Each and Next transforms to be randomly reset
• Each functions are now known as ForEach. e.g. EachText is now ForEachText
• Added RestartRnd() which restarts a ForEach sequence after a random amount of iterations between the given minValue and maxValue
• Added SetBinary(), RndBinary(), NextBinary(), EachBinary() and EachFrom versions of these
• Added RowLimit() to Each data items to allow for generation of data for smaller sub sets
• Added SetDifferenceFilename() option to comparison options
• All transforms can now be contained within a TestDataSet()
• All TestDataSet() calls must now have an accompanying EndTestDataSet()
• Added GetCardType() to check a card number for a matching card type, returns the found type as a string
• Added MatchKeysAlphbetically() method to comparison settings to allow for keys to be compared alphanumerically
• Added the ability to enable soundex for similar record checker via similar record tags
• Added the ability to ignore words for similar record checker via similar record tags
• Added the a sort by method the similar record configuration object
• Added BlankCol() to blank selected columns
• Added UniquePatternLengthIndependent profiler
• Added MakeRndCreditCardCols() to generate credit card data on tagged columns. Additionally, SetValidCardTypes() can be used to select card types to generate.
• Changed
• Temp data is now written to local app data
• By default both MakeRndAddressCols() and MakeRndPersonCols() use predefined seeds. This can be changed with the new UseDefaultSeeds()/UseColSeeds() options
• Fixed
• Prevent error when uniqueidentifier is table key and also using Rnd transforms
• Fixed system buffer version mismatch
• Fixed spelling on some Rnd... transforms from Rng...

Version - 1.7

Version - 1.6.1

• New
• Added the ability to set a timeout when calling Execute() on a DBQuery
• Added NextIntAsc(), NextFloatAsc(), NextDecimalAsc(), NextYearAsc(), NextMonthAsc(), NextDayAsc(), NextHourAsc(), NextMinuteAsc(), NextSecondAsc() and NextMillisecondAsc() which increment a value by a given step
• Added the RngRegex transformation to randomly generate values that match the specified regular expression
• Added ability to generate synthetic data using OpenTableGenerator(). Additionally, use SetTotalRows() and UseOptimalRows to set the amount generated
• Added a new DataType profiler that reports back the type of each column profiled
• Added a new Median profiler that reports back an estimated median value when the number of unique values has been exceeded. Actual median is reported below this limit
• Added Host.NewDateTimeFormat() and Host.SetDefaultDateTimeFormat() which allow for parsing custom date formats
• Added a progress bar when the profiler is writing out html reports
• Added a new VaryBy transformation that can be used when obfuscating values
• Added OnRndRows() and OnRows() to allow for transformations to be applied on a subset of rows
• Added grouped profiling results to the profiling reports
• Added IsBlank() and NotBlank() conditional transformations
• Added Between() and NotBetween() conditional transformations
• Added option to paginate sql and csv files via a parameter on Host.WriteSqlScript() and Host.WriteCsvFile()
• Added the ability to edit and add new connections from the connection picker when running a wizard
• Added Or() conditional transformation
• Added SetBigInt() which sets the value to the given int as Int64
• Added ConvertToBigInt() which converts the value to a 64 bit integer
• Changed
• MoreThan(), Equals(), AtLeast() and other comparisons will convert text values to dates before comparing them
• NextIntFrom(), NextDoubleFrom(),NextFloatFrom(), NextTextFrom() and NextDateFrom() have been renamed to NextInt(), NextDouble() etc when using a list of values opposed to a lookup
• Null values are now included in the unique values profiler results
• Total row count is now shown next to the Unique values on the profiling report
• Now when the number of unique values passes 1000(by default) an estimation of the number of unique values is provided for the profiling reports
• Fixed
• Error message is displayed in reports when invalid column mappings are provided

Version - 1.6.0

• New
• When updating from 1.5.* to 1.6 iData needs to be uninstalled prior to updating
• Added SetWriteFailedRecords() which allows failed records to be written to a separate destination
• Added new setting to profiler results SetMaxCharLimit() which prevents the profiling of exceedingly large text values
• Added NewLookup() to Host that allows the building of lookup tables from within scripts
• Added NextIntFrom(), NextDecimalFrom(), NextDateFrom(), NextFloatFrom() and NextTextFrom() which loop through given values replacing each value in turn
• Added RndTextFrom(), RndIntFrom(), RndDecimalFrom(), RndFloatFrom(), RndDateTimeFrom() and RndTextFrom() which select a random value from provided values
• Added RndTextPattern() which converts a pattern, e.g. 'A9A 9A9', into a random string with the same pattern e.g A7A 3R8
• Added SetGenerationSeedCols() to set which column values are used as the seed for generated data
• Added RndIntRange(),RndFloatRange(),RndDoubleRange(),RndDecimalRange() and RndDateRange() used to generate random data
• Added the ability to sample datastreams with the SetSampleSize() method
• Added the ability to generate random addresses with the MakeRndAddressCols() transformation
• Added Each transforms which generate data in combination with other Each transforms. Added for Text, Int, Decimal, Float and Dates. Each transforms can be used with LookupTables using Each...From
• Changed
• Renamed AutoFixPhoneNumber() transformation to FormatPhoneNumber()

Version - 1.6

Version - 1.5.1

• Fixed
• Fixed comparison summary not reporting differences when source rows missing from destination
• Fixed row count out by 1 when reading from csv files

Version - 1.5.0

• New
• Added Profiling and Profiling wizard to discover data patterns and potential data quality issues
• Added Check for Similar Records and Similar records Wizard to search for records that could be duplicates
• Created data transform engine for ETL as well as comparison See NewDataTransform in docs
• Added transformations: ConvertToDecimal(), ConvertToText(), ConvertToDateTime(), ReformatDateTimeText()
• Added AutoCapitalise() transformation allowing for words to be automatically capitalised
• Added transformations: Trim(), TrimEnd(), TrimStart()
• Added the ability to tag columns as the following: TagAsPersonTitleName(), TagAsPersonFirstName(),TagAsPersonMiddleName(),TagAsPersonLastName()
• Intelligently clean person/name related data: AutoFixPersonFields(), DistributePersonFullName() that have been tagged as person fields
• Added EnableDomainCheck() on TagAsEmail to check if the email domain exists
• New transformations: CopyCol() and CopyColValue()
• New transformation: ReplaceWildcard()
• Added for lookup table: EnableWildcards(), EnableRegex()
• Added conditions: IsValid(), NotValid() to report if tagged columns such as emails are valid
• Added TagAsPhoneNumber() to allow tagging of phone number columns see AutoFixPhoneNumbers
• Added AutoFixPhoneNumbers() to convert phone numbers to standarised formatting
• Reports are now timestamped so results do not get lost between runs
• Added conditions: MatchesDateTimeText(format) and NotMatchDateTimeText(format)
• Added CheckForeignKey() to stream to check if all foreign keys are present
• Added CheckUniqueKey() to streams to check all key values are unique
• Added TagProfilerSetting() to allow for custom profiler settings
• An SQL output can be added for both CheckForeignKeys() and CheckUniqueKeys() via SetSqlResultsFilename()
• Common values outputted by the Profiler can be save to CSV via the WriteCommonValuesToCSV() command
• Added PadLeft() and PadRight() to add spaces to a value
• Added DifferenceFrom condition to allow for approximate string matching
• Added ConvertToJulianDateTime() and ConvertToUnixDateTime()
• The ConvertToDateTime transformation now works with unix timestamps and julian dates.
• Added SetLong(), SetBool(), SetDecimal(), SetGuid(), SetShort(), SetByte(), and SetDateTimeOffset()
• Added EnableMultipleSelections()/DisableMultipleSelections() to SetControlToListBox() for use with Wizards
• Added an Else statement for use with conditions
• The name parameter of MakeCol() can now be omitted, MakeCol() will use the column name provided in preceding IfCol() or IfSrcCol() methods
• Added ability to enable and disable warnings with via EnableLogWarnings(), DisableLogWarnings(), EnableAbortOnWarnings(), DisableAbortOnWarnings(), RestoreLogWarning()
• Added CoalesceCols() to get the first non null value from a list of columns
• Added Substring() transform to get part of a text value
• Added JoinCols() transform to combine several colums into one value
• Added InsertText() and InsertColText() transform to add text at the begining or middle of a value
• Added TagAsDomain() to allow tagging of a web domain column
• Added NewDBResultsWriter() to the Host object
• Added TrackRowUpdates() and StopTrackRowUpdates()
• Added SetDNSServer(), ExportDNSCache() and ImportDNSCache()
• Added TagAsCreditCard() and validation to check if credit card numbers are valid
• Added EnableSoundex() to lookup tables
• Added Obfuscate() transform to replace original characters with random characters
• Added Mask() transform to replace original characters with fixed characters
• Profiling Results can now be grouped by columns
• Lookup tables can now be filtered using FilterOn() and Using()
• Changed
• Database connections and application settings are now read from %LocalAppData%/iData/Settings
• Data comparison engine still works but is deprecated, use NewDataTransform instead
• NewInputStream still works but is deprecated, use NewStream instead

Version - 1.5

Version - 1.4.0

• New
• Upgrade to V8 jscript engine, for better performance, error handling and latest jscript syntax
• Doubled performance running scripts in gui with background thread
• Allow debugging scripts from VSCode. See Docs
• Allow scripts to be stopped from the UI by clicking the run button a second time
• NewDbCompare can use SetSrcData or SetDestData instead of SetSrcTable or SetDestTable. SetSrcData can be passed value from OpenCsvFile. This allows control over csv file separators and column settings
• NewDbCompare can use SetSrcQuery or SetDestQuery to allow comparison of joined tables or pre-processing of data using sql before iData transformations
• Use a lookup table from a query or csv file to replace values using ReplaceValueWithLookup
• The database definition of a Null value can now be accessed through Host.DBNull which has a different value from null
• IgnoreCase can be applied to make transformations such as Replace case insensitive and MatchCase can turn it case sensitive again
• Added SetInt, SetFloat, and SetDateTime to set a column to a specific value of the given type
• Created maths operations for Add, Subtract and Remainder
• SetMatchDistance added to allow nearest close match to data in lookup table. See Docs
• Added SetDateTimeParts to allow part of a dateTime to be changed.
• Added SetANSICodePage to allow encoding to be specified when reading CSV files.
• Changed
• Interactive scripts must have 'Wizard' as part of their filename, for automatic optimisation
• ReplaceRegex ignore case parameter deprecated in favor of IgnoreCase command
• ReplaceValueWithLookup deprecated and renamed ReplaceLookup
• Fixed
• Fixed invalid warning when comparing source query to destination csv file
• Fixed crash caused by duplicate database connection names
• Report any errors generated by wizards
• Prevent ConfirmRequiredVersion flagging identical build numbers as different
• Prevent missing dates in CSV files generating invalid SQL dateTimes
• Fixed cleanup of database connections after DbCompare

Version - 1.4

Version - 1.3.6

• New
• Add testConnection wizard. See getting started
• Accept jscript regex expressions in MatchRegex and NotMatchRegex. See Api docs
• Changed
• Database connections details reloaded on demand. No need to re-start gui to refresh
• Improved error message for script errors in comparisons
• Fixed
• Update progress report when skipping rows during comparison

Version - 1.3.5

• New
• Recored and report statitics for each column
• Accept native jscript regex expressions in MatchRegex and NotMatchRegex. See Api docs
• Remove duplicates rows before comparison. See RemoveSrcDuplicates in Api Docs
• Changed
• Limit comparison reports of mismatched rows to avoid hiding other issues
• Fixed
• Report attempts to map columns incorrectly in comparisons
• Prevent installer overwritting connection strings on re-install

Version - 1.3.4

• New
• Allow comparison to specify IncludeColumns as well as SkipColumns. See Api docs
• Allow columns to be SetNull. See api docs

Version - 1.3.3

• New
• All In to use lookup tables from CSV or queries. See API docs

Version - 1.3.2

• New
• Additional conditions In, StartsWith etc. See API docs

Version - 1.3.1

• Changed
• Improved database connection error messages

Version - 1.3.0

• New
• Support for splitting and joining columns. e.g. AddColText. See Api docs

Version - 1.3

Version - 1.2.0

• New
• Database comparison wizard
• Import CSV data for comparison

Version - 1.2

Version - 1.1.0

• New
• Database comparison
• Read/write to Excel files

Version - 1.1

Version - 1