Import several excel files and transpose column into row

[Jaeyong Yoo]

I need to import several Excel files. Each Excel file has three variables, item, price and quantity.

Here are some examples.
.
food.xlsx

A	B	C
Food	Price	10
Food	Quantity	100

.
gas.xlsx

A	B	C
Gas	Price	3
Gas	Quantity	50

.
I want to import several Excel files and make one Stata dataset like this:

Item	Price	Quantity
Food	10	100
Gas	3	50
Water	7	25
...	...	...

.

Price and Quantity are located in one column in Excel, but I want to transpose the column into the row in Stata for several Excel files.

Please help me solve this. Thanks!

[William Lisowski]

The example below assumes you have imported each Excel worksheet into separate Stata datasets using the import excel command followed by the save command. If you have large quantities of Excel worksheets, the community contributed xls2dta command available from SSC can make this easier, and can do the append as part of the process. See ssc describe xls2dta for more information on this command, and help import excel for more information on the import excel command.

. clear

. append using "`food'" "`gas'"

. list, clean

          a          b     c  
  1.   Food      Price    10  
  2.   Food   Quantity   100  
  3.    Gas      Price     3  
  4.    Gas   Quantity    50  

. reshape wide c, i(a) j(b) string
(note: j = Price Quantity)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                        4   ->       2
Number of variables                   3   ->       3
j variable (2 values)                 b   ->   (dropped)
xij variables:
                                      c   ->   cPrice cQuantity
-----------------------------------------------------------------------------

. rename a Item

. rename (c*) (*)

. list, clean

       Item   Price   Quantity  
  1.   Food      10        100  
  2.    Gas       3         50

[Jaeyong Yoo]

Thank you, William. I will look at xls2dta.