Error in Importing Excel Data

[Xiao Zhang]

Dear Stata users,

I am using StataMP 14.1. I am having a problem reading data in .xlsx format. Here are the codes that I used:

clear
import excel "D:\FirmInfoAffiliation12.15.xlsx", sheet("FullList") firstrow

After I run the code, I got the following warning: “file D:\FirmInfoAffiliation12.15.xlsx could not be loaded
r(603);”

I also tried to read the data manually: File-> Import -> Excel Spreadsheet. I got the following warning message.


The same data can be read on my college’s PC, so the data should have no problem. Or it could be that the data have some problems but my college's PC managed to solve the problem and read the data.

All of these happened very suddenly. I did not update the Stata package. And I did not update excel as well. I tried to read other data in .xls format and Stata works well.

Attached are two samples of my data which is confidential so I can only show you a sample. Book2.xlsx Book1.xlsx
Both samples have two rows and 39 variables. The first row contains variable names, and the second row contains the data. What's more confusing is that the first sample, Book1.xlsx can be read while the second sample Book2.xlsx which contains exactly same data as Book1 cannot.

I googled and found some similar questions. The only solution that works for me is to save as .xls, but I have quite a lot of data in xlsx format. And the data size is limited if I use .xls.

Anyone suggestions are welcomed. Thank you very much.

[Xiao Zhang]

Another solution is to use ODBC load. But I have to set each excel file as an ODBC data source manually before I can read it.

[Joseph Coveney]

You might want to make sure that your Stata is up-to-date. (Actually, it's not, because you can update to Version 14.2.)

Otherwise, you can automate using ODBC using an ado-file that just requires the path and file name, with a worksheet option (if it's not Sheet1). Try something like the following

program define loadem
    version 14.1
    syntax anything, [Sheet(string)]

    local dsn Excel Files;DBQ=`anything';
    local sheet = cond(mi("`sheet'"), "Sheet1$", `"`sheet'"')
    odbc load, table(`"`sheet'"') dsn("`dsn'") clear
end

And its syntax (on Windows at least) would be

loadem D:\FirmInfoAffiliation12.15.xlsx, sheet(FullList$)

[Xiao Zhang]

You might want to make sure that your Stata is up-to-date. (Actually, it's not, because you can update to Version 14.2.)

Otherwise, you can automate using ODBC using an ado-file that just requires the path and file name, with a worksheet option (if it's not Sheet1). Try something like the following

program define loadem
version 14.1
syntax anything, [Sheet(string)]

local dsn Excel Files;DBQ=`anything';
local sheet = cond(mi("`sheet'"), "Sheet1$", `"`sheet'"')
odbc load, table(`"`sheet'"') dsn("`dsn'") clear
end

And its syntax (on Windows at least) would be

loadem D:\FirmInfoAffiliation12.15.xlsx, sheet(FullList$)

Thank you very much Joseph! It worked well.