Help with locating error on import of data

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with locating error on import of data

R help mailing list-2
I have imported data from an Excel file and I am getting errors:

> library(readxl)
> Balance_sheet <- read_excel("Y:/All Documents/Training/Data/Routines for consolidating all the data/Individual tables/AIM companies/Balance_sheet.xlsx", na = "")
New names:
* `` -> ...6
* `` -> ...7
* `` -> ...9
* `` -> ...10
* `` -> ...11
* ... and 22 more problems


How can I find where the error is originating? What does New names mean?

Thanks in advance for your help.


Sent from Mail for Windows 10


        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with locating error on import of data

Patrick (Malone Quantitative)
It looks like it's looking for column names in the first row of your Excel
sheet and not finding them. What does the first row contain?

On Tue, Jun 23, 2020 at 10:57 AM Ahson via R-help <[hidden email]>
wrote:

> I have imported data from an Excel file and I am getting errors:
>
> > library(readxl)
> > Balance_sheet <- read_excel("Y:/All Documents/Training/Data/Routines for
> consolidating all the data/Individual tables/AIM
> companies/Balance_sheet.xlsx", na = "")
> New names:
> * `` -> ...6
> * `` -> ...7
> * `` -> ...9
> * `` -> ...10
> * `` -> ...11
> * ... and 22 more problems
>
>
> How can I find where the error is originating? What does New names mean?
>
> Thanks in advance for your help.
>
>
> Sent from Mail for Windows 10
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>


--
Patrick S. Malone, Ph.D., Malone Quantitative
NEW Service Models: http://malonequantitative.com

He/Him/His

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with locating error on import of data

Rui Barradas
In reply to this post by R help mailing list-2
Hello,

Try setting argument col.names = FALSE, it seems that the function is
not find column headers and is choosing its own.


Balance_sheet <- read_excel("Y:/All Documents/Training/Data/Routines for
consolidating all the data/Individual tables/AIM
companies/Balance_sheet.xlsx", na = "", col_names = FALSE)


Hope this helps,

Rui Barradas

Às 15:56 de 23/06/2020, Ahson via R-help escreveu:

> I have imported data from an Excel file and I am getting errors:
>
>> library(readxl)
>> Balance_sheet <- read_excel("Y:/All Documents/Training/Data/Routines for consolidating all the data/Individual tables/AIM companies/Balance_sheet.xlsx", na = "")
> New names:
> * `` -> ...6
> * `` -> ...7
> * `` -> ...9
> * `` -> ...10
> * `` -> ...11
> * ... and 22 more problems
>
>
> How can I find where the error is originating? What does New names mean?
>
> Thanks in advance for your help.
>
>
> Sent from Mail for Windows 10
>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

--
Este e-mail foi verificado em termos de vírus pelo software antivírus Avast.
https://www.avast.com/antivirus

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with locating error on import of data

jholtman
In reply to this post by Patrick (Malone Quantitative)
one of the problems with Excel is that people can put anything in any
column.  You might want to restrict which columns you are reading
since if it finds data in some cells and there is not a header, it
will create one.

Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

On Tue, Jun 23, 2020 at 8:09 AM Patrick (Malone Quantitative)
<[hidden email]> wrote:

>
> It looks like it's looking for column names in the first row of your Excel
> sheet and not finding them. What does the first row contain?
>
> On Tue, Jun 23, 2020 at 10:57 AM Ahson via R-help <[hidden email]>
> wrote:
>
> > I have imported data from an Excel file and I am getting errors:
> >
> > > library(readxl)
> > > Balance_sheet <- read_excel("Y:/All Documents/Training/Data/Routines for
> > consolidating all the data/Individual tables/AIM
> > companies/Balance_sheet.xlsx", na = "")
> > New names:
> > * `` -> ...6
> > * `` -> ...7
> > * `` -> ...9
> > * `` -> ...10
> > * `` -> ...11
> > * ... and 22 more problems
> >
> >
> > How can I find where the error is originating? What does New names mean?
> >
> > Thanks in advance for your help.
> >
> >
> > Sent from Mail for Windows 10
> >
> >
> >         [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide
> > http://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
> >
>
>
> --
> Patrick S. Malone, Ph.D., Malone Quantitative
> NEW Service Models: http://malonequantitative.com
>
> He/Him/His
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Help with locating error on import of data

Rasmus Liland-3
In reply to this post by Rui Barradas
On 2020-06-23 17:24 +0100, Rui Barradas wrote:
> Às 15:56 de 23/06/2020, Ahson via R-help escreveu:
> > I have imported data from an Excel file
> > and I am getting errors:
>
> Try setting argument col.names = FALSE, it
> seems that the function is not find column
> headers and is choosing its own.

Dear Ahson,

The openxlsx package is also worth checking
out for situations like this one.

First read the file into a workbook object,
then read the sheet names from the workbook,
lastly use the sheet names to read each sheet
from the workbook using lapply:

        xlsxFile <- "hmm.xlsx"
        wb <- openxlsx::loadWorkbook(xlsxFile)
        sheets <- names(wb)
        list.of.sheet.dfs <- lapply(sheets, function(sheet, wb) {
          openxlsx::read.xlsx(
            xlsxFile=wb,
            sheet=sheet,
            colNames=FALSE)
        }, wb=wb)
        names(list.of.sheet.dfs) <- sheets
        list.of.sheet.dfs

yields:

        $Sheet1
             X1   X2   X3        X4     X5
        1  this <NA> <NA> blablabla   <NA>
        2  <NA> does <NA>      <NA>   <NA>
        3  <NA> <NA> <NA>       not   <NA>
        4  <NA> <NA> make      <NA>   <NA>
        5 sense <NA> <NA>      <NA>     42
        6  <NA> <NA> <NA>      <NA> lalala
       
        $ReallyComplexSamples0003
            X1      X2   X3    X4     X5     X6   X7
        1 <NA>    <NA> <NA>  <NA>   <NA>   <NA> some
        2 <NA>    <NA> with  <NA>   <NA> other  <NA>
        3 <NA> random  <NA>  <NA>   <NA>   <NA> <NA>
        4 info    <NA> <NA> sheet   <NA>   <NA> <NA>
        5 <NA>  stuck    in  <NA>   <NA>   <NA> <NA>
        6 <NA>    <NA>   it  <NA> lalala    $$$ <NA>
        7 <NA>    <NA> <NA>  <NA>   <NA>      2 <NA>

Best,
Rasmus

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

signature.asc (849 bytes) Download Attachment