Appending many CSV and xls files

[Nick Baradar]

Hello All,

I am trying to append 86 xls files. Each csv file is named by numbers, e.g., 1.xls, 2xls, 3.xls, etc.. All files have the same variable lists.

I use the following code to append the files:

            import excel "$Announcements\1.xls", sheet("Screening") cellrange(A8:O10008) firstrow clear
            gen file=1
            save "$Final_data\Announcements_merged", replace
            forval i=2/86{
                      import excel "$Announcements/`i'.xls", sheet("Screening") cellrange(A8:O10008) firstrow clear
                      append using "$Final_data\Announcements_merged"
                      replace file=`i' if missing(file)
                      save "$Final_data\Announcements_merged", replace
            }

            use "$Final_data\Announcements_merged", clear

            sort file

However, I have noticed that my code generates double observations and I really don't understand why..


To append another set of files (csv files), I have used a different code as the names of the files were very different, and again, the code generated double observations.

tempfile temp1 temp2 temp3 temp4 temp5 temp6 temp7 temp8 temp9

            import delimited "$SSL\airline.csv", clear
            gen domain = "airline"
            save "`temp1'", replace
            
            import delimited "$SSL\auto.csv", clear
            gen domain = "auto"
            save "`temp2'", replace
            append using "`temp1'"  "`temp2'", force
            save "`temp1'", replace
            
            import delimited "$SSL\beverages.csv", clear
            gen domain = "beverages"
            save "`temp2'", replace
            append using "`temp1'"  "`temp2'", force
            save "`temp1'", replace

             save "$Final_data/SSL_merged", replace

I would be very grateful if you could help me understand where the issue is.

[Daniel Schaefer]

Hi Nick, I'm having trouble reproducing your issue. I took your code from the first code block in #1 and ran it against three .xlsx files. I use different paths, I don't use globals in my path definitions, I change the number of iterations of the loop to range from 2 to 3, and I don't use the sheet and cellrange options on the -import excel- commands. Otherwise the logic is the same. I wouldn't expect any of these changes to explain why you are seeing double observations, and yet I get exactly the right expected number of observations on my end.

Is it possible either the doubling is happening because of some code outside of the first block in #1?

[Nick Baradar]

Hi Daniel, thank you so much for your reply. I experimented a bit with the second code, and it turns out the issue was related to how I was saving each newly imported dataset as a temp file and how I executed the append function. Essentially, I didn't need to save a new temp file every time I imported a CSV file. When appending, I just needed to reference the most recently saved temp file. I have to admit, the original code was generated by ChatGPT, and it caused me quite a bit of headache this week!

 tempfile temp1 temp2 temp3 temp4 temp5 temp6 temp7 temp8 temp9           
import delimited "$SSL\airline.csv", clear            
gen domain = "airline"           
save "`temp1'", replace                          

import delimited "$SSL\auto.csv", clear            
gen domain = "auto"            
append using "`temp1'", force            
save "`temp2'", replace                          

import delimited "$SSL\beverages.csv", clear            
gen domain = "beverages"            
append using "`temp2'", force            
save "`temp3'", replace              

save "$Final_data/SSL_merged", replace