Transferring a tab separated variable file via clipboard to Excel but retaining line breaks WITHIN t-CodePudding

I am using C# Script in Tabular Editor to read a Power BI file and use clipboard to get information to Excel. Totally newbie to both so my code isn't great but works. My code is below.

For the Expression field I am using .Replace("\n"," ") to replace the line breaks with a blank space.

Otherwise the line broken text moves into the next row in Excel and doesn't align with the corresponding column anymore.

Is there a way I can achieve both i.e. replace the line breaks with something in Excel that is recognized as a multiple line but still remains with in an Excel Cell. I have googled and read multiple threads and tried \r, \x0A, CHAR(10), WrapText in Excel etc.

var tsv = "Table_Name\tTable_MeasureCount\tMeasure_Name\tMeasure_Description\tMeasure_DisplayFolder\tMeasure_IsHidden\tMeasure_DataType\tMeasure_FormatString\tMeasure_DataCategory\tMeasure_ErrorMessage\tMeasure_Expression";

foreach(var Table in Model.Tables)

    foreach(var Measure in Table.Measures)
        {
            tsv  = "\r\n"   Table.Name
              "\t"   Table.Measures.Count 
              "\t"   Measure.Name 
              "\t"   Measure.Description
              "\t"   Measure.DisplayFolder
              "\t"   Measure.IsHidden
              "\t"   Measure.DataType 
              "\t"   Measure.FormatString
              "\t"   Measure.DataCategory
              "\t"   Measure.ErrorMessage.Replace("\n"," ")
              "\t"   Measure.Expression.Replace("\n"," ");
        }

tsv.Output();

CodePudding user response：

Now we have established CSV is the best option as it allows for line-breaks, I'd suggest the following:

First create a simple function to parse a string into a safe format:

  public string stringToCsvSafe(string str)
  {
    return "\""   str.Replace("\"", "\"\"")   "\"";
  }

Then in your loop build your output string, something like this:

var newRow = new List<object>() { 
    Table.Name, 
    Table.Measures.Count, 
    stringToCsvSafe(Measure.Name), 
    stringToCsvSafe(Measure.Description),
    ...
};
csv  = string.Join(",", newRow)   "\n";

CodePudding user response：

This is the logic I ended up with (I couldn't get a function to work in Tabular Editor)

var csv = "\"Table_Name\",\"Table_MeasureCount\",\"Measure_Name\",\"Measure_Description\",\"Measure_DisplayFolder\",\"Measure_IsHidden\",\"Measure_DataType\",\"Measure_FormatString\",\"Measure_DataCategory\",\"Measure_ErrorMessage\",\"Measure_Expression\"";

string str1 = "\r\n\"";
string str2 = "\",\"";
string str3 = "\"";

//foreach(var Measure in Model.AllMeasures)
foreach(var Table in Model.Tables)
    {
    foreach(var Measure in Table.Measures)
        {
            csv  = str1   Table.Name
              str2   Table.Measures.Count
              str2   Measure.Name
              str2   Measure.Description
              str2   Measure.DisplayFolder
              str2   Measure.IsHidden
              str2   Measure.DataType
              str2   Measure.FormatString.Replace("\"","\"\"")
              str2   Measure.DataCategory
              str2   Measure.ErrorMessage.Replace("\"","\"\"")
              str2   Measure.Expression.Replace("\"","\"\"") 
              str3;
        }
    }
            
csv.Output();