4

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!

  • 1
    You are going to have to show us what you mean here, either with screenshots of the program or contents of the file or both. As it stands I am finding it difficult to work out what your actual problem is. – Mokubai Nov 24 '13 at 17:03
  • 1
    When saving to .csv, the result is a text file for which a line is a row, and each columns are separated by comma. When Excel opens a CSV, it understands it and displays it in a table format. Open the CSV with notepad and you'll see all the columns and rows separated with comma. – Guillaume Rochat Nov 24 '13 at 17:54
  • Is this how your reading your file in R? dat = read.csv("name.csv", header = TRUE) – cybernard Mar 10 '18 at 15:14
3

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.

| improve this answer | |
  • @mcEmperor thanks for the edit, definitely an improvement – Some_Guy Apr 13 '17 at 14:45
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!

| improve this answer | |
1

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

| improve this answer | |
0

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...

| improve this answer | |
-1

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.

| improve this answer | |
-2

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.

| improve this answer | |
  • 4
    Excel uses the delimiter which is set under Regional and Language Options > List separator. So changing from semicolon to comma might work in your case but it probably wont for others because for the majority out there comma is the default – nixda Aug 1 '15 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.