Importing CSV into Excel - problem

  AroundAgain 01:46 27 Jun 2009


Using Win XP; MS Office Prof 2003; OpenOffice 3.1

When trying to open a particular (phone usage) .csv file into Excel, although the individual items (phone calls) are in their own row, all the data relating to these are in one column, not in separate columns as expected.

I have tried using tab separated only, comma only, tab and comma but always with the same outcome.

This same .csv file opens fine in OpenOffice Calc, thus I can filter, manipulate etc

Are there some settings somewhere I should be checking. Apparently the file opens perfectly in 'ibilling', of course!!!

Thanks to anyone who can shed light on this.

  Taff™ 09:32 27 Jun 2009

The obvious suggestion would be to open it in Calc and then save the file (Save As) in an Excel (.xls) format by changing the File Type drop down.

  AroundAgain 10:38 27 Jun 2009

Taff, thanks for your response and I appreciate what you say.

I have, in fact, kept it in Calc but could copy to Excel if I need different functions.

However, now, it's a challenge! Excel should be able to open .csv files, the file is seemingly ok so I am keen to get to the bottom of this.

Maybe it's something I'm not doing or maybe there are some settings, or something in the installation of the program.

Does anyone have any ideas, please?

  Taff™ 13:15 27 Jun 2009

Yes it should open in Excel, certainly does in Excel 2002. Try right clicking the file in Windows Explorer and choose "Open With" and select Excel - any different?

  AroundAgain 14:17 27 Jun 2009

Hi Taff

Thanks again but unfortunately that doesn't work either. I had tried that but have just tried again, with same result. I have just been trying 'all which ways' I can think of, hence the time to respond!

I can only get this file open, into columns as it should open, by going via (new worksheet) Data->Imported Ext Data->Imp Data->Select File->Opens Text Import Wizard; Then I need - Delimited checked; Comma ticked; Text Qualifier->None; tab can be either, Consec Delim leave unticked.

Also, having copy/pasted the column with all data into Word, saving as text file, I can also open into approprate columns by going via Data->Imp Ext Data, ticking 'comma' but can leave Text Qual to ".

I have tried all and sundry that I can think of, but this seems to be the only way.

Does this suggest my installation, or settings, of Excel are not right. Maybe I should reinstall Office, perhaps?

Previously, but not necessarily on this installation, .csv files have just 'opened', without problem.

thanks again for any help with this
It's really bugging me!!! ;)

  VoG II 14:38 27 Jun 2009

Select the data in the column in Excel, Data > Text to Columns > select Delimited and follow the prompts.

  AroundAgain 15:48 27 Jun 2009

Hi VoG
Thanks, that's certainly an easier way to get the data into columns!

However, shouldn't Excel be able to open .csv files straight off, without all this?

I'm still wondering if there is something amiss with my installation?


  Terry Brown 13:14 01 Jul 2009

Try right clicking on the CSV file , select open with, and select Excel.

This should open it directly into Excel instead of having to convert it from Open Office Calc first.

  AroundAgain 20:46 01 Jul 2009

Hi Terry
Thanks for your suggestion.

Yes, I agree this should work but, unfortunately, isn't the solution.

The .csv file will open but not in separate columns as they should do, hence having to use 'alternative' measures.

More detailed reasons etc are in a previous reply to Taff on Sat, 14.17hrs - yes a bit of a longer post where I maybe didn't explain very well! :)

Again, Terry, thanks for your input. It's appreciated.

If anyone has any other suggestions as to why the .csv file won't open in separate columns, I would really appreciate hearing them.

Thx J

  Terry Brown 14:47 02 Jul 2009

Try opening Excel, going to DATA then importing External data.
This may work.

  AroundAgain 23:16 02 Jul 2009

Thanks guys
Seems I have learnt 101 ways to swing a cat -oops! - open a csv file. ;)
Very grateful for all responses
Thx again, J

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

HomePod review

Les meilleurs logiciels de montage vidéo gratuits (2018)