How to avoide data conversion when opening a text file?
 

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?

How to avoide data conversion when opening a text file?
 

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.



Vel wrote:

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

How to avoide data conversion when opening a text file?
 

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.
--
David Biddulph

"Vel" wrote in message
...
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?

How to avoide data conversion when opening a text file?
 

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?

How to avoide data conversion when opening a text file?
 

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.

How to avoide data conversion when opening a text file?
 

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.

How to avoide data conversion when opening a text file?
 

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?

Vel wrote:

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?

--

Dave Peterson

How to avoide data conversion when opening a text file?
 

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.
--
David Biddulph

"Vel" wrote in message
...

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.

How to avoide data conversion when opening a text file?
 

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

How to avoide data conversion when opening a text file?
 

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

I don't have another guess.

Vel wrote:

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

How to avoide data conversion when opening a text file?
 

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.

How to avoide data conversion when opening a text file?
 

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!

How to avoide data conversion when opening a text file?
 

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!

How to avoide data conversion when opening a text file?
 

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

"Vel" wrote in message
...

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.

How to avoide data conversion when opening a text file?
 

Glad you found the answer.
--
David Biddulph

"Vel" wrote in message
...
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!

How to avoide data conversion when opening a text file?
 

Vel

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

Try re-registering Excel.

Close Excel first and On the Windows Taskbar

1) StartRun "excel.exe /unregserver"(no quotes)OK.
2) StartRun "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 StartRun "C:\yourpath\excel.exe /regserver"(no quotes)OK.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 06:09:00 -0700, Vel wrote:

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!

How to avoide data conversion when opening a text file?
 

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.


--
Regards,

Peo Sjoblom



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Vel

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

Try re-registering Excel.

Close Excel first and On the Windows Taskbar

1) StartRun "excel.exe /unregserver"(no quotes)OK.
2) StartRun "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 StartRun "C:\yourpath\excel.exe /regserver"(no quotes)OK.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 06:09:00 -0700, Vel
wrote:

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!

How to avoide data conversion when opening a text file?
 

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


Gord

On Tue, 15 May 2007 08:44:29 -0700, "Peo Sjoblom" wrote:

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.

How to avoide data conversion when opening a text file?
 

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

"Vel" wrote:


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.