none
Converting uploaded excel file into CSV RRS feed

  • Question

  • How do I covert Excel to CSV

    I have the code below

     static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
            {
                if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
                if (System.IO.File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);
                
                // connection string
                var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
                var cnn = new System.Data.OleDb.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();
                    }
                }
            }
    I already have a button that selects the folder to be coverted.

    I would want to covert excel file from a folder in the project to csv and store the csv file in another folder. How would I define the path in this code.

    Friday, May 10, 2013 8:53 AM

Answers

  • Hello,

    The following is done in VB.NET (yes I realize you are using C#) which you could convert to C# or simply review the code and get an idea.

    In short you would load Excel data via OleDb into a DataTable then use MyDataTable.ToCsv language extension to save the DataRows to a file. As coded the first row is the column headers which is optional, feel free to comment this out or add a parameter to toggle using headers or not.

    ''' <summary>
    ''' Write Datatable data to a delimited text file
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="FileName">Path and file name to save data too</param>
    ''' <param name="Delimitor">Character to delimit columns</param>
    ''' <returns>Indicates success or failure</returns>
    ''' <remarks></remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String, ByVal Delimitor As String) As Boolean
        Dim Result As Boolean = True
        Dim sb As New System.Text.StringBuilder
        Try
            ' Do header row, you may not want quotes around column names
            sb.AppendLine(String.Join(",", (From T In dt.Columns.Cast(Of DataColumn)() Select Chr(34) & T.ColumnName & Chr(34)).ToArray))
            For Each row As DataRow In dt.Rows
                sb.AppendLine(String.Join(Delimitor, row.ItemArray))
            Next
            IO.File.WriteAllText(FileName, sb.ToString)
        Catch ex As Exception
            Result = False
        End Try
        Return Result
    End Function
    ''' <summary>
    ''' Write Datatable data to a delimited text file using a comma to delimit columns
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="FileName">Path and file name to save data too</param>
    ''' <returns>Indicates success or failure</returns>
    ''' <remarks></remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function ToCsv(ByVal dt As DataTable, ByVal FileName As String) As Boolean
        Return dt.ToCsv(FileName, ",")
    End Function

    If you can not convert the code try the VS2010 project here. Add the project to your solution, in the file that does the csv operation add a reference to the VS2010 library then use code as shown below, adapt as needed.

    MyDataTable.ToCsvWithOutHeaders("NoHeaders.txt")
    MyDataTable.ToCsvWithHeaders("Headers.txt")


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    • Edited by KareninstructorMVP Friday, May 10, 2013 1:24 PM Added info on class library
    • Proposed as answer by Bob Shen Wednesday, May 15, 2013 7:36 AM
    • Marked as answer by Bob Shen Friday, May 31, 2013 10:08 AM
    Friday, May 10, 2013 1:07 PM