Excel – How to set the default data type to “text” while importing data from a text file in Excel

microsoft excel

I am working with Excel 2016 on a Windows 7 system. While importing data from a text file (csv, tsv, etc.), in the third step of the data import wizard, the data type for each column can be configured manually. The default data type in that wizard page is "General". How can I change that default to "text" (or to other data type)?

Best Answer

It's possible to change the default data type from General to Text, but VBA is required.

This Macro will import a Text file into Excel and will also specify the data type as TEXT.

Sub Import()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Test1.txt", Destination:=Range("$A$1") _
        )
        .Name = "Test1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Note: Please check the line .TextFileColumnDataTypes = Array(2, 2, 2). Here 2 sets the TEXT format and the 3 ELEMENTS of the array specify 3 columns. You should set the array to have as many elements as the number of columns in your text file.

Related Question