Importing specific cells from multiple excell sheets in multiple workbooks

[Isak Kornerup Houe]

Hello everyone

I have a problem I hope I can get your help with. I'm a first time poster, so please be kind.

I need to import data from multiple excel workbooks each containing three sheets with data arranged in 1-2 columns per sheet. Each workbook has a filename that has the interviewee name as the last part. The workbooks can be put in a common folder.

The data is organized vertically, so that the variable names are in the A column and the corresponding value might be in the C or D column.

I need a way to enter this into stata so that stata will create a datafile with the last part of the file name as a respondent ID, then find, say, a varname in A3 in sheet and add the value from C3 to the datafile.

In all three sheets variable names can be found in column A, in sheet 1-2 variable values will be in column D and E. In sheet 3 variable values will be in I. Ranges vary, but I figure I can clean up any generated variables that doesn't contain data. In the two sheets where there are two columns containing data I will want two sets of variables.

So my dta should look like:

	Variable name from Sheet 1 A1	Variable name from sheet 1 A2	Etc. continuing through sheet 2 and 3
Respondent ID from filename of first xls file	Variable value from sheet 1 C1 for first xls file	Variable value from sheet 1 C2 for first xls file
Respondent ID from filename of second xls file	Variable value from sheet 1 C2 for second xls file	Variable value from sheet 1 C2 for second xls file
etc

The data should be organized so that the same value is always the in the same cell, so say C3 will always be the value of the same value. But there could be revisions to a few of the sheets and I would like to be able to revise them in the future, so ideally I would want stata to always find the variable name in the A column and then append the data.

I believe this can be done with xls2dta, but I would benefit tremendously from your input on how to code it.

Thanks a bunch in advance!

Isak

P.s. I added an example of the excel file. It is all in Danish but I am thinking it might still be useful to understand how it's structured. I've marked in English where the data and varnames is for explanation. I hope this all makes sense.

[Phil Bromiley]

You didn't get a quick answer. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output (fixed spacing fonts helps), and sample data using dataex. Many on this list will not open files due to concerns about viruses.

I don't fully understand the structure of the data or exactly what you're trying to do. My inclination is generally to read into Stata and then work from there. Note that there is a excel import facility in Stata itself - look under File, Import. It may not have the functionality of xls2dta but is interactive.

By the way, in Stata, we talk about variables and observations, not rows, columns and cells. Excel is rows, columns, and cells, but Stata is variables and observations.