Tag: Excel

I stumbled upon this great article by Yuan Wang which allowed you to convert an XLS file into CSV in C# (Convert XLS into CSV). It was created and compiled using Visual Studio 2005. I copied the code and compiled it in VS 2008 and it compiled OK but when I ran it, I got the following exception:

C:\Documents and Settings\d205414\My Documents\Visual Studio 2008\Projects\XlsToCsv\XlsToCsv\obj\Release>XlsToCsv.exe
System.Data.OleDb.OleDbException: Could not find installable ISAM.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString cons
tr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOpti
ons options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection o
wningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection ow
ningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection ou
terConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at XlsToCsv.Program.convertExcelToCSV(String sourceFile, String worksheetName
, String targetFile) in C:\Documents and Settings\d205414\My Documents\Visual St
udio 2008\Projects\XlsToCsv\XlsToCsv\Program.cs:line 51

I made a few modifications to the source, to fix the exception but also allow the executable to accept parameters. Here’s the modified source code:

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

namespace XlsToCsv
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string sourceFile, worksheetName, targetFile, skipHeader;
                sourceFile = args[0]; worksheetName = "Sheet1"; targetFile = args[1]; skipHeader = args[2];
                convertExcelToCSV(sourceFile, worksheetName, targetFile, skipHeader);
            }
            catch
            {
                Console.WriteLine("USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]");
            }
        }

        static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile, string skipHeader)
        {
            string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + sourceFile + ";" + @"Extended Properties=""Excel 8.0;HDR=" + skipHeader + @";IMEX=1""";

            OleDbConnection conn = null;
            StreamWriter wrtr = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter da = null;

            try
            {

                conn = new OleDbConnection(strConn);
                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
                cmd.CommandType = CommandType.Text;
                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    string rowString = "";
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
                    }
                    wrtr.WriteLine(rowString);

                }

                Console.WriteLine("File converted successfully.");

            }
            catch
            {
                // Console.WriteLine(exc.ToString());
                Console.WriteLine("USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]");
                Console.ReadLine();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Dispose();
                da.Dispose();
                wrtr.Close();
                wrtr.Dispose();
            }
        }
    }
}

Save the C# code and compile in Visual Studio 2008 (I used express edition myself).

To execute, call the following in command prompt:

USAGE: XlsToCsv [XLS File] [CSV File] [Skip Header? Yes/No]

eg.

XlsToCsv source.XLS output.CSV No