# How do I stop excel from 'interpreting' fields in a CSV file. **URL:** \ **Category:** Factual Questions\ **Created:** [February 26, 2008, 9:54pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294 "2008-02-26T21:54:29Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [February 26, 2008, 9:54pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294/1 "2008-02-26T21:54:29Z")
I have a CSV file which contains some data ‘1/7’ When I open it in excel it becomes ‘07-Jan’ and if I reformat the column to text it becomes 39454 As you can imagine, this is irritating. How do I stop it happening? On a similar note, if I ever import data with a date in yy/mm/dd excel interprets it incorrectly. For instance 08/01/27 becomes the eighth of January 2027 ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [February 26, 2008, 10:05pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294/2 "2008-02-26T22:05:11Z")
I’m not sure about importing CSV files, but I often copy text files into Excel which I want interpreted as text rather than as numbers or dates. To do that I open a new blank Excel file, select the column(s) that the file will copy into, format those column(s) as text, and then use paste-special to copy the the external file as “Text” or as “Unicode text”. (The particular issue that I face is strings of digits that need to be treated as codes, not as numbers, e.g. “001”. If I don’t format as text, it gets converted into the number 1.) ---
**Author:** ![Sal\_Ammoniac](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@Sal\_Ammoniac](https://boards.straightdope.com/u/Sal_Ammoniac)\ **Post date:** [February 26, 2008, 10:12pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294/3 "2008-02-26T22:12:29Z")
I can answer the first part of your question. You’ll want to open a new Excel file and use the Import feature (Data-\>import external data-\>import data). Open your CSV file, choose the file type “delimited,” set your delimeter as the comma, and make sure your first column’s data format is set to “text.” As to your second question, I don’t really know the answer. I don’t know whether that’s native Excel behavior or something that can be modified. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [February 26, 2008, 10:23pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294/4 "2008-02-26T22:23:49Z")
Alternatively, change the file extension from “csv” to “txt”. Then open it with Excel and it will give you the Text Import Wizard. (This is the same as using the Data Import in **Sal Ammoniac** ’s post.) In the Wizard, set it as delimited with a comma and it will allow you to set column data formats. Use Text format to import your 1/7 exactly as is. Use Date format and select YMD to get the dates to import correctly. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [February 26, 2008, 10:27pm UTC](https://boards.straightdope.com/t/how-do-i-stop-excel-from-interpreting-fields-in-a-csv-file/439294/5 "2008-02-26T22:27:11Z")
With some versions of Excel, importing a CSV file skips over the steps where you get to define the data type for each column - it guesses based on content and in some cases, guesses badly (it might see a column apparently containing numbers and import them as numeric, when they were product codes with leading zeroes that needed preserving). The way I usually work around it is to rename the file as .txt, then use file\>open and tell it files of type: text - then it goes through the import wizard that allows you to define how each field should be imported. The date being interpreted as the wrong century could be a Windows setting - if you go to Control Panel\>Regional Options\>Customize\>Date, you should see some options for how two digit dates should be interpreted.