Converting Excel File From .csv To .xlsx

Question

I want my application to go and find a csv excel file and convert it into a .xlsx file instead.

Here's what I'm currently doing;

var fileName = @"Z:\0328\orders\PurchaseOrder.csv";
FileInfo f = new FileInfo(fileName);
f.MoveTo(Path.ChangeExtension(fileName, ".xlsx"));
var Newfile = @"Z:\0328\orders\PurchaseOrder.xlsx";

Now this does work. It changes the file extension to my desired format. However, the file then become 'corrupt' or at least Excel refuses to open it and neither will my application when I try to venture further.

Does anyone have a solution/work-around?

Answer 1

For those who want to use Interop instead of an external library, you can simply do this:

Application app = new Application();
Workbook wb = app.Workbooks.Open(@"C:\testcsv.csv", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(@"C:\testcsv.xlsx", XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();

The second argument of Workbook.SaveAs determines the true format of the file. You should make the filename extension match that format so Excel doesn't complain about corruption. You can see a list of the types and what they mean on MSDN.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

As always, please keep Microsoft's considerations in mind if this functionality is intended for a server environment. Interop may not be the way to go in that situation:

http://support.microsoft.com/kb/257757

Answer 2

I would parse in the CSV file and use this to write out an Excel file : https://github.com/JanKallman/EPPlus

Answer 3

This code should open the file you want and save it to the format without corrupting it.

1. Renames the file
2. Creates the Excel.Application instance
3. Opens the file
4. Does a save as to the desired format

5. Closes it

   using Excel = Microsoft.Office.Interop.Excel;
   
   private void Convert_CSV_To_Excel()
   {
   
       // Rename .csv To .xls
       System.IO.File.Move(@"d:\Test.csv", @"d:\Test.csv.xls");
   
       var _app = new Excel.Application();
       var _workbooks = _app.Workbooks;
   
       _workbooks.OpenText("Test.csv.xls",
                                DataType: Excel.XlTextParsingType.xlDelimited,
                                TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
                                ConsecutiveDelimiter: true,
                                Semicolon: true);
   
       // Convert To Excle 97 / 2003
       _workbooks[1].SaveAs("NewTest.xls", Excel.XlFileFormat.xlExcel5);
   
       _workbooks.Close();
   }