Automate converting CSV files to Excel files

When generating results for some data analysis, I usually output the results to CSV (comma separated values) files because it's pretty simple, especially when using a library like CsvHelper. To view these files, my colleagues (and sometimes myself) would open these files in Excel, but this can be problematic.

I've found that Excel assumes that CSV files must use a comma as a field separator (makes sense), but sometimes I'd like to use a different separator character. Also, I've also found that Excel doesn't always detect Unicode characters (e.g., Greek symbols) in the file.

There are ways to fix these problems in Excel, but it's a bit of work. Therefore, I decided to use Microsoft's Excel Model Object API to automate the conversion from a CSV file to an Excel file, while handling a custom separator character and reading Unicode.

First, use the NuGet package manager to get the Microsoft.Office.Interop.Excel library. Then use the following code to do the conversion (don't forget to add a using statement for Microsoft.Office.Interop.Excel):

var excelApp = new Application();

excelApp.Workbooks.OpenText("input.csv", 65001, 1,
    XlTextParsingType.xlDelimited,
    XlTextQualifier.xlTextQualifierNone,
    false, false, false, false, false, true, "|", Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

excelApp.ActiveWorkbook.SaveAs("output.xls", XlFileFormat.xlWorkbookDefault,
    Type.Missing, Type.Missing, false, Type.Missing,
    XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing);
excelApp.ActiveWorkbook.Close(false, Type.Missing, Type.Missing);

excelApp.Quit();

The separator character I'm using is "|" (the twelfth parameter in the OpenText method), but you may replace it with any other.

You can use this code as part of a larger application, but you may want create a separate console application that does the conversion. For example, it may take the input and output file names as command-line parameters, and perhaps the CSV separator character as well.