Problem with a big database

[Fiamma Benson]

Hello, I want to open an excel file, import it, but it does not leave stata 14, this excel file has 53.6 mb and the pop-up window tells me that for this type of file the maximum capacity is 40 mb. Could you guide me how to proceed? They've been working on this database SPSS, but as I have Stata, prefer to work it with this program

[Justin Niakamal]

Have you tried

set excelxlsxlargefile on

This will allow -import excel- to bypass the size checking.

[Clyde Schechter]

I was unaware of -set excelxlsxlargefile-. At least in Stata 15, it is not mentioned in the help for -set-, nor does it appear in the manual documentation for -import excel-. The closest one finds to it is this quote from the manual:

For performance, import excel imposes a size limit of 40 MB for Excel 2007/2010 (.xlsx)
files. Be warned that importing large .xlsx files can severely affect your machine’s performance.

-set excelxlsxlargefile- also does not appear in -help undocumented-, although I found that if I issue that command, I get non complaint from Stata (though I did not try importing a large .xlsx file).

So, 1) I'm curious how Justin Blasongame found about this feature.

2. If Stata has buried this so deeply and warns against its use, I think there is probably a good reason. Since you have colleagues who are already using this file in SPSS, you might be better off having somebody translate the SPSS file directly to a Stata data set. There are at least two ways you can do this. If you have a computer that has both SPSS and Stata installed, you can us Sergiy Radyakin's -usespss- command (available from SSC) to do the conversion. If there is no machine with both, then you can use StatTransfer (purchase from Circle Systems, Inc.) StatTransfer is not expensive and is excellent value for the money if you are going to be in this situation more than just occasionally. In my experience they also provide excellent customer support.

[David Benson]

The excelxlsxlargefile command has been mentioned a couple of times on Statalist (see here and here)

But Hua Peng from StataCorp mentions it with this caveat:

There is an undocumented setting

 set excelxlsxlargefile on

which will allow -import excel- to bypass the size checking. But You should be warned, the library we use to import Excel files has a large memory footprint when dealing with large xlsx files. Also the library currently has no ability to allow user to break during the middle of loading an Excel file. Hence when you attempt to load this 155MB xlsx file, the Stata session will become unresponsive until it finishes. During this time, you will not be able to break out using the break button.

I would try saving the specific sheet in Excel as a comma-delimited file (.csv) or tab delimited file, and then use import delimited to import the file into Stata. Also, you don't mention which version of Excel you are using, but Excel 97-2003 (.xls) files are considerably larger than equivalent Excel 2007, 2010, 2013, etc (.xlsx) files. So if it is an Excel 97-2003 (.xls) file, merely saving it as a .xlsx file might be enough to get it < 40 MB.

[Justin Niakamal]

Clyde, my mistake. I should've provided the Statalist references that David Benson mentioned. Thanks, David.

[Joro Kolev]

It is fun to be shocked by unexpected limits such as 40mb file size limit when you import from native Excel files. 40mb file size limit brings me back in time to year 1995-1999 when I had a desktop with 32mb of RAM... Then in year 1995-1999 a 40mb file would have scared me plenty.

It is also fun to read of undocumented mysterious features of Stata, so in this respect what Justin informs is fascinating.

Yet I would second part of what David Benson recommends in more opinionated way and with some historical remarks

1. The native import from Excel is a new feature that apparently was not present in Stata 11, and appears first time in Stata 12. One might wonder, how were we living without such a feature ever before Stata 12 ?

2. The standard procedure was and to me still is (I have never used native import from Excel so far in my work), you open your file in Excel, you save it in tab delimited or csv format, and you import it in Stata in this format. I do not think that there is any limit in Stata for importing text (tab delimited or csv ) files. Except for the amount of RAM you have.

[Sergiy Radyakin]

Joro Kolev regarding #2, the advantage of reading and writing Excel files directly is the possibility to share a file between two live systems, which continue to work on the same data. Re #1, before Stata 12 StatTransfer was (and still is) an option for moving data from Excel to Stata (and to many other destinations too). Best, Sergiy

[Joseph Coveney]

The native import from Excel is a new feature that apparently was not present in Stata 11, and appears first time in Stata 12. One might wonder, how were we living without such a feature ever before Stata 12 ?

I don't remember when it became available in Stata, and it has its limitations, but prior to Stata's native import/export feature I used ODBC (help odbc) to move data back-and-forth directly between Stata and Excel.

But mostly I used Stat/Transfer—money well spent. Despite the native import/export feature, I still keep Stat/Transfer upgraded, and use it most commonly today for SAS and SPSS datasets that I receive.

Anymore, ODBC is used for work with relational databases.