I have a problem converting an Excel sheet in a *.csv file. I've searched all day for solutions, but nothing seems to work.
After I select the sheet and save it as "CSV(Comma delimited)", pressing OK and Yes as Excel tells me there could be some problems, the file opens in format .csv without putting all the date in one column separated by commas… it just remains the same and obviously I get an error when I try to open it with R.
Is there someone who had the same problem? Did you find the solution? I even uninstalled and reinstalled Microsoft Office 2010 or cancelled the last two columns because they contain commas. Nothing works…
I would really appreciate if you could help me. I really don't know what to do next and I need it for my final paper for Master's degree.
Thank you!
Best Answer
Your problem is a misunderstanding of how Excel handles CSV files when it opens them.
Open the file in a text editor, for example Notepad, and you will see that the data within the file is in fact separated by commas.
However, Excel automatically puts the data in columns when it opens a CSV file. This is how most people want to use a CSV.
If you do want to open a CSV in Excel but with all the data displayed in one column separated by commas, instead of opening it, create a blank sheet in Excel and go to Data ยป Get External Data > From Text, then choose "fixed width" rather than "delimited".
Alternatively, simply open the CSV file in Word or Notepad, and copy it from there into excel.