Reply
Thread Tools Display Modes
#1
Old 02-26-2008, 03:54 PM
Member
Join Date: Mar 2002
Location: Douglas, Isle of Man
Posts: 18,214
How do I stop excel from 'interpreting' fields in a CSV file.

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
#2
Old 02-26-2008, 04:05 PM
Charter Member
Join Date: Apr 2004
Location: Newcastle NSW
Posts: 12,872
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.)
#3
Old 02-26-2008, 04:12 PM
Registered User
Join Date: Apr 2005
Location: Beans, Cod
Posts: 4,459
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.
#4
Old 02-26-2008, 04:23 PM
Guest
Join Date: Mar 2000
Posts: 2,926
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.
#5
Old 02-26-2008, 04:27 PM
Member
Join Date: May 2001
Location: England
Posts: 57,409
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.
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 02:58 PM.

Send questions for Cecil Adams to: [email protected]

Send comments about this website to:

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: [email protected].

Copyright 2018 STM Reader, LLC.

Copyright © 2017
Best Topics: just horny nembutal suicide vodka lemonade name gymnast wedgies christmas bonus average parlament cigarettes counterfeit poker chips glue for bookbinding tresomega coconut oil dark ketchup insulation washers cleverbot secrets german goodbye stool hardener pills small town cop cedar siding maintenance print signature fried braunschweiger reheating chicken wings fbi consultant minimum trade in yolk joke phil mickelson jerk brooke hogan oops kevin muncy freakonomics frankenstein's head decorated veteran meaning alumni pronounce kung fu grip steam clean engine hay roll japan nerds newpaper carrier incontentia buttocks narrow interests snl subway announcer what does garden level mean is steve rogers a virgin fork fork fork joke what is the song the stroke about 8 7 central meaning air in subcutaneous injection chihuahua st. bernard mix play buzztime trivia at home legal self defense weapons in nj vein in foot sticking out ground light switch to metal box best way to make money in sunless sea shave and a hair cut two bits no memo in so gay kings of england magnification to see cells how to mail a package anonymously are soundbars worth the money where does bail money go phone cords that do not tangle is arm hair unattractive what time does the mailman stop delivering milkshake brings all the guys to the yard meaning what does rolling in the deep mean excel turn off auto date i fell off my bike how to get into bartending open a locked filing cabinet difference between dill and kosher dill what is a parking lamp how long does fallout last rebel without a cause cars can candidates vote for themselves sand dollar legend non religious