public static string[] CleanColumnNames(this ADOTabular.AdomdClientWrappers.AdomdDataReader reader) { string[] columns = new string[reader.FieldCount]; for (int i = 0; i < reader.FieldCount; i++) { columns[i] = reader.GetName(i); } var columnPattern = new Regex(@"\[(?<col>.*)]\d*$", RegexOptions.Compiled); var mdxPattern = new Regex(@"\[[^\]]*\].\[[^\]]*\]"); const string MEASURES_MDX = "[Measures]."; var newColumnNames = new Dictionary <string, DaxColumn>(); // If at least one column has the Mdx syntax, identify the result as an MDX query (hoping the assumption is always true...) //bool isMdxResult = (from DataColumn col in dataTable.Columns // where col.ColumnName.IndexOf("].[") > 0 // select col).Count() > 0; bool isMdxResult = (from col in columns where mdxPattern.IsMatch(col) select col).Count() > 0; var measuresColumns = (from col in columns where col.IndexOf(MEASURES_MDX) >= 0 select col); bool hasPlainMeasures = (from col in measuresColumns where col.IndexOf("].[", col.IndexOf(MEASURES_MDX) + MEASURES_MDX.Length) > 0 select col).Count() == 0; foreach (string columnName in columns) { bool removeCaption = false; string name = columnName; bool removeSquareBrackets = !isMdxResult; int measuresMdxPos = name.IndexOf(MEASURES_MDX);// + MEASURES_MDX.Length; if (isMdxResult) { if ((measuresMdxPos >= 0)) { if ((name.IndexOf("].[", measuresMdxPos + MEASURES_MDX.Length) == -1) && (name.IndexOf("].[", 0) == MEASURES_MDX.Length - 2)) { removeSquareBrackets = true; } name = name.Replace(MEASURES_MDX, measuresMdxPos > 0 ? "\n" : ""); } else { removeCaption = hasPlainMeasures; } } if (removeSquareBrackets) { var m = columnPattern.Match(name); if (m.Success) { name = m.Groups["col"].Value; } // Format column naming for DAX result or if it is a measure name //int firstBracket = name.IndexOf('[') + 1; //name = firstBracket == 0 ? name : name.Substring(firstBracket, name.Length - firstBracket - 1); } var dc = new DaxColumn() { //OriginalCaption = col.Caption, OriginalName = columnName, //NewCaption = (removeCaption) ? "" : name, NewName = name.Replace(' ', '`').Replace(',', '`'), }; newColumnNames.Add(dc.OriginalName, dc); //col.Caption = (removeCaption) ? "" : name; //col.ColumnName = name.Replace(' ', '_'); } // check for duplicate names for (var outerIdx = 0; outerIdx < newColumnNames.Count; outerIdx++) { for (var innerIdx = outerIdx + 1; innerIdx < newColumnNames.Count; innerIdx++) { if (newColumnNames.ElementAt(outerIdx).Value.NewName == newColumnNames.ElementAt(innerIdx).Value.NewName) { newColumnNames.ElementAt(outerIdx).Value.UseOriginalName = true; newColumnNames.ElementAt(innerIdx).Value.UseOriginalName = true; } } } string[] newNames = new string[columns.Length]; // Update names for (int i = 0; i < columns.Length; i++) { var c = newColumnNames.ElementAt(i).Value; newNames[i] = c.UseOriginalName ? c.OriginalName : c.NewName; } return(newNames); }
public static void FixColumnNaming(this DataTable dataTable, string query) { var columnPattern = new Regex(@"\[(?<col>.*)]\d*$", RegexOptions.Compiled); const string MEASURES_MDX = "[Measures]."; var newColumnNames = new Collection <DaxColumn>(); // If at least one column has the Mdx syntax, identify the result as an MDX query (hoping the assumption is always true...) //bool isMdxResult = (from DataColumn col in dataTable.Columns // where col.ColumnName.IndexOf("].[") > 0 // select col).Count() > 0; bool isMdxResult = IsMdxQuery(query); var measuresColumns = (from DataColumn col in dataTable.Columns where col.ColumnName.IndexOf(MEASURES_MDX) >= 0 select col); bool hasPlainMeasures = measuresColumns.Count() > 0 && (from DataColumn col in measuresColumns where col.ColumnName.IndexOf("].[", col.ColumnName.IndexOf(MEASURES_MDX) + MEASURES_MDX.Length) > 0 select col).Count() == 0; foreach (DataColumn col in dataTable.Columns) { bool removeCaption = false; string name = col.ColumnName; bool removeSquareBrackets = !isMdxResult; int measuresMdxPos = name.IndexOf(MEASURES_MDX);// + MEASURES_MDX.Length; if (isMdxResult) { if ((measuresMdxPos >= 0)) { if ((name.IndexOf("].[", measuresMdxPos + MEASURES_MDX.Length) == -1) && (name.IndexOf("].[", 0) == MEASURES_MDX.Length - 2)) { removeSquareBrackets = true; } name = name.Replace(MEASURES_MDX, measuresMdxPos > 0 ? "\n" : ""); } else { removeCaption = hasPlainMeasures; } } if (removeSquareBrackets) { var m = columnPattern.Match(name); if (m.Success) { name = m.Groups["col"].Value; } // Format column naming for DAX result or if it is a measure name //int firstBracket = name.IndexOf('[') + 1; //name = firstBracket == 0 ? name : name.Substring(firstBracket, name.Length - firstBracket - 1); } var dc = new DaxColumn() { OriginalCaption = col.Caption, OriginalName = col.ColumnName, NewCaption = (removeCaption) ? "" : name, NewName = name.Replace(' ', '`').Replace(',', '`'), }; newColumnNames.Add(dc); //col.Caption = (removeCaption) ? "" : name; //col.ColumnName = name.Replace(' ', '_'); } // check for duplicate names for (var outerIdx = 0; outerIdx < newColumnNames.Count; outerIdx++) { for (var innerIdx = outerIdx + 1; innerIdx < newColumnNames.Count; innerIdx++) { if (newColumnNames[outerIdx].NewName == newColumnNames[innerIdx].NewName) { newColumnNames[outerIdx].UseOriginalName = true; newColumnNames[innerIdx].UseOriginalName = true; } } } // Update names foreach (DaxColumn c in newColumnNames) { var dc = dataTable.Columns[c.OriginalName]; dc.Caption = c.NewCaption; if (!c.UseOriginalName) { // var dc = dataTable.Columns[c.OriginalName]; // dc.Caption = c.NewCaption; dc.ColumnName = c.NewName; } } }