Question about using moving files to specific folder with the use of locals

[Andrew Xavier]

Dear all,

I cannot work out the following problem:

I have an excel spreadsheet called datax with two variables: refid and attachments
-refid is a number that is coded in terms of string, for example "123429058"
-attachments is a string variable. For some refids, attachments can be blank or have a value. An example of value for attachments could be "1-s2.0-S0959804914009988-main.pdf" or "1-s2.0-S0959804914009988-mmc1.doc" or "1-s2.0-S0959804914009988-main.pdf1-s2.0-S0959804914009988-mmc1.doc"

I run the following code that creates a folder for each refid

cd "C:\Users\user\Downloads"

import excel "datax.xlsx", sheet("DistillerSR") firstrow case(lower) clear

levelsof refid, local(id)

foreach x in `id' {
mkdir `x'
}

I have in the directory "C:\Users\user\Downloads" several pdf files, doc files, zip file and image files.

I want to move them from that directory to the specific refid folder I have generated previously.The excel spreadsheet indicates which refid folder I I should move the file. For some refids there is no files to move, for some it is just one file to move and for some there are multiple files to move.

I have tried the following code but it does not work

levelsof refid, local(id)
foreach x in `id' {
levelsof attachments if refid=="`x'", local(p)
copy "`p'" "C:\Users\joaol\Downloads\\`x'\\`p'", replace
}

Please can you help me? it should be a simple thing to do but I cannot work out the problem?


Thank you

Andre

[Nick Cox]

Just guessing but

copy "`p'" "C:\Users\joaol\Downloads\\`x'\\`p'", replace
}

will certainly fail if p contains two or more filenames.

[Daniel Shin]

I'm guessing something like this might work. Notice I'm using the windows shell command move and not the Stata copy command which I believe will copy and not move the file.

levelsof refid, local(id)
foreach x in `id' {
levelsof attachments if refid=="`x'", local(p)
foreach y in `p' {
! move "`y'" "C:\Users\joaol\Downloads\\`x'"
}
}

[Andrew Xavier]

Thank you but it does not work even for the case for one filename

For example the following code does not work even for situations with one filename

levelsof refid, local(id)
foreach x of local id {
levelsof attachments if refid=="`x'", local(p)
foreach y of local p {
local old="C:\Users\joaol\Downloads\\`y'"
local new="C:\Users\joaol\Downloads\\`x'\\`y'"
display "`old'"
display "`new'"
copy `old' `new', replace
}
}

The error is

"file C:\Users\joaol\Downloads\1471-2407-10-26.pdf

not found
{p_end}"

[Daniel Shin]

Perhaps your attachments field contains non-existent files. Use confirm to check:

levelsof refid, local(id)
foreach x in `id' {
levelsof attachments if refid=="`x'", local(p)
foreach y in `p' {
capture confirm file `y'
if !_rc {
! move "`y'" "C:\Users\joaol\Downloads\\`x'"
}
else {
di "`y' not found"
}
}
}

[Andrew Xavier]

I'm guessing something like this might work. Notice I'm using the windows shell command move and not the Stata copy command which I believe will copy and not move the file.

levelsof refid, local(id)
foreach x in `id' {
levelsof attachments if refid=="`x'", local(p)
foreach y in `p' {
! move "`y'" "C:\Users\joaol\Downloads\\`x'"
}
}

Sorry I tried your code and it did not work

It did not move or copy the content files into the any of the refid folders

Thanks

Andre

[Daniel Shin]

Andrew, you can try my latest code with your locals; I may have placed an extra \ somewhere.

[Andrew Xavier]

Perhaps your attachments field contains non-existent files. Use confirm to check:

levelsof refid, local(id)
foreach x in `id' {
levelsof attachments if refid=="`x'", local(p)
foreach y in `p' {
capture confirm file `y'
if !_rc {
! move "`y'" "C:\Users\joaol\Downloads\\`x'"
}
else {
di "`y' not found"
}
}
}

Thank you. I have tried your code and it did not work

it did not move any of the files to the respective folder. All the files came up as "not found"

[Daniel Shin]

Let's try it again. Please confirm the files that you want to move are in the proper folder, and see if the following works.

levelsof refid, local(id)
foreach x of local id {
levelsof attachments if refid=="`x'", local(p)
foreach y of local p {
local old="C:\Users\joaol\Downloads\\`y'"
local new="C:\Users\joaol\Downloads\\`x'\\`y'"
capture confirm file "`old'"
if !_rc { // file is present
copy "`old'" "`new'", replace
}
else { // file is not present
di "`old' not found"
}
}
}

[Andrew Xavier]

Let's try it again. Please confirm the files that you want to move are in the proper folder, and see if the following works.

levelsof refid, local(id)
foreach x of local id {
levelsof attachments if refid=="`x'", local(p)
foreach y of local p {
local old="C:\Users\joaol\Downloads\\`y'"
local new="C:\Users\joaol\Downloads\\`x'\\`y'"
capture confirm file "`old'"
if !_rc { // file is present
copy "`old'" "`new'", replace
}
else { // file is not present
di "`old' not found"
}
}
}

Thank you. I have tried the code and I did confirm whether the files are in the proper folder. The code does not work

There is something strange with copy. copy does not seem to recognize the name of the files that are locals,

The code does not return any error. it just returns each file as not found. Example

C:\Users\joaol\Downloads\cncr.32639.pdf
not found

I wonder whether this could explain the problem:

In the output window this is what is presented: C:\Users\joaol\Downloads\cncr.32639.pdf not found

When I click to copy it, it becomes like this: C:\Users\joaol\Downloads\cncr.32639.pdf not found

(an extra space between "pdf" and "not found")

When I copy it and paste becomes like this
"C:\Users\joaol\Downloads\cncr.32639.pdf
not found"

Maybe there is a strange character somewhere here and that's why it does not work?

Thanks

[Daniel Shin]

Can you run this and tell us what you get?

confirm file C:\Users\joaol\Downloads\cncr.32639.pdf
confirm file "C:\Users\joaol\Downloads\cncr.32639.pdf"

[Andrew Xavier]

Can you run this and tell us what you get?

confirm file C:\Users\joaol\Downloads\cncr.32639.pdf
confirm file "C:\Users\joaol\Downloads\cncr.32639.pdf"

I do not get anything, no message, for each of the two codes

The file exists because if I copy C:\Users\joaol\Downloads\cncr.32639.pdf into internet explorer, the file open

[Daniel Shin]

If there are no error messages, that means Stata confirms the existence of the files. That indicates the capture confirm file is finding an error, likely due to the macro containing extra as you've suspected. Perhaps use Nick's charlist (ssc inst charlist) to identify the offending characters? Afterwards replace the file name macro with subinstr and char(#)?

[Daniel Shin]

Another suggestion is to generate a new attachments variable first then use the new variable to source the files. I'm not well versed in regular expressions but hopefully it will work:

gen files = lower(strtrim(regexs(0))) if regexm(attachments, "[a-zA-Z0-9.]+")
levelsof files...

[Andrew Xavier]

Another suggestion is to generate a new attachments variable first then use the new variable to source the files. I'm not well versed in regular expressions but hopefully it will work:

gen files = lower(strtrim(regexs(0))) if regexm(attachments, "[a-zA-Z0-9.]+")
levelsof files...

Thank you for your message.

This is ridiculous and I can't believe I spent so long trying to work out this simple issue!

I thought that there was a problem with copy command in stata but the copy command works correctly.

The problem was that my excel file which was not created by me had feed control characters. The code worked just fine when I did

replace attachments = subinstr( attachments , char(10), "", .)

Thank you a lot for your help.

Andre