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?
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.
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