top of page

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.

Related Posts

See All

ESAPI Essentials 1.1 and 2.0

A few months ago, I introduced ESAPI Essentials—a toolkit for working with ESAPI (available via NuGet). I've recently added one major feature: asynchronous access to ESAPI for binary plugin scripts. Y

Announcement: ESAPI Subreddit

A few months ago, Matt Schmidt started the ESAPI subreddit. It's another useful resource for finding and discussing ESAPI-related topics. According to the description, This community is to post and di

Dump All Patient Data from ESAPI

If, for whatever reason, you need to dump out all of the data for a patient from ESAPI, there's a quick and dirty way to do it. Well, there are probably several ways to do it, but here's one I've foun

Komentáře


bottom of page