 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / April 2008How 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? 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. > 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: [quoted text clipped - 6 lines] > How to set up Excel not to convert the data on opening the file or to > convert it the right way for all data?  SignatureDave 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. Well, it doesn't popup. It just opens the file. How do I set up the Excel to make the wizard appear? 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? > > 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? SignatureDave Peterson > 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 I don't think it's something you can deactivate. I don't have another guess. > > You've renamed the file to *.txt and you're using file|Open to open the .txt > > file? [quoted text clipped - 11 lines] > Windows XP SP2 > MS Excel 2007 SignatureDave Peterson 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! 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 >The problem is resolved. > [quoted text clipped - 5 lines] > >Thank you very much for your help and advice, Dave! 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. SignatureRegards, Peo Sjoblom > Vel > [quoted text clipped - 22 lines] >> >>Thank you very much for your help and advice, Dave! Good point...............I went at it backwards. Gord >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. 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. SignatureDavid Biddulph >I have a text file, in the first column I have dates in the format >MM/DD/YYYY. [quoted text clipped - 7 lines] > How to set up Excel not to convert the data on opening the file or to > convert it the right way for all data? > 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. 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. SignatureDavid Biddulph >> If you're opening a text file and want a column to stay as text, select >> text [quoted text clipped - 17 lines] > it > knows better what I need, but I don't see how to fix it. > 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. 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. SignatureDavid Biddulph >> It's too late when you've finished the import. At step 3 of 3 in the >> text [quoted text clipped - 4 lines] > 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. Select the column with the dates with problem. Select Data/Text to Column The wizard will appear. Then you select the new format. > > 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 [quoted text clipped - 3 lines] > 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. 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! Glad you found the answer. SignatureDavid Biddulph > The problem is resolved. > [quoted text clipped - 5 lines] > > Thank you very much for your help and advice, David! > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.