R- import CSV file, all data fall into one (the first) column

Question

I'm new, and I have a problem:

I got a dataset (csv file) with the 15 columns and 33,000 rows.

When I view the data in Excel it looks good, but when I try to load the data into R- studio I have a problem:

I used the code:

x <- read.csv(file = "1energy.csv", head = TRUE, sep="")
View(x)

The result is that the columnnames are good, but the data (row 2 and further) are all in my first column.

In the first column the data is separated with ; . But when i try the code:

x1 <- read.csv(file = "1energy.csv", head = TRUE, sep=";")

The next problem is: Error in read.table(file = file, header = header, sep = sep, quote = quote, : duplicate 'row.names' are not allowed

So i made the code:

x1 <- read.csv(file = "1energy.csv", head = TRUE, sep=";", row.names = NULL)

And it looks liked it worked.... But now the data is in the wrong columns (for example, the "name" column contains now the "time" value, and the "time" column contains the "costs" value.

Does anybody know how to fix this? I can rename columns but i think that is not the best way.

Answer 1

Excel, in its English version at least, may use a comma as separator, so you may want to try

x1 <- read.csv(file = "1energy.csv", head = TRUE, sep=",")

I once had a similar problem where header had a long entry that contained a character that read.csv mistook for column separator. In reality, it was a part of a long name that wasn’t quoted properly. Try skipping header and see if the problem persists

x1 <- read.csv(file = "1energy.csv", skip = 1, head = FALSE, sep=";")

In reply to your comment: Two things you can do. Simplest one is to assign names manually:

myColNames <- c(“col1.name”,”col2.name”)
names(x1) <- myColNames

The other way is to read just the name row (the first line in your file) read only the first line, split it into a character vector

nameLine <- readLines(con="1energy.csv", n=1)
fileColNames <- unlist(strsplit(nameLine,”;”))

then see how you can fix the problem, then assign names to your x1 data frame. I don’t know what exactly is wrong with your first line, so I can’t tell you how to fix it.

Yet another cruder option is to open your csv file using a text editor and edit column names.