Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2012, 11:09 AM
Jamal NUMAN Jamal NUMAN is offline Opening the CSV file in the Excel has two different appearances! Windows 7 64bit Opening the CSV file in the Excel has two different appearances! Office 2010 64bit
Expert
Opening the CSV file in the Excel has two different appearances!
 
Join Date: Nov 2010
Posts: 496
Jamal NUMAN is on a distinguished road
Question Opening the CSV file in the Excel has two different appearances!

Opening the CSV file in the Excel has two different appearances!
The CSV shown in screenshot 1 and 2 opened in the Excel in two different format. In the first screenshot all the data is loaded in one column while in the second one the data are categorized in many columns.
My question: how to control the appearance of the CSV file to be opened the same way as in the screenshot 2?



Why does it appear sometimes as it shown in the screenshot 1?
Thank you in advance,
Regards
Jamal
Attached Images
File Type: jpg excel.jpg (124.9 KB, 4 views)
File Type: jpg Clip_280.jpg (140.1 KB, 4 views)
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank.
Reply With Quote
  #2  
Old 01-19-2012, 12:15 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Opening the CSV file in the Excel has two different appearances! Windows 7 32bit Opening the CSV file in the Excel has two different appearances! Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

There are quite a few different ways to get the data from the CSV file into Excel.

When the data from the CSV file is imported, you may or may not need to tell Excel that it should use a comma delimiter. That is, each time Excel sees a comma in the CSV file, it understands that it has to put the subsequent data into a separate column. If no delimiter is used then Excel will just dump each entire line into the 1st column in the spreadsheet.


If you open the file within windows explorer then the comma delimiter will automatically be used so you will get the screenshot 2 result.
The same is true if you open the file in Excel by going File > Open.



If you import the CSV file by going Data > From Text then you need to be a bit more careful because you have to tell it to use a comma delimiter. The text import wizard will appear -
  • On Step 1 make sure Delimited is chosen
  • On Step 2 make sure that Comma delimiter (not Tab) is chosen
If you forget to do this and Excel puts everything in a single column then all is not lost. You can use the text to columns utility to split the data out.


Does that help?
Reply With Quote
  #3  
Old 01-19-2012, 12:34 PM
Jamal NUMAN Jamal NUMAN is offline Opening the CSV file in the Excel has two different appearances! Windows 7 64bit Opening the CSV file in the Excel has two different appearances! Office 2010 64bit
Expert
Opening the CSV file in the Excel has two different appearances!
 
Join Date: Nov 2010
Posts: 496
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Colin Legg View Post
There are quite a few different ways to get the data from the CSV file into Excel.

When the data from the CSV file is imported, you may or may not need to tell Excel that it should use a comma delimiter. That is, each time Excel sees a comma in the CSV file, it understands that it has to put the subsequent data into a separate column. If no delimiter is used then Excel will just dump each entire line into the 1st column in the spreadsheet.


If you open the file within windows explorer then the comma delimiter will automatically be used so you will get the screenshot 2 result.
The same is true if you open the file in Excel by going File > Open.



If you import the CSV file by going Data > From Text then you need to be a bit more careful because you have to tell it to use a comma delimiter. The text import wizard will appear -
  • On Step 1 make sure Delimited is chosen
  • On Step 2 make sure that Comma delimiter (not Tab) is chosen
If you forget to do this and Excel puts everything in a single column then all is not lost. You can use the text to columns utility to split the data out.


Does that help?
thank you Colin for the answer.

The problem that i sometimes get the file open as in screenshot 1 and others as in screenshots 2 knowing that i use the same way to open the file (whether from the explorer or from the File>open in excel as shown in the attached screenshots)! this is why i got confused! same way to open the file but different format obtained! sometimes!

what might be behind this behavior?

Just in case i get them as in screenshot 1, is there away to have them converted to be like the format in screenshot 2?



regards

Jamal
Attached Images
File Type: jpg Clip_292.jpg (65.8 KB, 3 views)
File Type: jpg Clip_291.jpg (89.9 KB, 2 views)
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank.
Reply With Quote
  #4  
Old 01-19-2012, 01:36 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Opening the CSV file in the Excel has two different appearances! Windows 7 32bit Opening the CSV file in the Excel has two different appearances! Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi Jamal,

If you're opening the same file using the same method on the same PC, it's pretty strange that the delimiter would be honoured on some occasions but not others. It could possibly be due to the list separator not being set correctly in the Windows regional settings.
Quote:
Just in case i get them as in screenshot 1, is there away to have them converted to be like the format in screenshot 2?
Yes, certainly. You can select column A > Data > Text to Columns. Then the method is very similar to the import wizard steps I mentioned before.
Reply With Quote
  #5  
Old 01-19-2012, 02:14 PM
Jamal NUMAN Jamal NUMAN is offline Opening the CSV file in the Excel has two different appearances! Windows 7 64bit Opening the CSV file in the Excel has two different appearances! Office 2010 64bit
Expert
Opening the CSV file in the Excel has two different appearances!
 
Join Date: Nov 2010
Posts: 496
Jamal NUMAN is on a distinguished road
Question

Quote:
Originally Posted by Colin Legg View Post
Hi Jamal,

If you're opening the same file using the same method on the same PC, it's pretty strange that the delimiter would be honoured on some occasions but not others. It could possibly be due to the list separator not being set correctly in the Windows regional settings.

Yes, certainly. You can select column A > Data > Text to Columns. Then the method is very similar to the import wizard steps I mentioned before.
Thank you for the prompt answer Colin.

That's correct that i use the same method to open the file but not the same computer!

for the time being, I'm using my lab top, i'll check the setting of the "List separator" of the windows in the other PC and let you know.

is this the only reason for which i got the appearance shown in the screenshot 1?

regards

Jamal
Attached Images
File Type: jpg Clip_293.jpg (80.7 KB, 3 views)
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank.
Reply With Quote
  #6  
Old 01-20-2012, 02:49 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Opening the CSV file in the Excel has two different appearances! Windows 7 32bit Opening the CSV file in the Excel has two different appearances! Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi Jamal,
Quote:
That's correct that i use the same method to open the file but not the same computer
Ah ok, that sounds a little less strange then!
Quote:
is this the only reason for which i got the appearance shown in the screenshot 1?
If the CSV files are associated with Excel (when you double click on them in Windows Explorer your computer knows that it should open them into Excel), then the only thing I can think of is the regional settings list separator.
Reply With Quote
  #7  
Old 01-23-2012, 12:55 PM
Jamal NUMAN Jamal NUMAN is offline Opening the CSV file in the Excel has two different appearances! Windows 7 64bit Opening the CSV file in the Excel has two different appearances! Office 2010 64bit
Expert
Opening the CSV file in the Excel has two different appearances!
 
Join Date: Nov 2010
Posts: 496
Jamal NUMAN is on a distinguished road
Thumbs up

Quote:
Originally Posted by Colin Legg View Post
Hi Jamal,

Ah ok, that sounds a little less strange then!

If the CSV files are associated with Excel (when you double click on them in Windows Explorer your computer knows that it should open them into Excel), then the only thing I can think of is the regional settings list separator.
Great! It worked like a charm.
As you have advised, the setting of the “list separator” in windows affects the appearance of CSV file when it is opened.
Appreciated
Jamal
__________________
Jamal NUMAN, Jamal432@gmail.com, P.O.BoX: 731, Ramallah, West Bank.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening the CSV file in the Excel has two different appearances! Opening a file with FileDialogFilePicker ACA Word VBA 2 08-07-2011 12:43 PM
Opening the CSV file in the Excel has two different appearances! Issue when opening word file msadiqrajani Word 2 05-27-2011 08:11 PM
Opening the CSV file in the Excel has two different appearances! Opening multiple Excel files within the same Excel window. lost9471 Excel 2 05-01-2010 01:57 PM
File gets hanged up while opening in excel 2007 nvsudheer Excel 6 06-11-2009 05:47 AM
Opening MSO-2000 file in MSO-2003 w77 Word 0 04-21-2009 03:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft