GetColumnValue ( fromData ; whereColumnNumber ; equalsThisValue ; returnColumnNumber ; columnSeparator ; rowSeparator )

Get values from specified column number, when a single matching value is found.
Be the first to rate this function Log in to vote
Daniel Smith - Show more from this author
Sample input:
GetColumnValue ( "1|dan|m¶2|amy|f¶3|joe|m" ; 3 ; "m" ; 2 ; "|" ; "" )
Sample output:
dan¶joe
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
/**
 * =====================================
 * GetColumnValue ( fromData ; whereColumnNumber ; equalsThisValue ; returnColumnNumber ; columnSeparator ; rowSeparator )
 *
 * RETURNS:
 *	Value from specified column number, when a single matching value is found.
 *	Will find all matching rows and return values separated by rowSeparator.
 *	Nothing when no match found.
 *
 * PARAMETERS:
 *	fromData: array of data with normalized row and column separators, like what ExecuteSQL returns
 *	whereColumnNumber: Column number containing the value to match.
 *	equalsThisValue: Entire value to be found in the column number specified in whereColumnNumber.
 *	returnColumnNumber: Column number containing the value you want retrieved.
 *	columnSeparator: Character used to separate fields in the data.
 *		will default to comma "," if empty
 *	rowSeparator: Character used to separate records in the data.
 *		will default to a carriage return "¶" if empty
 *
 * DEPENDENCIES: none
 *
 * EXAMPLE:
 *	GetColumnValue (
 *		List (
 *			"1|dan|m" ;
 *			"2|amy|f" ;
 *			"3|joe|m"
 *		) ;
 *		3 ;   // whereColumnNumber
 *		"m" ; // equalsThisValue
 *		2 ;   // returnColumnNumber
 *		"|" ; // columnSeparator
 *		""    // rowSeparator, default = ¶
 *	) = "dan¶joe"
 *
 * NOTE:
 *	If this function doesn't appear to be returning the expected results, uncomment all the lines
 *	that reference $$gcv.debug, so you can see variables as they existed while this function was
 *	running
 *
 * LINKS:
 *	https://www.briandunning.com/cf/2330
 *
 * HISTORY:
 *	Created on 2019-DEC-16 by Daniel Smith dan@filemaker.consulting
 * =====================================
 */

Case (
	/* Step 0, set-up */
	not $~gcv.step ;
		Let ( [
			$~gcv.result = "" ;
			$~gcv.occurrence = 1 ;
			columnSeparator = If ( not IsEmpty ( columnSeparator ) ;
				columnSeparator ;
				","
			) ;
			rowSeparator = If ( not IsEmpty ( rowSeparator ) ;
				rowSeparator ;
				"¶"
			) ;
			/* get count of columns from first row with columns */
			~firstColumnSep = Position ( fromData ; columnSeparator ; 1 ; 1 ) ;
			~startOfFirstRow = Position ( rowSeparator & fromData ; rowSeparator ; ~firstColumnSep ; -1 ) ;
			~endOfFirstRow = Position ( fromData & rowSeparator; rowSeparator ; ~firstColumnSep ; 1 ) ;
			$~gcv.columnCount = PatternCount (
				Middle ( fromData ; ~startOfFirstRow ; ~endOfFirstRow - ~startOfFirstRow ) ;
				columnSeparator
			) + 1 ;
			$~gcv.step = Case (
				/* skip right to step 2 when a parameter is invalid */
				IsEmpty ( fromData ) ; 2 ;
				GetAsNumber ( whereColumnNumber ) < 1 ; 2 ;
				GetAsNumber ( returnColumnNumber ) < 1 ; 2 ;
				1
			)
/*
			; $~gcv.i = 0 ; $$gcv.debug = JSONSetElement ( "{}" ;
				[ "fromData" ; fromData ; JSONString ] ;
				[ "whereColumnNumber" ; whereColumnNumber ; JSONString ] ;
				[ "equalsThisValue" ; equalsThisValue ; JSONString ] ;
				[ "returnColumnNumber" ; returnColumnNumber ; JSONString ] ;
				[ "columnSeparator" ; columnSeparator ; JSONString ] ;
				[ "rowSeparator" ; rowSeparator ; JSONString ] ;
				[ "columnCount" ; $~gcv.columnCount ; JSONNumber ]
			)
*/
		] ;
			GetColumnValue ( fromData ; whereColumnNumber ; equalsThisValue ; returnColumnNumber ; columnSeparator ; rowSeparator )
		) ;

	/* Step 1, gather values from specified column */
	$~gcv.step = 1 ;
		Let ( [
			/* pad the value with correct column and/or row separators, so the entire value is matched */
			~paddingBefore = If ( $~gcv.columnCount = 1 or whereColumnNumber = 1 ; rowSeparator ; columnSeparator ) ;
			~paddingAfter =  If ( $~gcv.columnCount = 1 or whereColumnNumber = $~gcv.columnCount ; rowSeparator ; columnSeparator ) ;
			~equalsThisValuePadded = ~paddingBefore & equalsThisValue & ~paddingAfter ;
			~pos = Position ( rowSeparator & fromData & rowSeparator ; ~equalsThisValuePadded ; 1 ; $~gcv.occurrence ) ;
//			$$gcv.debug = JSONSetElement ( $$gcv.debug ; "iterations[" & $~gcv.i & "]~pos with padding" ; ~pos ; JSONNumber ) ;
			~pos = If ( ~pos ;
				~pos - Length ( rowSeparator ) + Length ( ~paddingBefore )
			) ;

			/* if match found, check if it's in the correct column */
			~startOfRow = If ( ~pos ;
				Position ( rowSeparator & fromData ; rowSeparator ; ~pos ; -1 )
			) ;
			~column = If ( ~startOfRow ;
				PatternCount (
					Middle ( fromData ; ~startOfRow ; ~pos - ~startOfRow ) ;
					columnSeparator
				) + 1
			) ;

			/* if in correct column, extract value */
			~startOfValue = If ( ~column = whereColumnNumber ;
				If ( returnColumnNumber = 1 ;
					~startOfRow ;
					Position ( fromData ; columnSeparator ; ~startOfRow ; returnColumnNumber - 1 ) + Length ( columnSeparator )
				)
			) ;
			~endOfValue = If ( ~startOfValue ;
				If ( $~gcv.columnCount = returnColumnNumber ;
					Position ( fromData & rowSeparator ; rowSeparator ; ~startOfRow ; 1 ) ;
					Position ( fromData ; columnSeparator ; ~startOfRow ; returnColumnNumber )
				)
			) ;
			~value = If ( ~endOfValue ;
				Middle ( fromData ; ~startOfValue ; ~endOfValue - ~startOfValue )
			) ;

			$~gcv.result = If ( ~endOfValue ;
				If ( not IsEmpty ( $~gcv.result ) ; $~gcv.result & rowSeparator ) & ~value ;
				$~gcv.result
			) ;
			$~gcv.step = If ( ~pos ; 1 ; 2 ) ;
/*
			$$gcv.debug = JSONSetElement ( $$gcv.debug ;
				[ "iterations[" & $~gcv.i & "]~equalsThisValuePadded" ; ~equalsThisValuePadded ; JSONString ] ;
				[ "iterations[" & $~gcv.i & "]~pos without padding" ; ~pos ; JSONNumber ] ;
				[ "iterations[" & $~gcv.i & "]~startOfRow" ; ~startOfRow ; JSONNumber ] ;
				[ "iterations[" & $~gcv.i & "]~column" ; ~column ; JSONNumber ] ;
				[ "iterations[" & $~gcv.i & "]~startOfValue" ; ~startOfValue ; JSONNumber ] ;
				[ "iterations[" & $~gcv.i & "]~endOfValue" ; ~endOfValue ; JSONNumber ] ;
				[ "iterations[" & $~gcv.i & "]~value" ; ~value ; JSONString ]
			) ;
*/
			$~gcv.occurrence = $~gcv.occurrence + 1
		] ;
			If ( $~gcv.step = 1 ;
				GetColumnValue ( fromData ; whereColumnNumber ; equalsThisValue ; returnColumnNumber ; columnSeparator ; rowSeparator ) ;
				GetColumnValue ( "" ; "" ; "" ; "" ; "" ; "" )
			)
		) ;

	/* Step 2, clean-up and return result */
	$~gcv.step = 2 ;
		Let ( [
			~result = $~gcv.result ;

			// purge variables
			$~gcv.result = "" ;
			$~gcv.step = "" ;
			$~gcv.occurrence = "" ;
			$~gcv.columnCount = "" ;
			$~gcv.i = ""
		] ;
			~result
		)
)

/* UNIT TESTS: ================================================================
Let ( [
	// specify output format
	~outputEvaluate =
		"$output & If ( $expected = $actual ; \"pass   \" ; \"-FAIL- \" )"
		& " & $test & \"  |  \" & Quote ( $expected ) & \"  |  \" & Quote ( $actual ) & \¶" ;

	~data =
		"1,dan,m¶" &
		"2,amy,f¶" &
		"3,joe,m" ;
	colSep = "" ;
	rowSep = "" ;
	
	// return values from all positions
	$test = "default return row 1 col 1" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 1 col 3" ;
	$expected = "m" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "default return row 2 col 1" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 2 col 3" ;
	$expected = "f" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "default return row 3 col 1" ;
	$expected = "3" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 3 col 2" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default return row 3 col 3" ;
	$expected = "m" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	// match values from all positions
	$test = "default sep match row 1 col 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 2 ; "dan" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 1 col 3" ;
	$expected = "dan¶joe" ;
	$actual = GetColumnValue ( ~data ; 3 ; "m" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "default sep match row 2 col 1" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 2 ; "amy" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 2 col 3" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 3 ; "f" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "default sep match row 3 col 1" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 3 col 2" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 2 ; "joe" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "default sep match row 3 col 3" ;
	$expected = "dan¶joe" ;
	$actual = GetColumnValue ( ~data ; 3 ; "m" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1,dan¶" &
		"2,amy" ;
	
	// return values from all positions
	$test = "2 column data return row 1 col 1" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 2 col 1" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	// match values from all positions
	$test = "2 column data match row 1 col 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 2 ; "dan" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 2 col 1" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 2 ; "amy" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"dan¶" &
		"amy" ;
	
	$test = "1 column data row 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "dan" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "1 column data row 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "amy" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	
	~data =
		"1--dan--m||" &
		"2--amy--f||" &
		"3--joe--m" ;
	colSep = "--" ;
	rowSep = "||" ;
	
	// return values from all positions
	$test = "custom return row 1 col 1" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 1 col 3" ;
	$expected = "m" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "custom return row 2 col 1" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 2 col 3" ;
	$expected = "f" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "custom return row 3 col 1" ;
	$expected = "3" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 3 col 2" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom return row 3 col 3" ;
	$expected = "m" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	// match values from all positions
	$test = "custom sep match row 1 col 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 2 ; "dan" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 1 col 3" ;
	$expected = "dan||joe" ;
	$actual = GetColumnValue ( ~data ; 3 ; "m" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "custom sep match row 2 col 1" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 2 ; "amy" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 2 col 3" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 3 ; "f" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "custom sep match row 3 col 1" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 1 ; "3" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 3 col 2" ;
	$expected = "joe" ;
	$actual = GetColumnValue ( ~data ; 2 ; "joe" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "custom sep match row 3 col 3" ;
	$expected = "dan||joe" ;
	$actual = GetColumnValue ( ~data ; 3 ; "m" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1--dan||" &
		"2--amy" ;
	
	// return values from all positions
	$test = "2 column data return row 1 col 1" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 2 col 1" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data return row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	// match values from all positions
	$test = "2 column data match row 1 col 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 1 col 2" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 2 ; "dan" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 2 col 1" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "2 column data match row 2 col 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 2 ; "amy" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	$test = "invalid whereColumnNumber" ;
	$expected = "" ;
	$actual = GetColumnValue ( ~data ; "not a number" ; "amy" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "invalid returnColumnNumber" ;
	$expected = "" ;
	$actual = GetColumnValue ( ~data ; 1 ; "amy" ; "not a number" ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1--dan||" &
		"2--amy||" ;
	
	$test = "2 column w/ trailing row sep" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 2 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"dan||" &
		"amy" ;
	
	$test = "1 column data row 1" ;
	$expected = "dan" ;
	$actual = GetColumnValue ( ~data ; 1 ; "dan" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "1 column data row 2" ;
	$expected = "amy" ;
	$actual = GetColumnValue ( ~data ; 1 ; "amy" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1--b--x--b||" &
		"2--a--b--c" ;
	
	$test = "same value in multiple columns, but different rows" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 3 ; "b" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1--b----b||" &
		"2--a--b--c" ;
	
	$test = "get empty value" ;
	$expected = "" ;
	$actual = GetColumnValue ( ~data ; 2 ; "b" ; 3 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "match empty value" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 3 ; "" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"[section header]||" &
		"a--b--c" ;
	
	$test = "first row without columns match col 1" ;
	$expected = "a" ;
	$actual = GetColumnValue ( ~data ; 1 ; "a" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "first row without columns match col 2" ;
	$expected = "a" ;
	$actual = GetColumnValue ( ~data ; 2 ; "b" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "first row without columns match col 3" ;
	$expected = "a" ;
	$actual = GetColumnValue ( ~data ; 3 ; "c" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data = "" ;
	
	$test = "empty data" ;
	$expected = "" ;
	$actual = GetColumnValue ( ~data ; 1 ; "a" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	~data =
		"1---b------b||||||||" &
		"2---a---b---c" ;
	colSep = "---" ;
	rowSep = "||||||||" ;
	
	$test = "long and unequal length separators match col 1" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 1 ; "1" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match col 2" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 2 ; "b" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match col 3" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 3 ; "" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match col 4" ;
	$expected = "1" ;
	$actual = GetColumnValue ( ~data ; 4 ; "b" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match row 2 col 1" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 1 ; "2" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match row 2 col 2" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 2 ; "a" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match row 2 col 3" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 3 ; "b" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	$test = "long and unequal length separators match row 2 col 4" ;
	$expected = "2" ;
	$actual = GetColumnValue ( ~data ; 4 ; "c" ; 1 ; colSep ; rowSep ) ;
	$output = Evaluate ( ~outputEvaluate ) ;
	
	
	
	// set output to local let variable
	~output = $output ;
	// Clean up variables
	$test = "" ;
	$expected = "" ;
	$actual = "" ;
	$output = ""
] ;
	~output
)
END OF UNIT TESTS: ============================================================ */
Extract a specific value from delimited input data by matching a value in the same row. Originally written to parse the (stored) output of `GetContainerAttribute ( Containers::container ; "all" )`.
Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.
GetColumnValue ( fromData ; whereColumnNumber ; equalsThisValue ; returnColumnNumber ; columnSeparator ; rowSeparator )

Comments

Support this website.

Support this website.
