How can I set Excel to always import all columns of CSV files as Text?

This works:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Usage:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Comma-separated file is now open as Excel sheet with all columns formatted as text.

Note that @Wetmelon's wizard solution works just fine, but if you're opening many files then you may, like me, grow weary of, each time, scrolling to column 60 in order to Shift-Click it.

EDIT @GSerg states in the comment below that this "doesn't work" and "eats spaces and leading zeroes". I'll just quote the comment to the question, which is more descriptive:

For reasons unknown, even if you explicitly provide formats for all columns in VBA, Excel will ignore it if the file extension is CSV. As soon as you change the extension, that same code will yield the correct results.

So the code above "works", but gets killed by this ridiculous Excel behaviour. Whichever way you cut it, you're stuck having to change the extension to something other than ".csv", sorry! After that, you're home free.

How to open CSVs in Excel

Good way

Excel → Data → Get external data → Select all columns with Shift and choose Text

Upside: Treats all values correctly as text without any exception

Downside: More steps than a simple Double-click

Bad way

Open a CSV with Double Click or Excel's Open with dialog

Downside: Excel's internal CSV handler misinterprets values with a leading - or = sign as a formula rather than text

Downside: You will lose leading zeros from binary values like 0001 due to Excel's auto detected column format

Good way (for VBA)

Use QueryTables (the VBA counterpart to Get external data) → Example code

Upside: Treats all values correctly as text without any exception

Downside: Slightly more code than OpenText method

Bad way (for VBA)

Use Workbooks.OpenText method → Example code

Downside: This method is still using Excel's internal CSV import handler with all its flaws

Downside: Additionally, the fieldinfo parameter of OpenText is ignored if the extension is CSV. Normally, this parameter lets you choose every column format, But not if the extension is CSV. You can read more about this behavior on Stack Overflow

^{Temporarily renaming the source extension from CSV to TXT and then back to CSV is a valid workaround if you have full control over the source file}

Additional methods

If you have access to the source which creates your CSV, you can alter the CSV syntax.
Enclose every value with double quotation marks and prefix an equal sign like ="00001" or prepend a tab to every value. Both ways will force Excel to treat the value as text

_{Original CSV content}

_{CSV in Excel when opened via double click}

Note how line 2 (double quote method) and line 3 (tab method) are not changed by Excel
Open CSV in Notepad and copy&paste all values to Excel. Then use Data - Text to Columns
Downside: Text in Columns for changing column formats from general back to text produces inconsistent results. If a value contains a - surrounded by characters (e.g. "=E1-S1"), Excel tries to split that value up into more than one column. Values located right to that cell may get overwritten
^{(The behavior of Text to columns was changed somewhere between Excel 2007 and 2013 so it doesn't work anymore)}

Excel Add-In to open CSVs and import all values as text

This is an Excel Plug-in to simplify CSV Import actions.
The main advantage: It's a one-click solution and uses QueryTables, the same bulletproof method behind Get external data

It adds a new menu command to Excel which lets you import CSV and TXT files. All values are imported to the active sheet starting at the currently selected cell
Excel Add-in are available for all Office versions on Windows and Mac
The whole Add-In has only 35 lines of code. Check the commented source code if you are curious
The used CSV list separator (comma or semicolon) is taken from your local Excel settings
Encoding is set to UTF-8

Installation

Download the Add-In and save it to your Add-Ins folder: %appdata%\Microsoft\AddIns
Open Excel and activate the Add-In: File tab → Options → Add-Ins → Go To and select ImportCSV.xla
Enable VBA macros: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
Restart Excel

You'll notice a new menu bar entry called Add-Ins and you use this button to quickly open your CSV files without going through the hassle of the Import dialog

enter image description here

PowerShell script to open CSVs directly from Windows Explorer

You can use a PowerShell script to open CSV files and automatically pass them to Excel. The script silently uses Excel's text import method which treats values always as text and, as a bonus, handles UTF-8 encoding

Create a new text file and paste the below script. A commented version can be found here

$CSVs = @()
$args.ForEach({
    If ((Test-Path $_) -and ($_ -Match "\.csv$|\.txt$")) {
        $CSVs += ,$_
    } 
})

if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()

    for ($i=0; $i -lt $CSVs.Count; $i++){

        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename

        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType  = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }
}

Save it somewhere like C:\my\folder\myScript.ps1. (Note the extension .ps1)
- Open your sendto folder via WinR » shell:sendto » Enter
Create a new shortcut via Right click » New » Shortcut and paste this line. Don't forget to change the path to your own one where you've put your script. Name the shortcut, for example, Excel

"%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe " -NoProfile -NonInteractive -WindowStyle Hidden -File "C:\my\folder\myScript.ps1"

Now you can select (multiple) CSVs and open them in Excel via Right-click » SendTo » Excel

You can try by opening a .xlsx first, then creating a data connection and importing the .csv. Select "comma" delimited, then select the option to treat all columns as text rather than "General".

Edit: Oh, I didn't fully read the question. In import wizard, select the first column header that you want to import as text, scroll to the final column header, and Shift+Click the header. Then select the "Text" radial option.

Wetmelon's suggestion works (even with .CSV) if you do the following:

Open Excel to a blank workbook or worksheet
Click to Data > [Get External Data] From Text
Use the "Text Import Wizard" as Wetmelon describes (Comma delimted, select the first column, SHIFT+CLICK the last column, set everything to Text).

I know it is more steps, but at least it lets me open CSVs this way without having to change the extension

Watch Out!

When using manual method "Excel → Data → Get external data → Select all columns and choose Text"......

This will only set the columns to text "that have data in the first row" (typically a header row). This wizard does not show you the columns farther to the right that might have data further below but not in the first row. For example:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

You will never see Col 4 in the import wizard so you will not get the option to change it from general to text format before importing!!!!