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!

Anita
  • 41

10 Answers10

4

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.

Some_Guy
  • 794
3

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!

Ben
  • 31
  • 1
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

joseph praful
  • 21
  • 5
1

Let me quote from this answer:

Changing the comma to semicolon when saving a CSV file is easy. Here's how:

  1. Click on the Start menu.
  2. Type control panel.
  3. Click on Control Panel(Desktop app).
  4. Select Clock, Language, and Region.
  5. Click Region.
  6. Select Additional settings in the pop-up window.
  7. Change the List separator to comma.
  8. Click Apply.
  9. Hit OK.

Once done, try saving your CSV file and check if you will see comma instead of a semicolon.

Nonah Chan
  • 11
0

All of the above don't work for me, at least on mac. Therefore, here is my small workaround using python:

import pandas as pd
csv = pd.read_csv(r"filepath/Book30.csv",sep=";")
csv.to_csv(r"filepath/Book30.csv")
pd.read_csv(r"filepath/Book30.csv")
Julius Reinhold
  • 1
0

I had the same issue, worked on it for a day. hahaha. IN the end it was very simple to solve. Just opened the text (CSV) file in notepad, and search/replace the ; with an , This solved the whole issue.

Jaap Bovendorp
  • 1
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...

Carrie
  • 1
-1

Excel still is exporting, saving, and saving as a basic text doc with NO SEPERATORS other than spaces, soft returns, and hard returns. In no way does it EVER export actual delineated files no matter what format is chosen. Excel is fully broken. Its been broken for YEARS. It is still broken. Microsoft is not fixing it. We simply have to use other (open source stuff most likely HAHAHA) since microsoft is garbage.

Jacob L.
  • 1
-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.

Firee
  • 1,910
-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.

suspectus
  • 5,008
Jason
  • 1