import excel and one line break into two lines

[Chen Samulsion]

Dear Stata users,

I often need to import Excel data to Stata. And as is often the case, there're observations value that occupy two lines. When I import those observations into Stata, they will be broken into two parts. Hope I describe my question clear. I will give an excel picture and the data I get in Stata.

import excel "C:\Users\XXX\Desktop\example.xls", sheet("Sheet1")

* Example generated by -dataex-. To install: ssc install dataex
clear
input str57 A double B
"Manufacture"                                               34.887170455619476
"    Processing of Farm and Sideline Food "                 26.688570975313773
"    Manufacture of Food"                                   13.664629448768595
"    Manufacture of Wine, Drinks and Refined Tea"           2.8556867338990766
"    Tobacco Products"                                       86.89332691673287
"    Textile Industry"                                      35.085489598796485
"    Manufacture of Textile Wearing Apparel and Finery"      7.110509182708402
"    Manufacture of Leather, Fur, Feather & Its Products  "  32.33679490254483
"       and Footwear"                                                        .
"    Timber Processing, Bamboo, Cane, "                      87.59910108981258
"    Palm Fiber & Straw Products"                                            .
"    Manufacture of Furniture "                              89.27586903045194
"    Papermaking and Paper Products"                          58.4465801955765
"    Printing, Reproduction of Recording Media"             36.977914320526295
"    Manufacture of Culture, Education,Arts and crafts,"     85.06309088488794
"       Sport and Entertainment Goods"                                       .
"    Processing of Oil, Coal and Other Fuel "                11.96613165706274
"    Manufacture of Raw Chemical Materials"                  75.42434161840235
"    Manufacture of Medicines"                               69.50233461854295
"    Manufacture of Chemical Fibers"                         68.66152174680667
end

[Joseph Coveney]

The dataset is misshapen in the Excel workbook and so you cannot do anything during the import. You'll have to clean things up afterward with something along the following lines.

replace A = stritrim(A + A[_n+1]) if mi(B[_n+1])
drop if mi(B)

[Chen Samulsion]

Thank you very much Joseph Coveney It's a nice solution.