Import and merge multiple csv files

[Sophie Yang]

Hi all,

I have many csv files that have one common variable (date) and one other variable. I would like to import them all to dta files and then merge them horizontally using the date variable.

I've used the following to try and create individual files, but for some reason some of the files are read as 1 var, and some as 2 var. The csv files are all from google trends and should be the exact same (except for different keyword searches).

local myfilelist : dir . files "*.csv"
foreach file of local myfilelist {
drop _all
insheet using "`file'"
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}

How can I force the two columns in the csv file to be read as 2 separate variables?

Additionally, is there any way to merge all of them in one go according to date? Thanks in advance for any insight!

[Clyde Schechter]

Unless you are using an old Stata, -insheet- has been superceded by -import delimited-. Try using that instead of -insheet-.

That said, there is nothing obviously wrong with your code. If switching to -import delimited- does not solve the problem, then it is likely that the trouble is with the data itself. Showing examples of the data from one of the single variable files and one of the two variable files would be necessary for further troubleshooting.

[Sophie Yang]

Many thanks, Clyde! It works perfectly using -import delim-.

Regarding merging, is there a way to loop over each dataset to build a master one? From my limited knowledge I wasn't sure what the using dataset should be in this loop.

local myfilelist : dir . files "*.dta"
use master

foreach f of local myfilelist {
merge 1:1 date using f
drop _merge
save master2, replace
}

[Clyde Schechter]

It should be

merge 1:1 date using `f'

You can think of a -foreach- command as defining a local macro whose values will change each time through the loop. When you want to make use of that value, you have to dereference that macro just as you would any other. So to get at the current value of f, you need to say `f'.

[Sophie Yang]

Much appreciated!

[Lars Pete]

The loop mentioned above for merging multiple files doesn't work. What could be going wrong?

[Clyde Schechter]

Lots of things could be going wrong. And saying something "doesn't work" provides no information at all. The first issue is, did you notice the correction in #5 and apply it? If not, then you are either in a different situation or have modified the code in some way that causes it to break.

Please post back showing the exact commands you are using and show the results you are getting from Stata, including any error or warning messages along the way. Make sure you do not edit the code or output at all, as there are no unimportant details in coding. To assure you do it accurately, copy from the Results window or your log file to the clipboard, and then paste here into the Forum editor, surrounded by code delimiters.

Added: I responded to this before seeing the post at https://www.statalist.org/forums/for...iple-dta-files, where Lars Pete asks what appears to be the same question, but provides some information that sheds a little light on the problem. He has received a response there, from Fei Wang, which is likely correct.