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

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

up vote40down votefavorite 18	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. microsoft-excel formatting
migrated from stackoverflow.com Jul 7 '11 at 6:21 This question came from our site for professional and enthusiast programmers.
	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. – Matrix Mole Jul 5 '11 at 2:18 add a comment

11 Answers

activeoldestvotes

up vote7down voteaccepted

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 claims 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. shareimprove this answer edited Jan 7 '15 at 21:17 answered Jul 6 '11 at 19:13 Jean-François Corbett 1,4752915

1   Doesn't work. Eats spaces, eats leading zeroes etc. – GSerg Jul 6 '11 at 19:19 Yes it does work, and no it doesn't eat anything. – Jean-François Corbett Jul 6 '11 at 19:23 Source. Result. Result when renamed to not CSV. – GSerg Jul 6 '11 at 19:31 @GSerg: Editing my answer following your somewhat more educational comment to the question! – Jean-François Corbett Jul 6 '11 at 19:36 The used method Workbooks.OpenText has its flaws by design (See my answer below). UseQueryTables instead. – nixda Jul 25 '13 at 23:58 add a comment

up vote39down vote

How to open CSVs in Excel Best 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 Worst 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 then text Downside: You will lose leading zeros from binary values like 0001 due to Excels auto detected column format Best 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 Worst 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 stackoverflow Temporarily renaming the source extension from CSV to TXT and than 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 a Excel Plug-in (Import CSV) to simplify CSV Import actions. The main advantage: Its 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 select CSV and TXT files. All values are imported to the active sheet and and the selected cell Add-in works for all Office versions on Windows and MAC (Linux is a little bit tricky but possible). The whole Add-In only has 35 lines of code. Check the commented sourcecode if you are curious The used CSV list separator (comma or semicolon) is taken from your local system settings. Non-english Excel users will benefit from this Installation Download the Add-In and save it to your Add-Ins folder: C:\Users\USERNAME\AppData\Roaming\Microsoft\AddIns Open Excel and activate the Add-In Excel 2003: Menu bar → Tools → Add-ins Excel 2007: Excel Options → Add-Ins → Go To Excel 2010-13: File tab → Options → Add-Ins → Go To Restart Excel and look at your menu bar click for fullscreen with more details PowerShell script to open CSVs direct from Windows Explorer Use a Powershell script to open a CSV with a Double-Click from within the Windows Explorer and preserve all values as text in Excel. This method is also available for Windows XP if you install Powershell manually. Insert the code below to a text file and save it as OpenCSV.ps1 or download it $csv = Get-Item $args[0] $excel = New-Object -ComObject excel.application $excel.visible = $true $workbook = $excel.Workbooks.Add() $worksheet = $workbook.worksheets.Item(1) $worksheet.Name = $csv.basename $arrFormats = ,2 * $worksheet.Cells.Columns.Count $TxtConnector = ("TEXT;" + $csv.fullname) $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1")) $query = $worksheet.QueryTables.item($Connector.name) $query.TextFileOtherDelimiter = $Excel.Application.International(5) $query.TextFileParseType = 1 $query.TextFileColumnDataTypes = $arrFormats $query.AdjustColumnWidth = 1 $query.Refresh() $query.Delete() Remove-Item variable:arrFormats [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() Insert the code below to a new text file and save it as OpenCSV.cmd Modify the path to your path where you saved the Powershell script earlier. Powershell -Command "C:\some\folder\OpenCSV.ps1" %1 Since we can't use a Powershell script as a file open handler we have to use the old-fashioned batch file as work around. Don't ask me why. Ask Microsoft. Now you should have two files: OpenCSV.cmd and OpenCSV.ps1 Right-click any CSV → Open with → Choose default program and select your CMD file (or) Avoid steps 1-3 and the hassle to create two scripts. Download the ready-to-use executable OpenCSV.exe and assign it as your default program to open CSV files. I just converted the PowerShell script with PS2EXE to an executable. Voila, from now on every time you double click a CSV it will be opened by Excel and all values are still formatted as text. How does the script work? First, when double-clicking a CSV we pass the CSV path to a CMD The CMD then calls a Powershell script and passes on the CSV path The Powershell script silently opens Excel and tells Excel to use QueryTables to open the CSV path. Thats the same method the Excel Add-In is using too.