This has been driving me crazy all day (since indeed you can't control the Excel column types before opening the CSV file), and this worked for me, using VB.NET and Excel Interop:
'Convert .csv file to .txt file.
FileName = ConvertToText(FileName)
Dim ColumnTypes(,) As Integer = New Integer(,) {{1, xlTextFormat}, _
{2, xlTextFormat}, _
{3, xlGeneralFormat}, _
{4, xlGeneralFormat}, _
{5, xlGeneralFormat}, _
{6, xlGeneralFormat}}
'We are using OpenText() in order to specify the column types.
mxlApp.Workbooks.OpenText(FileName, , , Excel.XlTextParsingType.xlDelimited, , , True, , True, , , , ColumnTypes)
mxlWorkBook = mxlApp.ActiveWorkbook
mxlWorkSheet = CType(mxlApp.ActiveSheet, Excel.Worksheet)
Private Function ConvertToText(ByVal FileName As String) As String
'Convert the .csv file to a .txt file.
'If the file is a text file, we can specify the column types.
'Otherwise, the Codes are first converted to numbers, which loses trailing zeros.
Try
Dim MyReader As New StreamReader(FileName)
Dim NewFileName As String = FileName.Replace(".CSV", ".TXT")
Dim MyWriter As New StreamWriter(NewFileName, False)
Dim strLine As String
Do While Not MyReader.EndOfStream
strLine = MyReader.ReadLine
MyWriter.WriteLine(strLine)
Loop
MyReader.Close()
MyReader.Dispose()
MyWriter.Close()
MyWriter.Dispose()
Return NewFileName
Catch ex As Exception
MsgBox(ex.Message)
Return ""
End Try
End Function