81

Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. As far as I can tell, the only way to prevent this from happening on import is to rename the file so the extension isn't .csv and use the import wizard to specify the format of each column individually. For files with 50-60 columns, this is impractical.

Since every answer for this oft-asked question on the internet suggests either some means of converting the formatted numbers back once the file is open (which won't work for me - I want to solve the general problem, not a few specific cases) or manually selecting the format type of each column (which I don't want to do), I'm looking for a way to set a global preference or style such that all columns of all CSV files opened are always formatted as text. I know about "armoring" the numbers with quotes, too, but the files I get don't come like that and I was hoping to avoid having to pre-process the files so Excel doesn't screw them up.

Is there a way to do specifically this: Always format all columns in opened CSV files as text, without manually selecting each column every time during import?

I'm using Excel 2003, but I'll take answers for 2007 if that's what you know.

1
  • 1
    Hey Scripting Guy did a blog article about Importing CSV into Excel that might have some useful tidbits for you. Playing with the data object inside powershell may allow you to do what you want. Not posted as an answer as it's essentially just an offsite link, but it might have something of use to you. Jul 5, 2011 at 2:18

10 Answers 10

9

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.

3
  • 1
    Doesn't work. Eats spaces, eats leading zeroes etc.
    – GSerg
    Jul 6, 2011 at 19:19
  • 1
    Source. Result. Result when renamed to not CSV.
    – GSerg
    Jul 6, 2011 at 19:31
  • 1
    The used method Workbooks.OpenText has its flaws by design (See my answer below). Use QueryTables instead.
    – nixda
    Jul 25, 2013 at 23:58
83

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
    enter image description here

    CSV in Excel when opened via double click
    enter image description here

    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

  1. Download the Add-In and save it to your Add-Ins folder: %appdata%\Microsoft\AddIns
  2. Open Excel and activate the Add-In: File tab → Options → Add-Ins → Go To and select ImportCSV.xla
  3. Enable VBA macros: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
  4. 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

  1. 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()
    }
}
  1. Save it somewhere like C:\my\folder\myScript.ps1. (Note the extension .ps1)
    • Open your sendto folder via WinR » shell:sendto » Enter
  2. 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

6
  • 1
    Add-In was tested with Office 2003 and 2013
    – nixda
    Jun 15, 2013 at 12:39
  • The top "Best Way" is exactly what I needed for a one-off import. No need to mess with VBA and can even control what columns are taken as date/numbers (with the rest taken as text). I wished Excel would offer to open this import wizard when double-clicking on CSV.
    – ADTC
    Apr 16, 2015 at 4:20
  • @Vadzim Hello, I appreciate your effort to fix dead links. Indeed ge.tt makes some problems in the last months. But I think I fixed all problems and the old original links should work again. I don't know why Google Chrome warns before downloading anything from ge.tt. But all other browsers work normal and I can assure you that they are no malware
    – nixda
    Nov 7, 2016 at 19:51
  • 3
    @nixda The links to ge.tt are again broken. Most say they are not available because the owner violated the TOS. However, the one for the OpenCSV.exe says "These files are no longer available as they violated the terms of service." Which brings me to ask, are you the author of the ImportMyCSV Excel function? (sourcecode is still available on pastebin) This is an awesome tool - I use it at work and have recommended it to others. But I am interested in the origin of the code, if nothing else, to properly credit the author. Apr 6, 2017 at 20:31
  • The link to OpenCSV.exe above is dead again. So I've uploaded my copy here: drive.google.com/file/d/1rYkbZdwQYrDuO1I7ujfmxS0dr_pKkhA_/…
    – Vadzim
    Apr 8, 2021 at 21:56
5

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.

1
  • 2
    Yeah, this works nicely -- as long as the file's extension isn't ".csv". +1 Jul 6, 2011 at 18:41
4

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

  1. Open Excel to a blank workbook or worksheet
  2. Click to Data > [Get External Data] From Text
  3. 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

3

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!!!!

1
  • 1
    A good warning, since 99.9999% of the time, people don't scroll down in the import wizard, and even then it's hard to catch all the columns. However, technically, you can scroll down while you're in the Import wizard and catch additional columns that don't have data in the first row. Mar 14, 2017 at 20:31
1

Here is the alternative procedure to code posted above by Jean-François Corbett

This Procedure will import csv file into Excel with all columns formatted as Text

Public Sub ImportCSVAsText()
    Dim TempWorkbook As Workbook
    Dim TempWorksheet As Worksheet
    Dim ColumnCount As Integer
    Dim FileName As Variant
    Dim ColumnArray() As Integer

    'Get the file name
    FileName = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", FilterIndex:=1, Title:="Select the CSV file", MultiSelect:=False)

    If FileName = False Then Exit Sub

    Application.ScreenUpdating = False

    'Open the file temporarily to get the count of columns
    Set TempWorkbook = Workbooks.Open(FileName)
    ColumnCount = TempWorkbook.Sheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    TempWorkbook.Close SaveChanges:=False

    'Resize the array to number of columns
    ReDim ColumnArray(1 To ColumnCount)

    For i = 1 To ColumnCount
        ColumnArray(i) = xlTextFormat
    Next i

    Set TempWorkbook = Workbooks.Add
    Set TempWorksheet = TempWorkbook.Sheets(1)

    Application.DisplayAlerts = False
    TempWorkbook.Sheets(2).Delete
    TempWorkbook.Sheets(2).Delete
    Application.DisplayAlerts = True

    With TempWorksheet.QueryTables.Add("TEXT;" & FileName, TempWorksheet.Cells(1, 1))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1251
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = ColumnArray
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub
1
  • Welcome to SuperUser, instead of adding new answer, you should have edited the Corbett's answer and improve it. I realize you don't have enough reputation to do that yet, but you could link your changes in comment where you ask him to read it. Dec 4, 2015 at 9:59
0

If you are always importing the same data (constant record format, layout, etc...) you could write an Access macro using an import spec and then dump the data back out to Excel. Done this may times. The other way that I have done it is to use VBA and read the data into the worksheet one record at a time and parse it out as it reads. As far as I know there is no way to set a default format during import in Excel and even if you could it would cause problems with the next file type you try to parse.

1
  • 1
    Here's the thing - I'm not trying to do any parsing except for putting the text string that was in between the commas into columns when I import it. If I need it to be formatted a certain way, I can then do that (like make date strings dates, etc), but I just need the data left alone initially. Why should that be so hard? Jul 5, 2011 at 2:15
0

As request, submitting my comment as an answer (with a little more info added):

Hey Scripting Guy did a blog article about Importing CSV into Excel that might have some useful tidbits for you. Playing with the data object inside powershell may allow you to do what you want.

Although the article specifically just mentions importing the data into the cells which may leave the number format, it may be possible to play around with some of the Excel ComObject properties and methods to force the data to enter the cells as raw text instead (or force the formatting of the cells into text before or after the import).

0

For reasons unknown, even if you explicitly provide formats for all columns, Excel will ignore it if the file extension is CSV.

Some options:

  • Create a query to import the data, as Wetmelon suggests.
    Disadvantage: you may be missing CSV database drivers on a 64-bit machine.

  • Use Jean's code, but incorporate copying the file to a temporary folder and changing the copy's extension.
    Disadvantage: No link to the original file (saving will overwrite the copy); you will have to manually delete the copy afterwards. Still, you can manually Save As over the original CSV.

  • Open the CSV in Notepad, Ctrl+A, Ctrl+C, paste to Excel, then Data - Text to Columns, then it's the usual wizard where you can set all columns to Text in one go. It's a different flavour of the previous option, because it also hides the precious extension from Excel.
    Disadvantage: manual.

  • Have a very simple VBA loop that reads the whole file into memory and puts it onto the sheet, cell by cell.
    Disadvantage: slower, ugly.

2
  • Isn't "No link to the original [CSV] file" a disadvantage of all these methods? I like the idea of copying to a temp folder and changing the extension there... Could even overwrite the original (as CSV) as soon as the copy is open. Jul 7, 2011 at 7:00
  • @Jean-FrançoisCorbett, I use the Import Wizard all the time to import .csv files with an extension of csv into Excel 2010. I explicitly specify the columns' format as Text and it retains the cell format as text. Perhaps this was an issue with older versions of Excel? Mar 14, 2017 at 20:44
-2

If I understand the question correctly, this is easily solved using the Transpose option in Paste-Special as described here.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .