none
C# program to convert Excel file to Csv file? RRS feed

  • Question

  • How to convert Excel file to CSV format using C# Program . Please Share any code

    Thanks in Advance

    Sunday, May 6, 2012 5:48 AM

Answers

  • I could able to convert excel to csv file but output shows first column is empty and also header is missing,Any idea?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Data.OleDb;
    using System.Data;

    namespace ConsoleApplication1
    {

         

        class Class3
        {


            static void Main()
            {
                string sourceFile;
                int worksheetName;
                string targetFile;
                sourceFile = "C:\\J\\Learnings\\Test.xlsx";
                //sourceFile = "Test.xlsx";
                worksheetName = 1;
                targetFile = "C:\\J\\Learnings\\Target.csv";
                //targetFile = "Target.csv";
                //CovertExcelToCsv(sourceFile, worksheetName, targetFile);
                CovertExcelToCsv(sourceFile, targetFile, worksheetName);
            }

            static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
            {
               // if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
               // if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

                // connection string
                var cnnStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;");
                var cnn = new OleDbConnection(cnnStr);

                // get schema, then data
                var dt = new DataTable();
                try
                {
                    cnn.Open();
                    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                    string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                    string sql = String.Format("select * from [{0}]", worksheet);
                    var da = new OleDbDataAdapter(sql, cnn);
                    da.Fill(dt);
                }
                catch (Exception e)
                {
                    // ???
                    throw e;
                }
                finally
                {
                    // free resources
                    cnn.Close();
                }

                // write out CSV data
                using (var wtr = new StreamWriter(csvOutputFile))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        bool firstLine = true;
                        foreach (DataColumn col in dt.Columns)
                        {
                            if (firstLine)
                            {
                                wtr.Write(",");
                             }
                            
                            else { firstLine = false; }
                            var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                            wtr.Write(String.Format("\"{0}\"", data));
                        }
                        wtr.WriteLine();
                    }
                }
            }


        }
    }

    • Marked as answer by SP Support Monday, May 7, 2012 4:03 PM
    Sunday, May 6, 2012 3:20 PM
  • Actually your are printing a row set that do not have header information , please do folowing changes in your code

    1. To print header you need to print column names before starting outer for-each loop
    2. chnage if (firstLine) >> if (! firstLine) , you need to skip ',' on very first column
    3. see my sample modified code below
    using (var wtr = new StreamWriter(csvOutputFile))
    {
    
    // Print column
        foreach (DataColumn col in dt.Columns)
        {
           wtr.Write(col.Name);
            wtr.Write();
         }
         wtr.WriteLine("");
          foreach (DataRow row in dt.Rows)
           {
              bool firstLine = true;
              foreach (DataColumn col in dt.Columns)
               {
                   if (! firstLine)
                    {
                       wtr.Write(",");
                    }
                            
                     else { firstLine = false; }
                     var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                       wtr.Write(String.Format("\"{0}\"", data));
                    }
                    wtr.WriteLine();
                    }
                }
            }

    Hope it will help

    ashwini


    • Edited by Ashwini47 Monday, May 7, 2012 6:42 AM
    • Marked as answer by SP Support Monday, May 7, 2012 4:03 PM
    Monday, May 7, 2012 6:41 AM

All replies

  • Hi, 

    Similar question is answered here, 

    http://stackoverflow.com/questions/2536181/is-there-any-simple-way-to-convert-xls-file-to-csv-file-excel


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

    • Proposed as answer by Ashwini47 Sunday, May 6, 2012 7:44 AM
    Sunday, May 6, 2012 5:59 AM
  • Thanks for the reply. Is there any other way to achieve this requirement.I need simple c# program to convert Excel to CSV File as I am using a other platform
    • Edited by SP Support Sunday, May 6, 2012 7:48 AM
    Sunday, May 6, 2012 7:47 AM
  • There is no out of box solution , all you need to create very small exe that will work for you

    1. create a console application
    2. Add reference of Microsoft.Office.Interop.Excel.dll
    3. overwrite main function as below
    static void Main(string[] args)
            {
                string excelFile = args[0];
                string textFile = excelFile + ".csv";
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets;
                Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1];
                Microsoft.Office.Interop.Excel.Range thisCell = (Microsoft.Office.Interop.Excel.Range)CurSheet.Cells[1, 1];
                wbWorkbook.SaveAs(excelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                wbWorkbook.SaveAs(textFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    
                wbWorkbook.Close(false, "", true);
    
            }

    now run the build it now you can use this from command line like   MyConverter.exe "Myexcel.xls"

    Sunday, May 6, 2012 8:58 AM
  • I could able to convert excel to csv file but output shows first column is empty and also header is missing,Any idea?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Data.OleDb;
    using System.Data;

    namespace ConsoleApplication1
    {

         

        class Class3
        {


            static void Main()
            {
                string sourceFile;
                int worksheetName;
                string targetFile;
                sourceFile = "C:\\J\\Learnings\\Test.xlsx";
                //sourceFile = "Test.xlsx";
                worksheetName = 1;
                targetFile = "C:\\J\\Learnings\\Target.csv";
                //targetFile = "Target.csv";
                //CovertExcelToCsv(sourceFile, worksheetName, targetFile);
                CovertExcelToCsv(sourceFile, targetFile, worksheetName);
            }

            static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
            {
               // if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
               // if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

                // connection string
                var cnnStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;");
                var cnn = new OleDbConnection(cnnStr);

                // get schema, then data
                var dt = new DataTable();
                try
                {
                    cnn.Open();
                    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                    string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                    string sql = String.Format("select * from [{0}]", worksheet);
                    var da = new OleDbDataAdapter(sql, cnn);
                    da.Fill(dt);
                }
                catch (Exception e)
                {
                    // ???
                    throw e;
                }
                finally
                {
                    // free resources
                    cnn.Close();
                }

                // write out CSV data
                using (var wtr = new StreamWriter(csvOutputFile))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        bool firstLine = true;
                        foreach (DataColumn col in dt.Columns)
                        {
                            if (firstLine)
                            {
                                wtr.Write(",");
                             }
                            
                            else { firstLine = false; }
                            var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                            wtr.Write(String.Format("\"{0}\"", data));
                        }
                        wtr.WriteLine();
                    }
                }
            }


        }
    }

    • Marked as answer by SP Support Monday, May 7, 2012 4:03 PM
    Sunday, May 6, 2012 3:20 PM
  • Actually your are printing a row set that do not have header information , please do folowing changes in your code

    1. To print header you need to print column names before starting outer for-each loop
    2. chnage if (firstLine) >> if (! firstLine) , you need to skip ',' on very first column
    3. see my sample modified code below
    using (var wtr = new StreamWriter(csvOutputFile))
    {
    
    // Print column
        foreach (DataColumn col in dt.Columns)
        {
           wtr.Write(col.Name);
            wtr.Write();
         }
         wtr.WriteLine("");
          foreach (DataRow row in dt.Rows)
           {
              bool firstLine = true;
              foreach (DataColumn col in dt.Columns)
               {
                   if (! firstLine)
                    {
                       wtr.Write(",");
                    }
                            
                     else { firstLine = false; }
                     var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                       wtr.Write(String.Format("\"{0}\"", data));
                    }
                    wtr.WriteLine();
                    }
                }
            }

    Hope it will help

    ashwini


    • Edited by Ashwini47 Monday, May 7, 2012 6:42 AM
    • Marked as answer by SP Support Monday, May 7, 2012 4:03 PM
    Monday, May 7, 2012 6:41 AM
  • Kris,

    Strange enough is in this forum everything answered what has nothing to do with the .Net base class. 

    This forum is exactly for your question.

    http://social.msdn.microsoft.com/Forums/en-US/vsto/threads


    Success
    Cor

    Monday, May 7, 2012 7:06 AM
  • agree , moderators please move it
    Monday, May 7, 2012 7:17 AM
  • Thanks Ashwin.

    Using your code I am able to convert only one sheet of excel.

    Do you know how to convert all  sheets of excel to Csv. Thanks in Advance

    Monday, May 7, 2012 3:57 PM
  • Cor Ligthert,

    I agree you. I almost mention where to get precise answer after answer the post but some times i miss. thanks for reminding.


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

    Monday, May 7, 2012 4:25 PM
  • Hi ,

             Always CSV files can have only one work sheet in a workbook. So as you request, we cannot get all the sheets of an xls file to the .csv file

    Thanks!

    Rookie_MS

    Monday, November 28, 2016 11:04 AM
  • This Sample code will convert excel file to CSV , with Header included.

    static voidconvertExcelToCSV(stringsourceFile, stringworksheetName, stringtargetFile)

     {

    string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ sourceFile + @";Extended Properties=""Excel 12.0;""";

    OleDbConnectionconn = null;          

    StreamWriterwrtr = null;           

    OleDbCommandcmd = null;           

    OleDbDataAdapterda = null;          

    try           

    {

    conn =new OleDbConnection(strConn);

    conn.Open();

    cmd =

    newOleDbCommand("SELECT * FROM ["+ worksheetName + "$]", conn);

    cmd.CommandType = CommandType.Text;

    wrtr =new StreamWriter(targetFile, false, newUTF8Encoding(true));

    da =new OleDbDataAdapter(cmd);

    DataTable dt = newDataTable();

     da.Fill(dt);

    foreach(DataColumncol indt.Columns)

     {

     wrtr.Write(String.Format("\"{0}\"",col.ColumnName));

     wrtr.Write(",");

     }

    wrtr.Write("\n");

    foreach(DataRowrow indt.Rows)

     {

    bool firstLine = true;

    foreach(DataColumncol indt.Columns)

     {                      

    if(! firstLine)

    {

     wrtr.Write(",");                           

    }                      

    else

    {

    firstLine = false;

     }                       

    var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");

     wrtr.Write(String.Format("\"{0}\"", data));

    }

     wrtr.Write("\n");

    }

    Console.WriteLine();


    Console.WriteLine("Done! Your "+ sourceFile + " has been converted into "+ targetFile + ".");

                   

    Console.WriteLine();

     }          

    catch(Exceptionexc)

     {             

    Console.WriteLine(exc.ToString());             

    Console.ReadLine();

     }

    finally


    {              

    if(conn.State == ConnectionState.Open)

    conn.Close();

     conn.Dispose();

    cmd.Dispose();

     da.Dispose();

       wrtr.Close();

    wrtr.Dispose();

                }

            }

        }


    • Edited by GauravVermaOCT Wednesday, April 10, 2019 7:38 AM Identation
    Wednesday, April 10, 2019 7:32 AM