Error 603 when trying to import an Excel file

[Klaudia Erhardt]

Hello,

I use Stata 13.1 in a network environment.

I have to combine multiple excel files from certain directories on our server. (In the moment: 536 files). I wrote a routine to import the excel files, but 20 of 536 files produce an Error 603 which means, file exists, but cannot be opened.

All Excel files have been produced by a script (not in stata), so one should assume that either no file or all files are importable.

After opening one of the problem-causing files in Excel and saving it anew, the file could be imported in Stata. But as I have to deal with a host of excel files which change often and have to be imported anew, the solution to the problem must be an automatic one.

I screened the Internet and the Stata list and saw that other people had the same problem, but I did not find a solution that was to be included in a stata do-file and executed automatically.

I add the syntax I use, but I don't think it is a problem of my syntax, because most of the excel files open ok.

I hope someone knows the reason for the problem which I suppose lying in the Excel file. Or maybe it is caused by my Stata settings or something like that.

Klaudia

  
clear
set more off

local pfad "/home/soepdist"  /* Main path (stone) */

/* Directories containing Excel-Files */
local pfad1 "`pfad'/labels_en"
local pfad3 "`pfad'/make_v31/labels_en/xls"

local sprlist = "pfad1 pfad3"
 
foreach spr in  `sprlist'    {
    clear
    local dirct "``spr''"    /* dirct: Path to one of the SOEP source-files directories */
    local allf : dir "`dirct'" files "*.xls", respect
    local allf = subinstr(`"`allf'"',`"""',"",.)  /* eliminate double quotes */
    local allf = subinstr(`"`allf'"',".xls","",.) /* eliminate file type appendix */
    local nds : word count `allf'
    local probl ""
    
    forvalues i = 1(1)`nds'    {
        local datei = word("`allf'", `i')
        capture import excel using "`dirct'/`datei'.xls", describe
        local c = _rc
        
        if `c' != 0    {
            if `c' == 603    {
                display " "_n ///
                "Error `c': File `dirct'/`datei' was found but failed to open properly"
                local probl = "`probl'" + " `datei'"
            }
            else {
                display " "_n ///            
                display "Error `c'"
            }
        }

        if `c' == 0 {
                import excel using "`dirct'/`datei'.xls", describe
        }
    }

    local nf : word count `probl'
    if `nf' > 0    {
        display " "_n ///
        "The following `nf' Excel files from `dirct' could not be imported, ${S_DATE} : "_n ///
        "`probl'"_n ///
        "   "
    }
}

[Carole J. Wilson]

In the past, I have run into some similar problems when my computer was indexing files or my virus protection program was scanning files. In those cases, pausing the indexing or scanning program solved the problem. So, there may be other programs that are accessing those files and thus preventing Stata from opening them. Of course, also make sure that the files are not open. Office programs do not like to share!

[ben earnhart]

I've had similar problems reading/writing to network folders that can't keep up with Stata. Using "sleep" helped, by having Stata pause until the file system could catch up.

Oh, and another alternative: have Stata copy the files over to a local directory and work on them there. That way others accessing them isn't an issue, and your hard drive should be able to keep up. When finished, have Stata delete them.

[Carole J. Wilson]

Good point, Ben! I've run into that, too, and I'll stick in sleep 500 whenever I worry about a lag. It is easy to implement and might work for Klaudia.