How to open tuning pack CSV file with line breaks in Excel?

Dashboard Server
1

This may be more a Excel question rather than an Cookdown Easytune question, but it is related…

I am attempting to import the tuning pack CSV file for Microsoft SQL Server management pack. One of the column (description) has line breaks with " as the text qualifier.

I have not been able to import the CSV file successfully into Excel 365 - the line breaks causes new rows which does not contain the right number of columns.

So, how do you import the such a CSV file into Excel?

Or (which is more Easytune related) is it possible to remove the line breaks in the CSV file, and still be able to successfully import the tuning pack into Easytune?

2

I solved this one. The problem was two-sided. First, line breaks in some cells of the Description column caused Excel to not import it correctly. Secondly, when exporting to CSV again from Excel, the text qualifier was not added (" around the cell values). I found that if I added " around all cell values, then EasyTune would accept the updated CSV file. I used a macro in Excel for that part.

So what I ended up doing was this:

  1. Download tuning pack (CSV file) from EasyTune in SCOM
  2. Remove linebreaks in Description column manually in Textpad
  3. Create a new Excel file and import the CSV file via “From Text/CSV”. Make the top row header.
  4. Make the necessary overrides
  5. Save as CSV file using the macro in https://docs.microsoft.com/en-us/office/troubleshoot/excel/export-text-file-with-comma-quote (mark only the cells to export!)
  6. Copy the CSV file into the EasyTune folder (eg c:\EasyTune - Tuning Packs)
  7. Refresh in SCOM console

 

I hope this may help other in a similar situation.

3

A short update - when I perform this I have changed the VBScript a bit, to replace " with “” in cell contents. The code now looks like this:

Sub QuoteCommaExport()
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   DestFile = InputBox("Enter the destination filename" & Chr(10) & "(with complete path):", "Quote-Comma Exporter")
   FileNum = FreeFile()

   On Error Resume Next
   Open DestFile For Output As #FileNum
   
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   On Error GoTo 0

   For RowCount = 1 To Selection.Rows.Count

      For ColumnCount = 1 To Selection.Columns.Count
         Print #FileNum, """" & Replace(Selection.Cells(RowCount, ColumnCount).Text, """", """""") & """";

         If ColumnCount = Selection.Columns.Count Then
            Print #FileNum,
         Else
            Print #FileNum, ",";
         End If
      Next ColumnCount
   Next RowCount

   Close #FileNum
End Sub