Office & Productivity Software forum

General discussion

Turning auto conversion off for text files(csv)

by WildeBeest12 / June 30, 2008 12:48 AM PDT

Office 2003 and 2007 behave the same way. As I can recall all Office version behave the same way.

I have csv files with numbers that have a decimal place followed by a 0. When opening the csv file Excel strips off the decimal place and 0.

Shouldn't Excel treat this type of file as text and not auto-covert? I sure think so.

Also, if I format a worksheet as text, and fill a column with 0, select the column, perform a replace all 0 to 0.0 the result is 0 not 0.0. This is crap.
A macro that does this works.

I know the trick of renaming .csv to .txt, shouldn't Excel handle all text files the same?

Discussion is locked
You are posting a reply to: Turning auto conversion off for text files(csv)
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Turning auto conversion off for text files(csv)
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
"shouldn't Excel handle all text files the same?"
by R. Proffitt Forum moderator / June 30, 2008 1:52 AM PDT

No. Remember we are dealing over a decade of "how it worked then and now." While you may disagree with how it works I read your post twice and see you know how to deal with this.

Was there something you needed fixing that your rename didn't fix?

Collapse -
shouldn't Excel handle all text files the same?
by WildeBeest12 / June 30, 2008 2:46 AM PDT

I guess I was hoping for there to be a way to turn auto-covert off without having to go through the extra steps.

Collapse -
Auto conversion of the text 100E000 to number
by petersjazz / July 1, 2008 9:34 PM PDT

I have a similar problem. I create a tab separated text file with the name xxxx.xls (have also tried with xxxx.txt but with the same result). One of the values contains reference like "100NN345", "100X345". I open the file in Excel and it looks good except for a row where 100E000 has been converted to number. Is it possible to create the tab separated file in another way to avoid this auto conversion?

The file:
"100NN345"<tab>8374634<tab>...
"100E000"<tab>343454<tab>...

Collapse -
No problem here in Excel 2000 (XP).
by Kees Bakker / July 1, 2008 9:47 PM PDT

I copied your example to .txt file (changing <tab> to a comma).
Then in Excel:
- File>Open to open test.txt
- Choose separated > Next
- Choose , > Next (it defaults to tab, even if there's none)
- On the next screen choose Text for the format of the first column, leave second column to Standard

It works with tabs also.

I assume this is the .txt-trick the first poster refers to. But I don't know why it doesn't work with you, as you state.

Kees

Collapse -
Excel will auto convert the tab separated file
by petersjazz / July 1, 2008 9:55 PM PDT

I create the file as tab separated and saves it with the filename xxxx.xls. Then open file file in Excel. Not first open Excel and open the file in Excel.

It will be the same result if I save it as xxxx.txt but then I will need to right click and open with Excel to open the file in Excel.

Collapse -
Indeed.
by Kees Bakker / July 1, 2008 11:28 PM PDT

If you open it by typing excel <filename> in the start>run box (or a command prompt) it opens directly, without calling the Text Import wizard. That's rather inconvenient. Apparently, it assumes you pressed shift in this case. See http://support.microsoft.com/kb/214295 about how to skip the wizard (which is exactly the reverse from what you want).

So it's up to you to change your habits and use File>Open to open the .txt file in this case, I'm afraid. Not a big deal.

Kees

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

Does BMW or Volvo do it best?

Pint-size luxury and funky style

Shopping for a new car this weekend? See how the BMW X2 stacks up against the Volvo XC40 in our side-by-side comparison.