Convert Excel file (XLS) to CSV


/ Published in: C#
Save to your folder(s)

Convert Excel file (XLS) to CSV


Copy this code and paste it in your HTML
  1. using System;
  2. using System.Data;
  3. using Microsoft.SqlServer.Dts.Runtime;
  4. using System.Windows.Forms;
  5. using Excel = Microsoft.Office.Interop.Excel;
  6. using Microsoft.Office.Interop.Excel;
  7. namespace ST_6dc747ff29cf41c6ac11b7c0bca33d19.csproj
  8. {
  9. [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
  10. public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  11. {
  12. #region VSTA generated code
  13. enum ScriptResults
  14. {
  15. Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
  16. Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  17. };
  18. #endregion
  19. /*
  20.   The execution engine calls this method when the task executes.
  21.   To access the object model, use the Dts property. Connections, variables, events,
  22.   and logging features are available as members of the Dts property as shown in the following examples.
  23.   To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
  24.   To post a log entry, call Dts.Log("This is my log text", 999, null);
  25.   To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
  26.   To use the connections collection use something like the following:
  27.   ConnectionManager cm = Dts.Connections.Add("OLEDB");
  28.   cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
  29.   Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
  30.  
  31.   To open Help, press F1.
  32.  */
  33. private static Workbook mWorkBook;
  34. private static Sheets mWorkSheets;
  35. private static Worksheet mWSheet1;
  36. private static Excel.Application oXL;
  37. private static string ErrorMessage = string.Empty;
  38. public void Main()
  39. {
  40. try
  41. {
  42. string sourceExcelPathAndName = @"D:\Excel Import\Excel Import.xls";
  43. string targetCSVPathAndName = @"D:\Excel Import\Excel Import.csv";
  44. string excelSheetName = @"Sheet1";
  45. string columnDelimeter = @"|#|";
  46. int headerRowsToSkip = 0;
  47. if (ConvertExcelToCSV(sourceExcelPathAndName, targetCSVPathAndName, excelSheetName, columnDelimeter, headerRowsToSkip) == true)
  48. {
  49. Dts.TaskResult = (int)ScriptResults.Success;
  50. }
  51. else
  52. {
  53. Dts.TaskResult = (int)ScriptResults.Failure;
  54. }
  55. }
  56. catch (Exception ex)
  57. {
  58. Dts.TaskResult = (int)ScriptResults.Failure;
  59. }
  60. }
  61. public static bool ConvertExcelToCSV(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip)
  62. {
  63. try
  64. {
  65. oXL = new Excel.Application();
  66. oXL.Visible = false;
  67. oXL.DisplayAlerts = false;
  68. Excel.Workbooks workbooks = oXL.Workbooks;
  69. mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
  70. //Get all the sheets in the workbook
  71. mWorkSheets = mWorkBook.Worksheets;
  72. //Get the specified sheet
  73. mWSheet1 = (Worksheet)mWorkSheets.get_Item(excelSheetName);
  74. Excel.Range range = mWSheet1.UsedRange;
  75. //deleting the specified number of rows from the top
  76. Excel.Range rngCurrentRow;
  77. for (int i = 0; i < headerRowsToSkip; i++)
  78. {
  79. rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;
  80. rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);
  81. }
  82. //replacing ENTER with a space
  83. range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  84. //replacing COMMA with the column delimeter
  85. range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  86. mWorkBook.SaveAs(targetCSVPathAndName, XlFileFormat.xlCSVMSDOS,
  87. Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
  88. Type.Missing, Type.Missing, Type.Missing,
  89. Type.Missing, false);
  90. return true;
  91. }
  92. catch (Exception ex)
  93. {
  94. ErrorMessage = ex.ToString();
  95. return false;
  96. }
  97. finally
  98. {
  99. if (mWSheet1 != null) mWSheet1 = null;
  100. if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
  101. if (mWorkBook != null) mWorkBook = null;
  102. if (oXL != null) oXL.Quit();
  103. System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
  104. if (oXL != null) oXL = null;
  105. GC.WaitForPendingFinalizers();
  106. GC.Collect();
  107. GC.WaitForPendingFinalizers();
  108. GC.Collect();
  109. }
  110. }
  111. }
  112. }

URL: XLStoCSV

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.