CSV Parsing Problems: Quick Solution for fixing the data types of the underlying columns

One of the easiest and quick solution is to use the Excel object Library,
open it and save it as a Proper CSV.



Suitable comments have been provided at necessary places.

C# code for the same is as below.

         object BackGroundQuery = true;
         //Start New Excel Application

         _XLApp = new Excel.Application();
         _XLApp.Visible = false;

         _XLWB = _XLApp.Workbooks.Open(@File, missingValue, false, missingValue, missingValue, missingValue, missingValue, missingValue,
         missingValue, true, missingValue, missingValue, missingValue, missingValue, missingValue);
         _XLWS = (Excel.Worksheet)_XLWB.Worksheets[1];
            
          Excel.Range _range;
          _range = _XLWS.UsedRange;
           _range.ClearContents();

           object BackgroundQuery  = false;
                     
           object Connection = "TEXT;"+ Path.Combine(sourceLocation,File);
          _XLQueryTable =(Excel.QueryTable)_XLWS.QueryTables.Add(Connection, _XLApp.get_Range("A1", missingValue), missingValue);
          _XLQueryTable.Name = "Converted File";
          _XLQueryTable.FieldNames = true;
          _XLQueryTable.RowNumbers = false;
          _XLQueryTable.FillAdjacentFormulas = false;
          _XLQueryTable.PreserveFormatting = true;
          _XLQueryTable.RefreshOnFileOpen = false;
          _XLQueryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
          _XLQueryTable.SavePassword = false;
          _XLQueryTable.SaveData = true;
          _XLQueryTable.AdjustColumnWidth = true;
          _XLQueryTable.RefreshPeriod = 0;
          _XLQueryTable.TextFilePromptOnRefresh = false;
          _XLQueryTable.TextFilePlatform = 437;
          _XLQueryTable.TextFileStartRow = 1;



//Note: this is required for CSV parsing
_XLQueryTable.TextFileParseType = Excel.XlTextParsingType.xlDelimited;
          _XLQueryTable.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
          _XLQueryTable.TextFileConsecutiveDelimiter = false;
          _XLQueryTable.TextFileTabDelimiter = false;
          _XLQueryTable.TextFileSemicolonDelimiter = false;



          //CSV : in this case is comma delimited
          _XLQueryTable.TextFileCommaDelimiter = true;
          _XLQueryTable.TextFileSpaceDelimiter = false;
          _XLQueryTable.TextFileColumnDataTypes = new object[]{1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1};
          _XLQueryTable.TextFileTrailingMinusNumbers = true;
          _XLQueryTable.Refresh(BackgroundQuery);


          //Choose the range.
          _range = _XLWS.get_Range("C1","C1");
          //Set the Formatting
          _range.EntireColumn.NumberFormat = "0000";

          _range = _XLWS.get_Range("N1","N1");
          _range.EntireColumn.NumberFormat = "General";

          //Save as CSV
Message = Path.Combine(Path.GetDirectoryName(sourceLocation),Path.GetFileNameWithoutExtension(File)+"_New.csv");
          _XLWB.SaveCopyAs(Message);
                    

Don't forget to call these methods as part of cleanup procedure.

   NAR(_XLWS);
   NAR(_XLWB);
   NAR(_XLApp);


/// <summary>
/// NARs the specified o.
/// </summary>
/// <param name="o">The o.</param>
private void NAR(object o)
{
   try
   {
      System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
   }
   catch
  {
   }
   finally
   {
     o = null;
   }
}