0

I have downloaded a file from the following URL:

https://www.canmoney.in/IDT.XLS

It is the list of stocks eligible for intraday trading in my stock broker. when I read it using read.csv the folowing is the warning message and output:

> idt1 <- read.csv("C:/Users/user/Desktop/Rfiles/IDT1.XLS")
Warning message:
In read.table(file = file, header = header, sep = sep, quote = quote,  :
  incomplete final line found by readTableHeader on 'C:/Users/user/Desktop/Rfiles/IDT1.XLS'
> idt1
[1] ÐÏ.à..

the file contains 4 columns and 208 rows. Why is read.csv not redaing the file properly?

very many thanks for your time and effort...

smci
  • 32,567
  • 20
  • 113
  • 146
AKshayKulkarni
  • 57
  • 5
  • 2
    Because `read.csv` reads `csv` files , since this is an excel file use other libraries like `readr`. use `install.packages('readr')` to install and then you can read excel files as well – PKumar May 22 '18 at 13:21
  • 2
    Well, to start it's not a `.csv` file. You should probably use an appropriate function to read a `.xls` file. – divibisan May 22 '18 at 13:21
  • its an excel file see here: https://stackoverflow.com/questions/7049272/importing-excel-files-into-r-xlsx-or-xls – dpel May 22 '18 at 13:21
  • Easiest solution is open it in Excel and Save as CSV. – smci May 22 '18 at 13:25
  • @divibisan: it's not quite a duplicate of that question about read.xlsx and anyway those answers are cluttered with 7 years of package recommendations (needs culling). Whereas it takes all of 30 seconds to open the XLS file in a spreadsheet and Save as CSV. – smci May 22 '18 at 13:29
  • Use the XLConnect package: https://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf Data <- readWorksheet(loadWorkbook("C:/IDT1.xlsx"),sheet=1) – nak5120 May 22 '18 at 13:36
  • @scmi It takes even less time to use `read.xlsx` from `openxlsx` for example – Jaap May 22 '18 at 13:37
  • 2
    @smci opening in excel and saving as .csv is not a reproducible solution. There are several packages that can read .xls/.xlsx and would be the preferred method. `readxl` from tidyverse is one of the most common. – cparmstrong May 22 '18 at 13:38
  • All: well I had mixed experiences the previous occasions I tried to programmatically read XLSX in R, so I gave a solution that's 100% guaranteed and very fast. But yes `readxl` is good. – smci May 22 '18 at 13:41
  • FYI **the file is not XLSX, it's XLS**. I had actually downloaded the file and opened it. IME readers are generally less reliable on XLS than XLSX. And btw opening and saving as .csv is a reproducible solution; it's just not programmatic. It doesn't have to be "in Excel", LibreOffice will do fine. – smci May 22 '18 at 13:46
  • 1
    @smci it isn't reproducible if the user doesn't have access to spreadsheet software or needs a solution that is entirely contained in R, such as needing to run a script from the command line – camille May 22 '18 at 14:05
  • @camille I already said above it's "reproducible, just not programmatic". Not every solution has to be programmatic. I've been dealing with unreliable or flaky XL import libraries in Python, PERL and R for too long to care. – smci May 22 '18 at 14:38

2 Answers2

4

Because it's not a CSV file. It's an XLS spreadsheet. (Note to others: it's not XLSX)

read.csv is only for reading CSV files or other formatted text files.

Solutions:

  1. open it in Excel or LibreOffice, save as CSV.
  2. alternatively, use one of the R packages for reading XLS
smci
  • 32,567
  • 20
  • 113
  • 146
3

Try

library(readxl)
IDT <- read_excel("C:/YOURPLACE/IDT.XLS")
Peter
  • 2,120
  • 2
  • 19
  • 33