From c965b60b6ec45f56aa406296b5c35f48360f175a Mon Sep 17 00:00:00 2001 From: Bibiko Date: Sat, 23 Oct 2010 22:18:53 +0000 Subject: =?UTF-8?q?=E2=80=A2=20CSV=20Import=20Field=20Mapper=20Sheet=20-?= =?UTF-8?q?=20added=20the=20possibility=20to=20apply=20any=20sql=20functio?= =?UTF-8?q?n=20while=20importing=20set=20via=20"Add=20value=20or=20express?= =?UTF-8?q?ion"=20sheet=20eg=20concat('$2',=20'$1',=20length('$3'))=20wher?= =?UTF-8?q?eby=20$1=20$2=20$3=20are=20the=20placeholder=20for=20the=20csv?= =?UTF-8?q?=20file=20columns=201=202=203=20[first=20column=20starts=20with?= =?UTF-8?q?=201]=20=E2=80=A2=20fixed=20'=20escaping=20issue=20in=20MCPConn?= =?UTF-8?q?ection?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- Source/SPDataImport.m | 92 +++++++++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 86 insertions(+), 6 deletions(-) (limited to 'Source/SPDataImport.m') diff --git a/Source/SPDataImport.m b/Source/SPDataImport.m index a1051c8c..61698b46 100644 --- a/Source/SPDataImport.m +++ b/Source/SPDataImport.m @@ -1259,6 +1259,7 @@ NSInteger mapColumn; id cellData; NSInteger mappingArrayCount = [fieldMappingArray count]; + NSString *re = @"(?= numberOfImportDataColumns) { - // Global variables are coming wrapped in ' ' if there're not marked as SQL - [setString appendString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + NSMutableString *globalVar = [NSMutableString string]; + if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else { + [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + // Global variables are coming wrapped in ' ' if there're not marked as SQL. + // If global variable contains column placeholders $1 etc. replace them. + if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { + while([globalVar isMatchedByRegex:re]) { + [globalVar flushCachedRegexData]; + NSRange aRange = [globalVar rangeOfRegex:re capture:0L]; + NSInteger colIndex = [[globalVar substringWithRange:[globalVar rangeOfRegex:re capture:1L]] integerValue]; + if(colIndex > 0 && colIndex <= [csvRowArray count]) { + NSString *colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); + // escape column string for ' or " if the char just before $… is a " or ' + if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '\'') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]; + else if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '"') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"\"" withString:@"\\\""]]; + else + [globalVar replaceCharactersInRange:aRange withString:colStr]; + } else { + [globalVar replaceCharactersInRange:aRange withString:@""]; + } + } + } + } + [setString appendString:globalVar]; } else { cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn); @@ -1299,8 +1326,34 @@ // Append the data // - check for global values if(fieldMappingArrayHasGlobalVariables && mapColumn >= numberOfImportDataColumns) { - // Global variables are coming wrapped in ' ' if there're not marked as SQL - [whereString appendFormat:@"=%@", NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + NSMutableString *globalVar = [NSMutableString string]; + if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else { + [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + // Global variables are coming wrapped in ' ' if there're not marked as SQL. + // If global variable contains column placeholders $1 etc. replace them. + if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { + while([globalVar isMatchedByRegex:re]) { + [globalVar flushCachedRegexData]; + NSRange aRange = [globalVar rangeOfRegex:re capture:0L]; + NSInteger colIndex = [[globalVar substringWithRange:[globalVar rangeOfRegex:re capture:1L]] integerValue]; + if(colIndex > 0 && colIndex <= [csvRowArray count]) { + NSString *colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); + // escape column string for ' or " if the char just before $… is a " or ' + if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '\'') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]; + else if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '"') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"\"" withString:@"\\\""]]; + else + [globalVar replaceCharactersInRange:aRange withString:colStr]; + } else { + [globalVar replaceCharactersInRange:aRange withString:@""]; + } + } + } + } + [whereString appendFormat:@"=%@", globalVar]; } else { cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn); @@ -1331,6 +1384,7 @@ NSInteger mapColumn; id cellData; NSInteger mappingArrayCount = [fieldMappingArray count]; + NSString *re = @"(?= numberOfImportDataColumns) { - // Global variables are coming wrapped in ' ' if there're not marked as SQL - [valueString appendString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + NSMutableString *globalVar = [NSMutableString string]; + if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else { + [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + // Global variables are coming wrapped in ' ' if there're not marked as SQL. + // If global variable contains column placeholders $1 etc. replace them. + if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { + while([globalVar isMatchedByRegex:re]) { + [globalVar flushCachedRegexData]; + NSRange aRange = [globalVar rangeOfRegex:re capture:0L]; + NSInteger colIndex = [[globalVar substringWithRange:[globalVar rangeOfRegex:re capture:1L]] integerValue]; + if(colIndex > 0 && colIndex <= [csvRowArray count]) { + NSString *colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); + // escape column string for ' or " if the char just before $… is a " or ' + if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '\'') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]; + else if(aRange.location && [globalVar characterAtIndex:aRange.location-1] == '"') + [globalVar replaceCharactersInRange:aRange withString:[colStr stringByReplacingOccurrencesOfString:@"\"" withString:@"\\\""]]; + else + [globalVar replaceCharactersInRange:aRange withString:colStr]; + } else { + [globalVar replaceCharactersInRange:aRange withString:@""]; + } + } + } + } + [valueString appendString:globalVar]; } else { cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn); -- cgit v1.2.3