Convert excel file (xls or xlsx) to csv file

Question

I need this code C# working for files excel 2003 and 2007 version.

I can't get this C# code working to convert excel file (xls) on csv file.

If try with excel file extension xlsx it's all ok but if try with extension xls I have error in this line:

result.Tables[0].TableName.ToString();

My code below, what's wrong?

code-behind

FileUploadControl.SaveAs(Server.MapPath("/public/") + filename);

System.IO.FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

Excel.IExcelDataReader excelReader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();

result.Tables[0].TableName.ToString();

string csvData = "";
int row_no = 0;
int ind = 0;

while (row_no < result.Tables[ind].Rows.Count)
{
    for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
    {
        csvData += result.Tables[ind].Rows[row_no][i].ToString() + ",";
    }
    row_no++;
    csvData += "\n";
}

keys = GetUniqueKey(8).ToUpper();
output = System.Web.HttpContext.Current.Server.MapPath("/public/target_" + keys.ToString() + ".csv");
StreamWriter csv = new StreamWriter(@output, false);
csv.Write(csvData);
csv.Close();

Excel 2003 has different structure than xlsx. I think you should find another solution for xls files. — hazjack, Jul 09 '15 at 11:52
Have you considered using ACE OLEDB provider to read xls/xlsx? — Matteo Umili, Jul 09 '15 at 12:02
Does this answer your question? [Is there any simple way to convert .xls file to .csv file? (Excel)](https://stackoverflow.com/questions/2536181/is-there-any-simple-way-to-convert-xls-file-to-csv-file-excel) — Michael Freidgeim, Mar 20 '20 at 10:18

score 1 · Accepted Answer · answered Jul 09 '15 at 11:55

1

Excel can be of two types:

Binary - Excel 2003 and older - xls
Zip - based on Open Office XML standards - Excel 2007 onwards - xlsx

You should try to use following for older excel format files:

ExcelReaderFactory.CreateBinaryReader(stream);

answered Jul 09 '15 at 11:55

Saagar Elias Jacky

2,684
2
14
28

score 0 · Answer 2 · answered Jul 09 '15 at 12:01

0

Use any Xls to Xslx conversation tool. You can try Aspose libraries. I think it's licensed but you can try trail version.

You can do all other conversions as well using these libraries.

answered Jul 09 '15 at 12:01

Raj Karri

551
4
19

score 0 · Answer 3 · answered Jul 09 '15 at 12:12

Here's how I do it - OLEDB - get the first sheet name, and remove all empty rows. Replace //whatever you need to do with your logic.

    //Your Method signature
    {
        //create connection string
        var connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
            ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

        //process
        using (var conn = new OleDbConnection(connStr))
        {
            conn.Open();

            //programatically get the first sheet, whatever it is named.
            var sheetName = GetSheetNames(conn)[0].SheetNameOf;

            var adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", sheetName), connStr);
            var ds = new DataSet();

            adapter.Fill(ds, "anyNameHere");

            var data = ds.Tables["anyNameHere"];

            //copy and remove blank lines
            var resData = data.Clone();
            var filteredData = data.Rows.Cast<DataRow>().Where(
                row => !row.ItemArray.All(
                    field => field is DBNull ||
                             field == null ||
                             (String.IsNullOrEmpty(field.ToString().Trim())))
                );
            filteredData.CopyToDataTable(resData, LoadOption.OverwriteChanges);

            var newData = resData.AsEnumerable();

            //whatever you need to do
    }

    public List<SheetName> GetSheetNames(OleDbConnection conn)
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        DataTable excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        var sheetNames = (from DataRow row in excelSchema.Rows
                          where !row["TABLE_NAME"].ToString().Contains("FilterDatabase")
                          select new SheetName { SheetNameOf = row["TABLE_NAME"].ToString() }
                          ).ToList();
        conn.Close();
        return sheetNames;
    }

score 0 · Answer 4 · answered Jul 09 '15 at 17:47

You can use Aspose.Cells to convert excel file like xls or xlsx into csv format with the following simple code.

string filePath = @"F:\Downloads\source.xlsx";

Workbook workbook = new Workbook(filePath);
workbook.Save("output.csv", SaveFormat.CSV);

Note: I am working as developer evangelist at Aspose.

Convert excel file (xls or xlsx) to csv file

4 Answers4

Linked