// Transforms the content of a specific Cell (by replacing it with a RegEx MATCH from a regular expression) public String Transform_Cell_Content(String InputFile, String ColumnName, int LineNumber, String RegExPattern) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); String MyContent = cu.Get_Cell_Content(ColumnName, LineNumber); if (MyContent != null) { String NewValue = MyContent; var pattern = @RegExPattern; var matches = Regex.Matches(MyContent, pattern); if (matches.Count > 0 && matches[0].Groups.Count > 1) { NewValue = matches[0].Groups[1].Value; } else { return("No Match Found or No Regex Group defined in Regular Expression Pattern"); } cu.Set_Cell_Content(ColumnName, LineNumber, NewValue); cu.Save_File_As_CSV(InputFile); return(NewValue); } return(null); }
// Remove String in Column Content public Boolean Remove_String_In_Column_Content(String InputFile, String ColumnName, String StringToRemove) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); int idx = cu.Get_Column_Index(ColumnName); if (idx < 0) { return(false); } foreach (KeyValuePair <int, List <String> > entry in cu.dict) { if (entry.Key > 0) // dont process the column header line { if (idx > -1) { String MyContent = cu.Get_Cell_Content(ColumnName, entry.Key); String NewValue = MyContent.Replace(StringToRemove, ""); cu.Save_Cell_Value_No_Save(ColumnName, entry.Key, NewValue); } } } cu.Save_File_As_CSV(InputFile); return(true); }
public String Get_Cell_Content(String InputFile, String ColumnName, int LineNumber) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); return(cu.Get_Cell_Content(ColumnName, LineNumber)); }
// Split Cell Value into other column public String Copy_Column_Content_To_Other_Column(String InputFile, String OriginColumn, String RegexPatternGroupToCopy, String TargetColumn) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); int idxTar = cu.Get_Column_Index(TargetColumn); int idxOri = cu.Get_Column_Index(OriginColumn); int NumberOfMatches = 0; if (idxTar > -1 && idxOri > -1) // If column exists.. { foreach (KeyValuePair <int, List <String> > entry in cu.dict) { if (entry.Key > 0) // preserving column headers { String CellContent = cu.Get_Cell_Content(OriginColumn, entry.Key); if (CellContent != null) { var pattern = RegexPatternGroupToCopy; var matches = Regex.Matches(CellContent, pattern); if (matches.Count > 0 && matches[0].Groups.Count > 1) { String ValueToCopy = matches[0].Groups[1].Value; cu.Set_Cell_Content(TargetColumn, entry.Key, ValueToCopy); NumberOfMatches++; } } else { return("Cell Content is Null"); } } } } else { return("At Least 1 Column in parameter does not exist."); } if (NumberOfMatches == 0) { return("No Match Found or No Regex Group defined in Regular Expression Pattern"); } else { cu.Save_File_As_CSV(InputFile); return("Number of Matching Cells Found and moved: " + NumberOfMatches); } }
// Transforms the content of an entire column (by replacing it with a RegEx MATCH from a regular expression) public String Transform_Column_Content(String InputFile, String ColumnName, String RegExPattern) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); int colIdx = cu.Get_Column_Index(ColumnName); if (colIdx < 0) { return("Column Does Not Exist."); } int NbOfMatches = 0; foreach (KeyValuePair <int, List <String> > entry in cu.dict) { if (entry.Key > 0) // dont process the column header line { String MyContent = cu.Get_Cell_Content(ColumnName, entry.Key); String NewValue = MyContent; var pattern = @RegExPattern; var matches = Regex.Matches(MyContent, pattern); if (matches.Count > 0 && matches[0].Groups.Count > 1) { NewValue = matches[0].Groups[1].Value; NbOfMatches++; } else { } cu.Save_Cell_Value_No_Save(ColumnName, entry.Key, NewValue); } } cu.Save_File_As_CSV(InputFile); if (NbOfMatches == 0) { return("No Match Found or No Regex Group defined in Regular Expression Pattern"); } else { return("Number of Matching Cells Found and transformed: " + NbOfMatches); } }
// Returns Y if a cell in Column A and line 4 matches a regex, otherwise returns N public String Does_Cell_Content_Match_Regex(String InputFile, String ColumnName, int LineNumber, String RegExPattern) { String IsThereMatch = "N"; CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); String MyContent = cu.Get_Cell_Content(ColumnName, LineNumber); //Console.Write("Debug:" + MyContent); if (MyContent != null) { var pattern = @RegExPattern; var matches = Regex.Matches(MyContent, pattern); if (matches.Count > 0) { IsThereMatch = "Y"; } } return(IsThereMatch); }
// Split Cell Value into other column (ex: on line 3 take the content of Column "Currency" ("Currency (USD)") and copy the regex group 1 to column "Clean_Currency") using Regex expression:" Currency \(([A-Z]{3})\)" public String Copy_Cell_Content_To_Other_Column(String InputFile, String OriginColumn, int LineNumber, String RegexPatternGroupToCopy, String TargetColumn) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); String CellContent = cu.Get_Cell_Content(OriginColumn, LineNumber); if (CellContent != null) { var pattern = RegexPatternGroupToCopy; var matches = Regex.Matches(CellContent, pattern); if (matches.Count > 0 && matches[0].Groups.Count > 1) { int idx = cu.Get_Column_Index(TargetColumn); if (idx > -1) // If column exists.. { String ValueToCopy = matches[0].Groups[1].Value; // Console.WriteLine("DEBUG: Value Extracted: " + ValueToCopy); cu.Set_Cell_Content(TargetColumn, LineNumber, ValueToCopy); cu.Save_File_As_CSV(InputFile); } else { return(""); } } else { return("No Match Found or No Regex Group defined in Regular Expression Pattern"); } return(""); } else { return("Cell Content is Null"); } }
// Transforms the content of an entire column (by replacing it with a RegEx MATCH from a regular expression) public String Split_Column_Content_based_on_matches(String InputFile, String ColumnNameToRead, String RegExPattern, String InsertAfterColumnName, String ColumnNameStub) { //Check the number of matches for each Row and retrieve the Max number(N) //Create N columns after “Insert After Column” named “Col_1”, “Col_2”, etc. //For each row, split it into the proper number of elements CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); int colIdx = cu.Get_Column_Index(ColumnNameToRead); if (colIdx < 0) { return("Column Does Not Exist: " + ColumnNameToRead); } int colIdxInsert = cu.Get_Column_Index(InsertAfterColumnName); if (colIdxInsert < 0) { return("Column Does Not Exist:" + InsertAfterColumnName); } int MaxNumberOfMatches = 0; foreach (KeyValuePair <int, List <String> > entry in cu.dict) { if (entry.Key > 0) // dont process the column header line { String MyContent = cu.Get_Cell_Content(ColumnNameToRead, entry.Key); String NewValue = MyContent; var pattern = @RegExPattern; //Console.WriteLine("Debug: Content:" + MyContent); var matches = Regex.Matches(MyContent, pattern); int NumOfMatches = matches.Count; if (NumOfMatches > MaxNumberOfMatches) { MaxNumberOfMatches = NumOfMatches; } } } //Console.WriteLine("Debug: Max Number of Matches: " + MaxNumberOfMatches); for (int i = 0; i < MaxNumberOfMatches; i++) { int tempIdx = MaxNumberOfMatches - i; //icolIdx = MaxNumberOfMatches - i; Add_Column_After(cu, InsertAfterColumnName, ColumnNameStub + tempIdx, ""); } foreach (KeyValuePair <int, List <String> > entry in cu.dict) { if (entry.Key > 0) // dont process the column header line { String MyContent = cu.Get_Cell_Content(ColumnNameToRead, entry.Key); var matches = Regex.Matches(MyContent, RegExPattern); //int NumOfMatches = matches.Count; int idxGrp = 0; foreach (Match match in matches) { idxGrp++; if (idxGrp > 0) { //Console.WriteLine("Group Number:" + idxGrp + ":" + group.Value); String CurrentCellValue = match.Groups[1].Value; //Console.WriteLine("Debug: " + CurrentCellValue); if (CurrentCellValue.Contains(',')) { // Console.WriteLine("Quote detected"); entry.Value[colIdxInsert + idxGrp] = "\"" + CurrentCellValue + "\""; } else { entry.Value[colIdxInsert + idxGrp] = match.Groups[1].Value; } } } } } cu.Save_File_As_CSV(InputFile); return(""); }