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

8

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!

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. 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.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

8 Comments

  1. 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.

  2. 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.

  3. 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.

  4. 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;

Leave A Reply

Back to Top