Excel won't leave my data alone!

[Trentham]

This has got to be simple but I can't see the answer!

I've got a csv file which I'm, opening in Excel. It's just a lot of text fields, i.e. "xxx","yyy","zzz",....

Some of the fields contain data such as "1/2" but when Excel opens this file, it knows best and converts it into a date, 01-Feb.

How do I stop it doing this?

 

[GlennUK]

Rename the file, so that it doesn't have an extension of CSV, then when you open it the Text Import Wizard will be activated, and you can specify that these fields are to be imported as something other than date.


Cheers, Glenn.

 

[MakeItSo]

Go to tools - options.
There deactivate the "auto entry for cell values" option.
If this does not suffice, try with different settings.
If the problem remains, try the following:
Import the csv as "text import" (toolbar: data - import text file) into an empty worksheet.
Set the column formats as you need them (e.g. text/number for the 1/2 column).
Save the worksheet as something.dot
You will be asked if Excel shall remove the data before saving and update on next open. Say YES.

YOu then have a template you can re-use with your csvs and which should have correct format for all cells.

A bit longwinded, but should also remove the problem.

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Trentham]

Thanks for both those ideas. I'll give them a go and see if I can get Micro$oft to let me decide how to process my data!

 

[Trentham]

MakeItSo's solution hasn't worked :-( I can't find a "auto entry for cell values" entry un iptions (Office 2000) and ones I've tried didn't help. Setting up a template didn't stop it converting to date format either.

 

[KenWright]

Change the extension to .txt and then when the import wizard runs, there is a step in there (Step 3 I think) where you can tell it to make a specific column TEXT. Do this on the columns you are having problems with.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

Why don't you try my solution given yesterday?

Cheers, Glenn.

 

[Trentham]

I tried that but it wasn't fooled... it knew better than to treat it as text!

 

[KenWright]

Then you honestly have to be doing something wrong. All my answer was, was a slight expansion of Glenns to include the bit about choosing text for the column in question, as that is the most common workaround for this problem. Whilst in the wizard, you specifically have to click on the column of text you want to remain text and choose the 'import as text' option in the top right of the dialog box.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Trentham]

Yep, I must have been doing something wrong. I've tried again and it seems OK now. TVM.