Argument out of range while importing .xlsx file

[Rob Henst]

Hi All,

I have been importing the same .xlsx file for the past two years without any problems. I keep updating the file, adding both rows and columns. Recently, I received the following error after entering more data:

. import excel "/Users/Rob/Documents/Participant data list_no format.xlsx", sheet("All_data") firstrow case(lower) clear
if invalid varname
exit(): 3300 argument out of range
import_excel_read_numcol(): - function returned error
import_excel_load_file(): - function returned error
import_excel_import_file(): - function returned error
<istmt>: - function returned error
r(3300);

I tried converting to .xls and .csv, both gave the same error. When I import the data manually (i.e. copy everything from the excel file and paste it into the data editor, it works fine). When I try to import using File > Import > Excel Spreadsheet, and select the file to be loaded, it only displays 256 columns. Currently, the file has 767 columns and 117 rows. The file size is 585 kb. I run Stata/IC 12.1 for Mac (64-bit Intel).

Why does Stata no longer import the file via the Excel data import? Has the maximum file size or column-width for imports been reached? Is there a away around it?

Looking forward to your answers.

Thank you so much.

[Joseph Coveney]

Using Stata 15 MP, 64-bit, I just created an Excel workbook (.xlsx) with a single worksheet of 767 columns and 117 rows, and read it back in without a problem.

What is the variable limit on Stata InterCooled Release 12? If that's not the source of your problem (and apparently it's not, if copy-and-paste works), then maybe with all of the modifications your Excel workbook's got corrupted or misconfigured in a manner that Stata cannot read the column tally anymore.

You could bite the bullet and copy-and-paste into Stata as you say, then export to a fresh Excel workbook if Stata 12 has that ability.

[Rob Henst]

Thank you for your reply @Jospeh.

It is possible that Stata cannot read the file properly anymore, the excel spreadsheet does have formulas and conditions.

I tried importing in Stata and then exporting again. The exported file could be imported into Stata, so it can't be a variable limit. Also, the file size limit is 60mb, I am not even close to that.

Obviously, the exported file helped me understand that the problem is not the variable limit, but I cannot use this exported spreadsheet as I need the formula's.

Why does the "if invalid varname" and "argument out of range" in the error message mean?

I already tried checking if anything strange was going on with the 256th or 257th column, but those are columns with just a number (0-4) without any formulas.

Thanks.

[Nick Cox]

if is a reserved word and can't be used as a variable name. It may be that your column headers seem to imply that a column should be mapped to a variable called if but Stata won't allow that. It may not be as simple as a bare if, as Stata would tend to strip many other characters any way.

As the documentation tells you:

11.3 Naming conventions

A name is a sequence of 1 to 32 letters (A–Z, a–z, and any Unicode letter), digits (0–9), and underscores ( ).

Programmers: Local macro names can have no more than 31 characters in the name; see [U] 18.3.1 Local macros.

Stata reserves the following names:
all float n skip
b if N str#
byte in pi strL
coef int pred using
cons long rc with
double

You may not use these reserved names for your variables.

In principle, it is easy to check this by looking at the first row of your worksheet.

On the argument being out of range: sorry, I am out of guesses on that.

[Rob Henst]

Thanks Nick. There are Three "if" in the entire document: In "shiftwork1", "shiftwork2" and "disqualify". These are some of the first columns. I also don't think the error code reads that "if" is an invalid varname, it simple quoted the script which has an if-function, which more literally reads:
"if the varname is invalid, exit the function".

I just tried Joseph's recommendation again, this time while selecting the "use labels as column header" instead of "use variable names as column header". The difference is that the labels are given by stata if the chosen variable name cannot be used (when in duplicate for example). It then gave me the following error:

"too many or no variables specified"

This leads me to believe it is definitely something with the labels. Can someone confirm that this is the case, with the new information I provided?

Thanks so much!

_____________edit_______________

When I import the workmap with selecting "Import first row as variable names", the import works fine, but the variable names are not as I want them. In short:

This works:
import excel "/Users/Rob/Documents/Participant data list_no format.xlsx", sheet("All_data") clear

This does not work:
import excel "/Users/Rob/Documents/Participant data list_no format.xlsx", sheet("All_data") firstrow case(lower) clear

So then I noticed the other option that was different is case(lower). I removed that, popped the firstrow option back in:

This does work:
import excel "/Users/Rob/Documents/Participant data list_no format.xlsx", sheet("All_data") firstrow clear

BOOM! Everything works as it should; problem solved.

Thank you so much Joseph and Nick, without you I wouldn't have even tried these things.

Now I wonder why the case(lower) option caused this error?

___________second edit______________

I found the culprit. When selecting "case(lower)", the column headers would at one point be "ia, ib, ic, id, ie, if". As Nick pointed out, "if" is reserved in lower-case. That's why it works if you remove the case() option or select case(upper).

Thanks Nick, you were right! The error message actually did read "if is an invalid varname".

Thanks again everyone!

[Nick Cox]

Manifestly I don't have your file to experiment and I haven't had experience of import excel crashing on me that I can recall.

I am offering a conjectural diagnosis of what I thought you reported, namely an error message that included

if invalid varname

which I copy and paste here from your #1. So I don't understand which "script" you are referring to or your grounds for a different diagnosis. But I see that the column headers don't seem to explain the problem.

So far, that's just useless from me to you for working out what's wrong. If you can, I would share the file with StataCorp Technical Support. Apart from any confidentiality or security concerns, I can't recommend posting the file here as it is too large to be practical.

[Rob Henst]

Hi Nick,

Please see my edits in my post that you replied to.

You were right, the problem was caused by the varname "if", which came after the varnames ia, ib, ic, id and ie in the column headers. This problem only occurs when the case(lower) option is selected, as "IF" is an allowed name.

The file contains sensitive patient information and can therefore not be shared with StataCorp Technical Support.

The new problem I encountered is that all column headers are named a, b, c, etc. even when the "firstrow" option is selected. I doesn't seem to be able to name the varnames as the first row in the excel spreadsheet.

Shall I open a new post, as this original post has been resolved?

[Rob Henst]

This person seemed to have the same problem that I have:

https://www.statalist.org/forums/for...me-not-working

[daniel klein]

The file contains sensitive patient information and can therefore not be shared with StataCorp Technical Support.

From what you describe the problem seems to be reproducible with the first row of the Excel sheet. I would delete all other values or replace them with random numbers so there will no longer be any confidentiality issues. Then sent the file with a syntax reproducing the error to tech-support. Stata's import excel really should not choke on invalid names and if it does this is a bug.

Best
Daniel