// 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("");
}
