After updating excel I can no longer import .csv files to r

General
1

Hi everyone,

I keep the majority of my data in .csv documents where my opening default is excel. this morning I updated excel and now suddenly I'm getting this error "incomplete final line found by readTableHeader on 'test .csv'".

With some research I saw that usually going to the last line of the file and pressing enter solves this problem, but that didn't work. I can upload .xls files using readxl but I like keeping everything as a csv because its just easier to use in r.

If I try and upload data I complied before the update I don't get an error, and it's fine.

any advice?

2

What program did you do this in? It will not work if you did it in Excel.

Try opening the file in a text editor (e.g. Notepad or Sublime Text) and scroll to the bottom. Check if there are the correct number columns as indicated by the number of commas and pressing enter at the end of the last line you see there.

You can check this if the editor has a search or find function that allows the use of regular expressions. If you search for \n it should highlight the end of each line.

Also you might find this CSVLint tool useful as it checks your CSV files for errors.

3

In text editor I don't have any actual lines. Not sure why everything was bunched all together. But I fixed the problem by adding then in by hand.

4

If your data was all one line when viewed in the text editor, it matters a lot which text editor you used. Notepad only recognizes old-style Windows line endings, which are \r\n rather than the UNIX-style (and now more generally common) \n. Meaning your file may have been just fine, even though it looked weird in Notepad. And introducing new line endings in Notepad might make the file harder to import into R. More sophisticated text editors (e.g., Sublime Text or the free Notepad++) let you control the line endings (and see what they are).

Confusingly, Excel has at least 3 different CSV export formats, each of which does something slightly different with encodings and line endings (and at least one of which — the “Macintosh” format — makes very little sense nowadays since it uses line endings that have been out of date since the introduction of OS X when the Mac got UNIX-y). Here’s some more detail on the many ways to attempt CSV export from Excel: https://www.ablebits.com/office-addins-blog/2014/04/24/convert-excel-csv/

2 Likes