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:
- Download tuning pack (CSV file) from EasyTune in SCOM
- Remove linebreaks in Description column manually in Textpad
- Create a new Excel file and import the CSV file via “From Text/CSV”. Make the top row header.
- Make the necessary overrides
- 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!)
- Copy the CSV file into the EasyTune folder (eg c:\EasyTune - Tuning Packs)
- 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:
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
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, ",";