Reputation: 305

Export Excel sheet data to csv format

In my application i can import csv file but i have data in excel sheet so i need to convert it to csv format. i got code from net for exporting excel data to csv when i downloaded zip file of it and run that its working but its not working when i copy this program to vs 2008 and run it

The code is

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)
        {
            string sourceFile, worksheetName, targetFile;
            sourceFile = @"D:\emp.xls";worksheetName = "sheet1"; targetFile = @"D:\empcsv.csv";
            convertExcelToCSV(sourceFile, worksheetName, targetFile);
        }

        static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\" Excel.0;HDR=Yes;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();
                Console.WriteLine("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");
                Console.WriteLine();
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.ToString());
                Console.ReadLine();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Dispose();
                da.Dispose();
                wrtr.Close();
                wrtr.Dispose();
            }
        }
    }
}

its throwing error like this

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 Excel_To_csv.Program.convertExcelToCSV(String sourceFile, String worksheet
Name, String targetFile) in D:\Excel to csv\Excel To csv\Excel To csv\Program.cs
:line 41

why is this error coming i dont know

Upvotes: 4

Answers (4)

Prabhakantha

Reputation: 660

your error may be arising because of excel driver issues im not 100% sure about that however this error arising because of some required dll's are missing form you computer.

you might be able to solve this issue by installing MDAC to your computer and try to execute this.

if it's not solved you can use the below pasted code which is written by me just as a answer to your question but first of all i should tell you this below code part is not efficient if the conversion file has quite considerable number of record's ex 65000

To use the below code part you need to add below reference

using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

Function

 private void EXCELTOCSV()
    {
        OpenFileDialog excelSheetToOpen = new OpenFileDialog();
        excelSheetToOpen.Filter = "Excel 97- 2003 WorkBook (*.xls)| *.xls | Excel 2007 WorkBook (*.xlsx) | *.xlsx | All files (*.*)|*.*";
        excelSheetToOpen.FilterIndex = 3;
        excelSheetToOpen.Multiselect = false;



        if (excelSheetToOpen.ShowDialog() == DialogResult.OK)
        {

            Excel.Application excelApp = new Excel.Application();
            String workbookPath = excelSheetToOpen.FileName;
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath);
            Excel.Sheets excelWorkBookSheets = excelWorkbook.Sheets;

            Excel.Range _UsedRangeOftheWorkSheet;


            foreach (Excel.Worksheet _Sheet in excelWorkBookSheets)
            {
                if (_Sheet.Name =="ExcelSheetName")
                {

                    _UsedRangeOftheWorkSheet = _Sheet.UsedRange;

                    Object[,] s = _UsedRangeOftheWorkSheet.Value;

                    System.IO.StreamWriter sw = new System.IO.StreamWriter("FileName.csv", true);

                    for (int b = 0; b < s.Length; b++)
                    {
                        StringBuilder sb = new StringBuilder();
                        for (int c = 0; c < s.Rank; c++)
                        {
                            if (sb == null)
                            {
                                sb.Append((String)s[b, c]);
                            }
                            else
                            {
                                sb.Append("," + (String)s[b, c]);
                            }
                        }
                        sw.WriteLine(sb.ToString());
                    }
                    sw.Close();

                }
            }

        }
    }

Hope this will work for you

Thanks.

Upvotes: 1