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