[carter dreymond]

And just to clarify, my Stata isn't big enough (Stata/IC) so I split up the work into 4 parts (which is why I had sub .dta files that I had to append together after appending the Excel files within each of the 4 parts.

[daniel klein]

This is getting a bit confusing. If you append files, then you are adding observations, not variables. Stata has (virtually) no limit for observations, irrespective of flavor (IC, SE, MP) and even if there was such a limit, it would bite whether you load a file at once or append it step by step, because either way the resulting final dataset would contain too many observations.

Further, are you really sure these datasets fit together? There are various incompatibility issues here. Variables seem to be of different type or even non-existing in some of the datasets.If you want to append these files (not merge or anything - you need to be clear on this), then you have to be sure that all Excel files use the same variable names (column names) for the same variable contents. Say, AF contains the number posts in a thread to solve a problem in the first Excel file, then in the second, third, ... Excel file if there is a variable (column) AF, it should also contain the number of posts in a thread to solve a problem. What you describe here sounds like this is not the case.

Can clarify what exactly your Excel files contain and how they are set up?

Best
Daniel

[carter dreymond]

Apologies for the confusion. Even I get confused with the amount of information within them.

So the excel files all contain information regarding countries. One excel files is one year. Within each file, you have a number of columns which has particular types of information. The idea is that we want to be able to ultimately have one large excel file (which will be our dataset) containing all of the years within it (which is why I'm trying to append all the individual excel files together).

In theory, the excel files should all fit together since they all have the same types of columns/information. (Ex: Every file may have country, year, president, capital city, salary, etc. on top of the excel file). What makes it confusing for me is that column AF doesn't contain anything (unless someone put some information on there on some files that I didn't know about).

I tried to append all the excel files at once in the beginning and got the "too many variables" message so I separated the 37 files into 4 separate folders. My plan was to append the files within the 4 folders together, resulting in 4 files, which I would then append together in order to create that one large excel file.

With all of your help, I was able to get to the very last step, until that problem with the variable AF arose. That is currently where I'm at.

Again, apologies for any confusion. I hope this clears things up.

Many thanks,

Carter

[daniel klein]

In theory, the excel files should all fit together since they all have the same types of columns/information. (Ex: Every file may have country, year, president, capital city, salary, etc. on top of the excel file).

Why should there be a year column/variable in the files, if each file contains information on one year? Also note that not only must the values/contents of the columns be matching, but also the names of these columns.

What makes it confusing for me is that column AF doesn't contain anything (unless someone put some information on there on some files that I didn't know about).

Sorry, I do not think anybody on the list can help here. We do not see the Excel files, you need to figure out what is wrong here. There is no problem with the code you are using (and that we can see).

I tried to append all the excel files at once in the beginning and got the "too many variables" message

If you want more detailed comments on this, you need to show the code you used (cf. FAQ #12).

so I separated the 37 files into 4 separate folders. My plan was to append the files within the 4 folders together, resulting in 4 files, which I would then append together in order to create that one large excel file.

As explained above, this will not work. The resulting file will be as large as the one you would create when appending all the files at once.

Best
Daniel

[laura wolff]

Dear all,

I am trying to use xls2dta to combine multiple xlsx files (1 sheet each) into dta format. The code I am using is:

xls2dta , save(C:\Users\User\Desktop\data) : import excel C:\Users\User\Desktop\data
xls2dta , save( C:\Users\User\Desktop\data\complete.dta) : append

However, when I do this for an xlsx file, I get the message:

file C:/Users/User/Desktop/data/file1.xlsx could not be loaded

where the file specified is always the second file in the folder.

When I convert each file to an xls format beforehand, there's no problem and xls2dta works fine. I want to avoid having to go through vba to reformat all my files though, so I would really appreciate if someone could tell me where my mistake is.

Thanks!

[Clyde Schechter]

I don't use -xls2dta- myself, so I can't directly answer your question, but this task can be accomplished entirely with official Stata commands. See Carole Wilson's #5 in this thread for code. While that example specifically goes for *.xls, if you just replace that by *.xls* you'll get both .xls and .xlsx files picked up.

As noted in #2, and then clearly exemplified in the rest of this entire thread, Excel files can be pretty anarchic in the way they hold data. Generally speaking the code for finding and appending files is the least of the task: the cleaning within each file so as to make them compatible for the -append- process is the hard part. Perhaps you will be lucky and have an ensemble of clean, consistent files that snap together nicely, but that is the exception not the rule. Be prepared to spend time studying each file separately and fixing the inconsistencies among them.

[daniel klein]

When I convert each file to an xls format beforehand, there's no problem and xls2dta works fine. I want to avoid having to go through vba to reformat all my files though, so I would really appreciate if someone could tell me where my mistake is.

Probably no mistake on your side. Try to import excel the problematic files. If this works, report back here (or to me using the mail address in the help file) because then it is some bug in xls2dta. I suspect, however, that you will receive the exact same error message in which case you may also report back here, but might want to take this to tech support.

Best
Daniel

[laura wolff]

thank you for your help!

Daniel, you are right - I cannot import any of problematic files using import excel. This is strange, since they have the exact same format as the first file, which I can import.
I will contact tech support!

Thank you