How To Convert Excel File To Csv File

Hello everybody,

today I want to document a few lines of code which can help to convert excel file to CSV file with help of OLE:

static void ConvertExcelToCsv(string excelFileName, string csvOutputFileName, int worksheetNumber = 1)
{
    if (!File.Exists(excelFileName)) throw new FileNotFoundException(excelFileName);
    if (File.Exists(csvOutputFileName)) throw new ArgumentException("File already exists: " + csvOutputFileName);
 
    var connectionString =
        $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={excelFileName};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"";
    var oleDbConnection = new OleDbConnection(connectionString);
 
    // get schema, then data
    var dataTable = new DataTable();
    try
    {
        oleDbConnection.Open();
        var schemaTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (schemaTable != null && schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
        if (schemaTable != null)
        {
            var worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
            var sql = $"select * from [{worksheet}]";
            var oleDbDataAdapter = new OleDbDataAdapter(sql, oleDbConnection);
            oleDbDataAdapter.Fill(dataTable);
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    finally
    {
        // free resources
        oleDbConnection.Close();
    }
 
    // write out CSV data
    using (var streamWriter = new StreamWriter(csvOutputFileName))
    {
        foreach (DataRow row in dataTable.Rows)
        {
            bool firstLine = true;
            foreach (DataColumn col in dataTable.Columns)
            {
                if (!firstLine) { streamWriter.Write(","); } else { firstLine = false; }
                var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                streamWriter.Write($"\"{data}\"");
            }
            streamWriter.WriteLine();
        }
    }
}

If this snippet for converting Excel files to CSV using OLE in Acumatica sparked your interest, imagine what tailored customizations could do for your business! Whether you need advanced data processing, seamless integrations, or unique workflows, our team is ready to bring your vision to life.

Leave a customization request today and let’s transform your Acumatica experience into something truly extraordinary. Your business deserves solutions that work as hard as you do—let’s build them together!