Answered by:
Converting uploaded excel file into CSV

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.
- Moved by Jack Zhai-MSFTMicrosoft contingent staff Tuesday, May 14, 2013 1:53 AM Move to a more appropriate forum.
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