Blogs

Blogs

Don't panic: maybe your XLSX file is already opened

12
By Topics | Programming Tips

As you can tell from my recent posts (see here and here), I've been working with SAS and Microsoft Excel files quite a bit. I'm really enjoying the ability to import an XLSX file in my 64-bit SAS for Windows without any additional setup.

After one long afternoon of back-and-forth between Excel and SAS, I ran into this alarming error message:

ERROR: Error opening XLSX file -> C:\Projects\MyData.xlsx .  It is either not 
an Excel spreadsheet or it is damaged.   Error code=80001019
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

It's a scary message, and I panicked -- but just briefly. I soon remembered that Microsoft Excel likes to place an exclusive file lock on spreadsheet, so if you have it open in Excel, nothing else will be able to read it. My file wasn't "damaged" -- it was simply in use.

I could wish for a better error message like: "Excuse me sir, but it appears that file is already opened by another application. Would you mind closing it so that we can read the data? I mean, whenever it's convenient for you."

Once I closed Microsoft Excel and reran my SAS program, all was good. Whew!

Tags excel proc import xlsx
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

12 Comments

  2. Bill Kramer on

    Thanks for the tip. This has only happened to me since my recent (last weekend) switchto 9.4 64 bit from 9.3 32 bit. Interestingly, some Excel files can be read even when opened and some cannot.

  4. Ginny Blum on

    I wish it could return to you who has the file open.
    I've got an app with 35 little files I read in and if anybody is editing them it's a no-go.
    It's easy enough to identify the file that's open, figuring out who has it open is the hard part.

  5. Paul Kaefer on

    Thanks, Chris.

    I received a similar message today when using PROC EXPORT. At first I thought there was an issue with the file path, as SAS was printing two forward slashes to the log. But that seemed to be not an issue as is mentioned in this SAS Communities forum thread.

    However, nobody had the file open. Since my PROC EXPORT would create the file, I deleted the old copy, closed and reopened SAS, and it worked fine.

  7. Cameron Muir on

    The same error for me: I was calling the name of the file, but I actually needed to call the name of the worksheet for my PROC CONTENTS:

    libname np xlsx '/home/cmuir/pulse_export2.xlsx';
    options validvarname=v7;
    proc contents data=np._ALL_;
    /* rt_top_kws and rt_top_kws2 are the excel tab names - I was trying to use np.pulse_export2 which gave the Error code=80001019 */
    run;
    libname np clear;

  8. Patrice Bourdages on

    Hello Guys.

    Looked into all your intervention but I still haven't found a solution for this.

    Is there a way, from within SAS, to find out if the file lock (let's call it this way) is available before getting the error?

    Another way of saying "is it possible to find out who's locking the file" ?

    • Chris Hemedinger
      Chris Hemedinger on

      This is not the only cause for the message. It could be the file is locked by a different process, or there could be some other problem with the file. It might be a spreadsheet file that isn't native Excel (like the style created by EXCELXP tagset, which creates an XML file that Excel can read but other apps cannot).

Back to Top