How to avoide data conversion when opening a text file?

[Guest]

I have a text file, in the first column I have dates in the format MM/DD/YYYY.
When I open this file in Excel the first column looks bad:

8/31/2006 stays the way it is.
12/01/2006 is converted to 12-01-06 which is wrong - somehow Excel thinks
this is January 12 when in fact it is December 1st.

I'd rather it to be uniform.
How to set up Excel not to convert the data on opening the file or to
convert it the right way for all data?

 

[Dave Peterson]

If the filename has an extension of .txt, you should see a text to columns
wizard popup right after you do the File|Open stuff.

Then you can choose that the first field is a date and is in mdy order.

 

[David Biddulph]

If you're opening a text file and want a column to stay as text, select text
as the format for that column at the end of the import.
If you want it to import as date, ensure that your Windows Control Panel
Regional Settings match your preferences.

 

[Guest]

If the filename has an extension of .txt, you should see a text to columns
wizard popup right after you do the File|Open stuff.

Well, it doesn't popup. It just opens the file.
How do I set up the Excel to make the wizard appear?

 

[Guest]

If you're opening a text file and want a column to stay as text, select text
as the format for that column at the end of the import.

Unfortunately when the file is open it is already too late for doing that!
Here is what happens when I select a column and do Format Cells -> Text:
The data that was kept as it was (10/24/2006) stays the same: 10/24/2006.
But the data that got automatically converted (05-01-06) produces a complete
nonsense: 38729

If you want it to import as date, ensure that your Windows Control Panel
Regional Settings match your preferences.

I want to import the data just the way it is, and see my column just the way
I want it to be: MM/DD/YYYY. I do not want it to match my Regional Settings
which I like to keep in a different format. I want Excel to stop acting as it
knows better what I need, but I don't see how to fix it.

 

[Guest]

If you're opening a text file and want a column to stay as text, select text
as the format for that column at the end of the import.

Unfortunately when the file is open in Excel it is already too late for
doing that.
Here is what happens when I select a column and do Format Cells -> Text:
The data that didn't get converted (10/24/06) stays the same: 10/24/06
The data that got converted (12-01-06) is turned to a complete nonsense:
38729.

If you want it to import as date, ensure that your Windows Control Panel
Regional Settings match your preferences.

I want to import the data the way it is, MM/DD/YYYY.
I don't want it to match my Regional Setting which I like to keep in a
different format.
I would like Excel to stop acting as it knows better what I need, but I
don't see how to fix it.

 

[Dave Peterson]

You've renamed the file to *.txt and you're using file|Open to open the .txt
file?

I've never seen this fail to pop up the text to columns wizard.

Can you provide more detail?

 

[David Biddulph]

It's too late when you've finished the import. At step 3 of 3 in the text
import wizard, each column has a header with its default format (probably
General). You need to select each column that you don't want to take that
format, and set the format for that column (to text, or whatever). If you
set each column as text, the format won't be changed by Excel.

Once you've got all the data into the file in the format that you had in
your source data, you can process it as appropriate to convert to whichever
format you want.

 

[Guest]

You've renamed the file to *.txt and you're using file|Open to open the .txt
file?

I have a text file, and its extension is txt. I did not rename it, in case
it matters.

I've never seen this fail to pop up the text to columns wizard.

Then, I guess, it must have been deactivated on this machine.
Could you tell me how to turn it on?

Can you provide more detail?

Windows XP SP2
MS Excel 2007

 

[Dave Peterson]

I don't think it's something you can deactivate.

I don't have another guess.

 

[Guest]

It's too late when you've finished the import. At step 3 of 3 in the text
import wizard, each column has a header with its default format (probably
General).

Then the problem is that there was no wizard.
Excel just opens the file, and I get no access to any converting options.
I can't figure out how to make the wizard work.

 

[Guest]

The problem is resolved.

In fact when I click on the 'file.txt' and choose 'Open with -> Excel', it
just opens it, probably using default settings.

BUT if I first open Excel, go to 'Open file' and chose 'file.txt', the
wizard is here, and it does the job.

Thank you very much for your help and advice, Dave!

 

[Guest]

The problem is resolved.

In fact when I click on the 'file.txt' and choose 'Open with -> Excel', it
just opens it, probably using default settings.

BUT if I first open Excel, go to 'Open file' and chose 'file.txt', the
wizard is here, and it does the job.

Thank you very much for your help and advice, David!

 

[David Biddulph]

You're not, by any chance, holding down the shift key when you hit "open",
having selected the file, are you?
http://support.microsoft.com/kb/214295

If the wizard isn't coming up through File/ Open and selecting the .txt
file, it might just be worth trying with Data/ Import External Data/ Import
Data.
That route apparently (in Excel 2000 & later) allows csv's to be opened
through the wizard without needing to rename from .csv to .txt, so it might
possibly resolve your problem too.

 

[David Biddulph]

Glad you found the answer.

 

[Gord Dibben]

Vel

Sounds like Excel has lost association with *.txt files.

Try re-registering Excel.

Close Excel first and On the Windows Taskbar

1) Start>Run "excel.exe /unregserver"(no quotes)>OK.
2) Start>Run "excel.exe /regserver"(no quotes)>OK.
See the space between exe and /regserver

You might have to designate a full path to excel.exe.
In that case Start>Run "C:\yourpath\excel.exe /regserver"(no quotes)>OK.


Gord Dibben MS Excel MVP

 

[Peo Sjoblom]

I don't think so, when I open txt files with Excel by right clicking them
they also open without triggering the text import wizard. In fact it is
handy since sometimes I don't want it to open with the text import wizard.

 

[Gord Dibben]

Good point...............I went at it backwards.


Gord

 

[Rafael Cortes (BiR0)]

Select the column with the dates with problem.
Select Data/Text to Column
The wizard will appear. Then you select the new format.