// 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); }
// 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); } }
// Change the content of a Cell public String Set_Cell_Content(String InputFile, String ColumnName, int LineNumber, String NewValue) { CsvUtils cu = new CsvUtils(); cu.SetFile(InputFile); int NbOfLines = cu.Get_Number_Of_Lines(); if (LineNumber > NbOfLines) { return("Line Number exceeds the number of total lines in the input file."); } int ColIdx = cu.Get_Column_Index(ColumnName); if (ColIdx < 0) { return("Column Does Not Exist."); } cu.Set_Cell_Content(ColumnName, LineNumber, NewValue); cu.Save_File_As_CSV(InputFile); return(""); }
// 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"); } }