Appending multiple files xlsx

[Meshal AlQhtani]

Dear Stata users,

I really appreciate your assistance every time I ask here.

my aim: to append ~100 excel files in one excel file where I want to use an appendix file to appned the remining files starting from row3 (where the valus start)

data: they have the same filename excpet for last serial no for example ( NCEC_AQMS_valid_data_l1_OFAQ_166)

my data scenario: I have more than 100 Excel files with more than 1000 rows


my dataex :

* Example generated by -dataex-. For more info, type help dataex
clear
input int n double date str8(ID Station) double(latitude longitude PM25 PM10 CO) byte CO2 double(NO NO2 O3 SO2) byte(H2S HCNM NH3 HCT CH4 Benzene)
 .                 . ""         ""                         .                 .    . .     . .    .    .    .    . . . . . . .
 .                 . ""         ""                         .                 .    . .     . .    .    .    .    . . . . . . .
 0       2.04336e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111    . .     . .    .    .    .    . . . . . . .
 1 2043363599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 24.4 . 1.799 . 11.3 51.6 16.3  7.4 . . . . . .
 2 2043367200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 29.5 . 1.504 .  5.7 37.5 27.4  7.2 . . . . . .
 3     2.0433708e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.5 . 1.572 .  8.7 35.3 22.4  7.7 . . . . . .
 4 2043374399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.7 . 1.621 .  4.8 45.1  9.6  7.2 . . . . . .
 5 2043378000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.8 .  1.48 .  1.9 30.2 21.9  6.9 . . . . . .
 6     2.0433816e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 27.9 . 1.567 .  8.8 33.8 15.7  7.4 . . . . . .
 7 2043385199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 23.1 . 2.095 .   56 56.9  4.1  8.4 . . . . . .
 8 2043388800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 37.1 .   1.7 . 21.5 39.5 24.7  8.5 . . . . . .
 9     2.0433924e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 47.9 . 1.503 .  7.4 22.6 50.7  9.8 . . . . . .
10 2043395999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 27.8 . 1.352 .  2.6  8.8 62.1 11.2 . . . . . .
11 2043399600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   15 . 1.328 .  1.9  5.6 65.4 12.6 . . . . . .
12     2.0434032e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.1 . 1.347 .  3.2  6.5 66.3    . . . . . . .
13 2043406799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.7 . 1.414 .  5.8 11.4 69.3 13.6 . . . . . .
14 2043410400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 18.6 . 1.336 .    2    7   83 14.7 . . . . . .
15      2.043414e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.5 . 1.344 .  1.7  6.5 86.4   15 . . . . . .
16 2043417599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.8 . 1.362 .  2.5  9.2 82.4    . . . . . . .
17 2043421200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.9 . 1.376 .  2.1 11.4 78.3 16.9 . . . . . .
18     2.0434248e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.7 . 1.431 .  1.8   18 67.3 17.3 . . . . . .
19 2043428399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   13 . 1.538 .    . 30.7 52.2 14.6 . . . . . .
20 2043432000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 27.1 . 1.633 .  2.1 40.6 42.9 12.9 . . . . . .
21     2.0434356e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.4 . 1.742 .  6.4   59 22.7 11.2 . . . . . .
22 2043439199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.2 . 1.777 .  7.6 69.8 12.2  9.7 . . . . . .
23 2043442800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 27.9 . 1.695 .  8.3 56.7 24.1  8.4 . . . . . .
24     2.0434464e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 21.7 . 1.498 .    3 39.9 38.7  8.2 . . . . . .
25 2043449999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.7 . 1.283 .  1.7 16.4 60.7  7.4 . . . . . .
26 2043453600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 17.8 . 1.351 .  2.9 19.8 49.3  8.1 . . . . . .
27     2.0434572e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.9 . 1.464 .  2.3 43.4 23.3  7.8 . . . . . .
28 2043460799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.2 . 1.512 .    . 35.4 28.9  7.2 . . . . . .
29 2043464400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 17.7 . 1.648 .    3 43.6 13.6  6.7 . . . . . .
30      2.043468e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 25.3 . 1.814 . 18.1   57  8.9  5.7 . . . . . .
31 2043471599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 28.3 .  2.32 .   97 59.8  2.3  6.7 . . . . . .
32 2043475200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   53 . 2.031 . 45.1 48.7 24.4  6.5 . . . . . .
33     2.0434788e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 71.3 . 1.457 .    7 23.4 55.5    8 . . . . . .
34 2043482399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 12.2 . 1.329 .    3 10.9 71.2  9.6 . . . . . .
35 2043486000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   10 . 1.307 .  2.5  7.4 77.9 10.5 . . . . . .
36     2.0434896e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 11.1 . 1.333 .  3.9 10.5 82.9 10.9 . . . . . .
37 2043493199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111  8.2 . 1.397 .  7.3   16   83 11.2 . . . . . .
38 2043496800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111  8.5 . 1.312 .  2.3  8.2 87.8   12 . . . . . .
39     2.0435004e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 17.6 . 1.303 .    2  7.1 85.1 12.7 . . . . . .
40 2043503999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 35.8 . 1.322 .    .  8.7   81    . . . . . . .
41 2043507600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.8 . 1.418 .  2.3 10.1   78 12.6 . . . . . .
42     2.0435112e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   18 .  1.41 .  2.5 18.5 68.1 12.3 . . . . . .
43 2043514799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 30.5 . 1.483 .  2.4 26.2   55 11.6 . . . . . .
44 2043518400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   26 . 1.574 .  3.1 41.3   38 10.8 . . . . . .
45      2.043522e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 22.8 . 1.689 .  5.5 59.6 20.2  9.2 . . . . . .
46 2043525599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 18.5 . 1.745 .    8 64.5 16.6    8 . . . . . .
47 2043529200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.7 . 1.632 .  5.2 59.5   18  6.7 . . . . . .
48     2.0435328e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 26.4 . 1.583 .  4.1 48.5 25.7  6.4 . . . . . .
49 2043536399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 21.7 . 1.591 .  6.7 53.4 18.8    . . . . . . .
50 2043540000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.2 .  1.73 . 13.4 53.2 15.3  6.5 . . . . . .
51     2.0435436e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   27 . 1.847 . 19.1 61.1  1.2  5.2 . . . . . .
52 2043547199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 21.3 . 1.802 .  9.6 57.5  2.1  3.9 . . . . . .
53 2043550800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.6 . 1.672 .  4.7   51    3  3.2 . . . . . .
54     2.0435544e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 24.7 . 1.788 . 12.8 50.4    2  3.3 . . . . . .
55 2043557999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   21 . 2.418 . 90.1 62.3  2.3  4.5 . . . . . .
56 2043561600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 29.3 . 1.944 . 34.3 49.4 27.2  5.2 . . . . . .
57     2.0435652e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.7 . 1.422 .  6.6 24.2 62.7  6.5 . . . . . .
58 2043568799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 26.2 . 1.336 .  2.6 10.8 76.1  9.2 . . . . . .
59 2043572400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 32.4 . 1.311 .  2.3  8.7 80.1 10.9 . . . . . .
60      2.043576e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.5 . 1.328 .  2.9 10.8 86.1 11.8 . . . . . .
61 2043579599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 11.3 . 1.391 .  6.7 15.5 82.1 12.9 . . . . . .
62 2043583200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 25.4 .  1.32 .  2.5  8.3 78.8 13.9 . . . . . .
63     2.0435868e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.4 . 1.331 .  2.4  7.3 77.5 14.1 . . . . . .
64 2043590399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   14 . 1.333 .    .  9.4 74.6 14.6 . . . . . .
65 2043594000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.7 . 1.371 .    3 12.2 71.3 14.4 . . . . . .
66     2.0435976e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 26.7 . 1.376 .  2.4 13.5 65.6   14 . . . . . .
67 2043601199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 17.8 . 1.404 .  2.7   20 57.9 13.3 . . . . . .
68 2043604800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 28.3 . 1.435 .  2.2 20.4 55.2 12.9 . . . . . .
69     2.0436084e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.3 . 1.538 .    . 34.1 39.2 12.6 . . . . . .
70 2043611999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.4 . 1.608 .  2.8   39 29.9 11.6 . . . . . .
71 2043615600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 26.4 . 1.532 .  4.3 42.1 23.6 13.9 . . . . . .
72     2.0436192e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   29 . 1.592 .  5.9 43.9 18.7 12.9 . . . . . .
73 2043622799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.4 . 1.587 .  5.7 50.3 11.5  7.8 . . . . . .
74 2043626400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 25.3 . 1.803 . 13.7   54  7.7  6.7 . . . . . .
75       2.04363e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 22.5 . 1.814 . 16.5   53  4.1  5.6 . . . . . .
76 2043633599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.7 . 2.126 . 38.1 51.2    .  4.9 . . . . . .
77 2043637200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 10.8 .  1.88 . 21.7   53   .5  3.1 . . . . . .
78     2.0436408e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 19.1 . 1.742 . 16.8   50    1  2.1 . . . . . .
79 2043644399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 13.5 . 1.704 .   23 47.5  5.5  2.3 . . . . . .
80 2043648000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 29.3 . 1.514 . 10.3   29 30.4  3.8 . . . . . .
81     2.0436516e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 22.9 . 1.289 .  2.8 10.1 59.4  5.7 . . . . . .
82 2043655199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.9 . 1.292 .  2.2  7.6   67  8.1 . . . . . .
83 2043658800000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   14 . 1.296 .  2.6  9.8 74.9 10.3 . . . . . .
84     2.0436624e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.1 . 1.291 .  2.4  9.3   82 12.3 . . . . . .
85 2043665999999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 17.9 . 1.301 .  2.3  8.2 79.3 13.9 . . . . . .
86 2043669600000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.1 . 1.303 .  2.2  6.8 75.7 14.3 . . . . . .
87     2.0436732e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 12.6 . 1.311 .  2.3  7.5 74.2 14.2 . . . . . .
88 2043676799999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   13 . 1.317 .  2.2  7.1 72.6 14.6 . . . . . .
89 2043680400000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 10.7 . 1.348 .    .  9.8 71.7 13.9 . . . . . .
90      2.043684e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 10.2 . 1.344 .  2.4 11.9   75 13.3 . . . . . .
91 2043687599999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 20.4 . 1.378 .    2 14.4 69.4 12.3 . . . . . .
92 2043691200000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 16.4 . 1.466 .  2.3 28.4 52.3 12.1 . . . . . .
93     2.0436948e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.2 . 1.607 .  2.2 36.1 39.3 11.6 . . . . . .
94 2043698399999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111   12 . 1.586 .  4.5 49.6 26.6 10.2 . . . . . .
95 2043702000000.001 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 14.5 . 1.696 .  6.7 64.9 11.1  8.8 . . . . . .
96     2.0437056e+12 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 15.8 .   1.7 .  6.3 59.7   15  7.4 . . . . . .
97 2043709199999.999 "AQMS_141" "AQMS_141" 17.48411111111111 44.12286111111111 12.1 . 2.043 . 25.7 66.2    4  6.8 . . . . . .
end
format %tc date

------------------ copy up to and including the previous line ------------------

Listed 100 out of 2211 observations
Use the count() option to list more

.









Many thanks
Meshal

[Devra Golbe]

This thread on the forum should get you started. Additionally, it points to this thread about xls2dta from ssc.

[George Ford]

In Stata, loop through the files, save as dta, and then append them all.

[Clyde Schechter]

clear*

tempfile building
save `building', emptyok
local filenames: dir "." files "NCEC_AQMS_valid_data_I1_OFAQ_*.xlsx"

foreach f of local filenames {
    import excel using `"`f'"', clear firstrow
    drop in 1/2
    gen source = `"`f'"'
    append using `building'
    save `"`building'"', replace
}

save combined_spreadsheet_data, replace

Note: the -local filenames:...- command assumes that all of these spreadsheets are located in the current working directory. If not, replace "." with the directory path name (in quotes). It also assumes that the files in question are of type .xlsx. If they are .xls, change accordingly.

That said, you may come to regret doing this. Even with spreadsheets that come from a source with a deserved reputation for high quality data production, with over 100 such files there is a substantial probability that there will be incompatibilities among the files. If, for example, some variable is a string variable in some of the spreadsheets, but is numeric in others, the -append- command will halt with an error message. While you can suppress the halting behavior with a force option, it doesn't solve the problem: it just means that the data in the file being appended will be lost to preserve the storage type of the earlier files. There may also be other difficulties such as variations in the spelling or capitalization of variable names. It is very difficult, or even impossible, to try to fix these problems in the combined file that results.

I think a better approach to problems like this is to use the loop to import each of the spreadsheets to a separate Stata .dta file. Then use Mark Chatfield's -precombine- command, available from SSC, to check for incompatibilities among them. With that information in hand you can then decide on how to resolve discrepancies and fix the individual files that present problems. Then you can append all of the fixed files.

Added: Crossed with #2 and #3.

[George Ford]

Clever. I hadn't thought of importing then appending like that. I normally import/save all excel files then append them all.

I could see where either approach would be useful.

[Meshal AlQhtani]

Many thanks !!!!


it was beneficial