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.