Excel saves a file in .csv but it keeps the columns and the rows as they were in the original sheet

Question

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!

Answer 1

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.

Answer 2

Found the answer!

I was having the exact same issue. I had a regular Excel file, would hit Save As, and select the first CSV option which is;

CSV UTF-8 (Comma Delimited)

This would give me all of the data in one column. However, if you look down the list of save types, there is also;

CSV (Comma Delimited)

Choosing this option fixed the issue completely and kept all of my columns intact.

Good luck!

Answer 3

I had the same problem. But I found out that my excel was using semi-colon as the separator instead of comma. The problem was solved by changing the option for 'List separator' in 'Region & Languages' --> 'Supplementary Parameters'.

https://stackoverflow.com/questions/10140999/csv-with-comma-or-semicolon

Answer 4

Because I am unclear on the format of your original file, I will take an initial a stab at a possible issue. On the original excel file, have you set the formatting of the column with the date information and then resave it as CSV?

Or maybe add a column in front of your date column, and copy ' down the new column (not sure how large your data is) use the concatenate function and add ' to your date column then save as CSV

Good luck on your paper...

Answer 5

This seems to a regular problem which Excel 2007 and above users face. In one sense it is a blessing in disguise, because you are saved a couple of steps if you want to view a CSV file.

But what I understand from your question, you want to ascertain if it has been converted to CSV or not. Once you save the file in CSV, try opening the file with Notepad, you will see what you want to see.

Answer 6

The problem you're having is because when xcel saves a sheet as CSV, it actually separates the values with a semicolon (;) and not a comma.

So once you've saved the sheet as a CSV, close the sheet and then open the sheet with notepad. Here you will see the values separated with ; - Now click Edit, Replace, and in the "Find" field type the semicolon (;) and in the "Replace" field type a comma (,) and click "Replace All" Save the notepad, close it and then open it in excel and you should see your columns gone.

I've just been struggling with the same problem, and this is how I solved it.