none
Convert CSV file to excel XLS file RRS feed

  • Question

  • I am working on saving a .csv file as an excel .xls file using the below code. This uses assembly reference to the Microsoft.Office.Interop.Excel.dll. I am looking to explore different ways to approach this problem. Please let me know what other options are available.

    Application xlap = new Application();
    Workbook xlwb = xlap.Workbooks.Open(@"C:\temp\test.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);
    xlwb.SaveAs(@"C:\temp\test.xls", XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    xlwb.Close();
    xlap.Quit();

    Thank you in advance.


    SQLEnthusiast

    Tuesday, August 15, 2017 10:35 PM

Answers

  • Yes, you can use ADO.NET and OLEDB (either Jet or ACE depending upon the Excel format) to export from a .csv file to an Excel Workbook. You can use the Jet OLEDB Provider for .XLS and the ACE OLEDB Provider for either .XLS or the new Excel formats. Some tweaks may be required depending upon the format of the .csv:

    System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test Files\\Book20.xls;Extended Properties=Excel 8.0;");
    ExcelConnection.Open();
    System.Data.OleDb.OleDbCommand ImportCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [TextImportSheet] FROM [Text;DATABASE=C:\\Documents and Settings\\...\\My Documents\\My Database\\Text].[TextFile.csv]");
    ImportCommand.ExecuteNonQuery();
    ExcelConnection.Close();


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, August 18, 2017 12:56 PM
  • Thanks Karen & Michael for your response. Is there a way to do it natively in C# using oledb without having to use any third party dll's.

    Regards


    SQLEnthusiast


    Have not tried doing this with OleDb but know enough that the process using OleDb would entail a fair amount of code and would be a two part process, first to read the .csv file then an operation to insert the data into Excel and would be prone to errors.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, August 18, 2017 8:31 AM
    Moderator

All replies

  • One option is SpreadSheetLight. Excel need not be installed for the library to work and it's free.

    http://spreadsheetlight.com/downloads/samplecode/ImportText.cs


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, August 15, 2017 10:51 PM
    Moderator
  • Hi,

    Another alternative way is to use Spire.XLS, you can install it from Nuget, reference the dll into your project and use the following straightforward code (MS Excel is not required).

    Workbook workbook = new Workbook();
    
    //Load CSV file
    workbook.LoadFromFile(@"input.csv", ",", 1, 1);
    
    //Save it to Excel            
    workbook.SaveToFile("output.xls", ExcelVersion.Version97to2003)




    Wednesday, August 16, 2017 1:37 AM
  • Thanks Karen & Michael for your response. Is there a way to do it natively in C# using oledb without having to use any third party dll's.

    Regards


    SQLEnthusiast


    Wednesday, August 16, 2017 4:58 AM
  • Thanks Karen & Michael for your response. Is there a way to do it natively in C# using oledb without having to use any third party dll's.

    Regards


    SQLEnthusiast


    Have not tried doing this with OleDb but know enough that the process using OleDb would entail a fair amount of code and would be a two part process, first to read the .csv file then an operation to insert the data into Excel and would be prone to errors.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, August 18, 2017 8:31 AM
    Moderator
  • Yes, you can use ADO.NET and OLEDB (either Jet or ACE depending upon the Excel format) to export from a .csv file to an Excel Workbook. You can use the Jet OLEDB Provider for .XLS and the ACE OLEDB Provider for either .XLS or the new Excel formats. Some tweaks may be required depending upon the format of the .csv:

    System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test Files\\Book20.xls;Extended Properties=Excel 8.0;");
    ExcelConnection.Open();
    System.Data.OleDb.OleDbCommand ImportCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [TextImportSheet] FROM [Text;DATABASE=C:\\Documents and Settings\\...\\My Documents\\My Database\\Text].[TextFile.csv]");
    ImportCommand.ExecuteNonQuery();
    ExcelConnection.Close();


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, August 18, 2017 12:56 PM
  • Thanks karen & Paul.

    SQLEnthusiast

    Friday, August 18, 2017 4:24 PM
  • If you want to convert a CSV file to Excel formats e.g. XLS, XLSX, XLSM, XLSB etc. then it can easily be done with Aspose.Cells API.

    Please check: https://products.aspose.com/cells/net

    I have discussed this in detail in this article.

    Convert CSV to Excel in C# ( https://medium.com/@shakeelfaiz/convert-csv-to-excel-formats-2d3769bdbf57 )

    Please see the following sample code that explains how to convert CSV to Excel formats XLS, XLSX etc.

    C#

    // Load your sample CSV file inside the Workbook object.
    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(dirPath + "sampleConvertCSVToExcelFormats.csv");
    
    // Save CSV file to XLS format.
    wb.Save(dirPath + "outputConvertCSVToExcelFormats.xls", SaveFormat.Excel97To2003);
    
    // Save CSV file to XLSX format.
    wb.Save(dirPath + "outputConvertCSVToExcelFormats.xlsx", SaveFormat.Xlsx);


    Wednesday, July 18, 2018 2:13 PM
  • So this works, but it creates an additional worksheet. Is there any way that I can delete that additioal worksheet?
    Monday, March 23, 2020 1:54 PM