// That's the script code:
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';
Inputdata:
LOAD * INLINE
[
ID, Customer name, Phone number, Birth date
123ABC, Fred Flintstone, 555-1234, FDJKJR@#09-06-1949#jklgerwnt
456DEF, Wilma Flintstone, 555-5678, .nmrewui93#05-01-1950#kmrem4324
ABC123, Barney Rubble, 555-9101, bhjewri432#07-03-1948#kmtjertv325
789GHI, Betty Rubble, 5551121, 324pfdskm#24-11-1947#nmn43jkj32fd
];
Cleandata:
LOAD
ID,
if(RegExTest([Phone number], '^\d{3}-\d{4}') = -1, 'Yes', 'No') as [Input phone number is valid], // Test if the phone number consists of three digits followed by a hyphen and four digits
RegExReplace([Phone number], '^(\d{3})(\d{4})', '$1-$2') as [Cleaned phone number], // Insert a hyphen between the third and fourth digit, if it is not already there
RegExReplace([Customer name], '^^(\w+)\s(\w+)', '$2, $1') as [Cleaned customer name], // Reformat the customer name in "Lastname, Firstname" format
RegExFind([Birth date] , '\d{2}-\d{2}-\d{4}', ';') as [Cleaned birth date] // Extract the birth date (a string in the form of xx-xx-xxxx) from the unclean, raw data
RESIDENT Inputdata WHERE RegExTest(ID, '^\d{3}[A-Z]{3}');
// And you need to copy this VBScript code into the Macro-Editor (CTRL-M should open it in the sheet view).
Function RegExTest(iString, Pattern, IgnoreCase)
' Returns TRUE if Pattern can be matched to iString
' iString: string, the input string to search in
' Pattern: string, the regular expression pattern to search for
' IgnoreCase: boolean, indicates if search should be case-sensitive
set RE = New RegExp
RE.Pattern = Pattern
RE.IgnoreCase = IgnoreCase
RegExTest = RE.Test(iString)
End Function
Function RegExReplace(iString, sPattern, rPattern)
' Replaces any occurence of sPattern within the string iString with rPattern
' and returns the modified string, if no match is found the original string
' is returned
' iString: string, the input string to search and replace in
' sPattern: string, the pattern to search for
' rPattern: string, the pattern to replace the found pattern with
set RE = New RegExp
RE.Pattern = sPattern
RE.Global = True
RegExReplace = RE.Replace(iString, rPattern)
End Function
Function RegExFind(iString, Pattern, Separator, IgnoreCase)
' Returns a string containing the matches that were found by searching for Pattern in iString.
' If more than 1 match was found, the results are separated by the character(s) specified in Separator
' iString: string, the input string to search
' Pattern: string, the pattern to search for
' Separator: string, the character(s) to use for separating results
' IgnoreCase: boolean, indicates if the search should be case-sensitive
set RE = New RegExp
RE.Pattern = Pattern
RE.IgnoreCase = IgnoreCase
RE.Global = True
set Found = RE.Execute(iString)
for i = 0 To Found.Count - 1
Result = Result & Found(i).Value & Separator
next
RegExFind = left(Result, len(Result)-1)
End Function
%20-%20Copy.png "tiago_hubner"){kind=avatar}
