Usually one would select the data-type for each imported column as step 3 of 3 of the text-import dialog. To avoid misformatting, just use text as format, and you're good.
However, you seem to use a VBA macro to import your CSV files. So I just recorded such an uncorrupted text import:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\test.csv", Destination:=Range("$A$1"))
.Name = "test_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1) 'THIS IS THE MAGIC LINE!
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Also, look at this example from excel-help for TextFileColumnDataTypes
:
Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = Array(5, 4)
.TextFileColumnDataTypes = _
Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
.Refresh
End With
These are the formats you can use:
- xlGeneralFormat
- xlTextFormat
- xlSkipColumn
- xlDMYFormat
- xlDYMFormat
- xlEMDFormat
- xlMDYFormat
- xlMYDFormat
- xlYDMFormat
- xlYMDFormat