Importing excel

[Paula de Souza Leao Spinola]

I am having trouble importing a 993 thousand rows excel to stata. Is it possible to do somehow?
Thank you!

. import excel using "C:\Users\Paula\Docs\Pesquisa\DADOS\dados_georef\CEPs_latlong\dados_brutos\tabela_integrada_geo.xlsx", fi
> rstrow case(lower)
file C:\Users\Paula\Docs\Pesquisa\DADOS\dados_georef\CEPs_latlong\dados_brutos\tabela_integrada_geo.xlsx too big
r(601);

[Nick Cox]

The help is informative:

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.

Is that the problem?

[Clyde Schechter]

I don't normally work with data sets this large, so I can't be sure if either of these approaches will accommodate your needs, but you could try:

1. Try having Excel save the data as a .csv file and then try bringing it into Stata with -import delimited-. To my knowledge, there is no size limitation on that command.

2. StatTransfer from CIrcle Systems can translate between Excel and Stata. It does have size limitations, but I believe they are liberal enough to accommodate this particular file.

That said, under any circumstances I think this will prove to be a very long, slow process.

[Friedrich Huebler]

See also

set excelxlsxlargefile on

Before you try this, please read the warning here: http://www.stata.com/statalist/archi.../msg01157.html

[Jorge L. Guzman]

I do it the old way.

I open the excel document and save is as a "tab delimited" text file.

Then I go on stata and...

clear all

insheet using "file_name.txt"

That should do it, regardless of the size of your file.

You're very welcome .

Jorge

[Friedrich Huebler]

From help insheet:

insheet has been superseded by import delimited. insheet continues to work but, as of Stata 13, is no longer an official part of Stata.

[Jorge L. Guzman]

That's very sad to hear. I am going to keep using Stata 12, then.

[Jorge L. Guzman]

Paula, if you want to, I can convert the file to a .dta file and send it to you via dropbox or google drive. It should take my like 3 minutes.

Best wishes,

Jorge

[Hua Peng (StataCorp)]

-insheet- in Stata 13 and 14 is no longer documented but still works. It works the same as of Stata 12 and it will keep working in future version of Stata. The reason to stop documenting -insheet- is that we introduced -import delimited- since Stata 13, which has more functionalities than -insheet- dealing with delimited text files. So if Paula decides to follow jorge L.Guzman's advice, he can do it in Stata 13 or 14.

[Nick Cox]

Jorge: In my experience, import delimited is much better than insheet. If you like insheet that's good, but you would transfer your affections quickly to import delimited.

[Jorge L. Guzman]

Thank you, Nick. I'll look into it. You've made me curious, now.

Best wishes,

Jorge

[Sem Vandekerckhove]

Hi all

I was looking on the forum for a comparison between insheet and import delimited. My experience is that the old insheet is more than six times faster than import delimited (2.57 versus 18.02 seconds). I have timed this on a 30mb chunk that had semicolon delimiters (extra information: Stata 14.2 on lowest spec Macbook Pro 2016, file has Windows line endings).

Hence although import delimited has more options, for infiling large csv's I would recommend insheet until further notice.

[Jorge L. Guzman]

Hi, I decided to use import excel, as Nick suggested, and it was very nice. Thanks for the tip.
Kind regards,
Jorge