Searching and listing folders using as source a Excel
up vote
0
down vote
favorite
As a junior system admin I was tasked with a data management chore on our NAS device.
By using an Excel workbook as the source (the cells of a specific column of this workbook), I have to use Windows File Manager to search our NAS location for the folders that they have the exact same name as the cells of that Excel column.
Is a way to search a disk location by using names in Excel cells to match the folders with these names from the cells columns?
I attach a image from the column and the cells so you can have a better idea.
microsoft-excel worksheet-function nas
edited Nov 13 at 14:03
Blackwood
2,66561727
asked Nov 13 at 13:30
Nick87
62
add a comment |
up vote
0
down vote
favorite
As a junior system admin I was tasked with a data management chore on our NAS device.
By using an Excel workbook as the source (the cells of a specific column of this workbook), I have to use Windows File Manager to search our NAS location for the folders that they have the exact same name as the cells of that Excel column.
Is a way to search a disk location by using names in Excel cells to match the folders with these names from the cells columns?
I attach a image from the column and the cells so you can have a better idea.
microsoft-excel worksheet-function nas
edited Nov 13 at 14:03
Blackwood
2,66561727
asked Nov 13 at 13:30
Nick87
62
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
You'd probably need to look atGet-ChildItemand maybeWhere-Object. One could give you a "tree" (not to be confused with the cmdtree) and should be able to filter it for you. If it's not able to filter sufficiently look atWhere-Object. Something along the lines ofgci -Recurse | where Name -like '*1999*'could be the result.
– Seth
Nov 13 at 13:53
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
As a junior system admin I was tasked with a data management chore on our NAS device.
By using an Excel workbook as the source (the cells of a specific column of this workbook), I have to use Windows File Manager to search our NAS location for the folders that they have the exact same name as the cells of that Excel column.
Is a way to search a disk location by using names in Excel cells to match the folders with these names from the cells columns?
I attach a image from the column and the cells so you can have a better idea.
microsoft-excel worksheet-function nas
edited Nov 13 at 14:03
Blackwood
2,66561727
asked Nov 13 at 13:30
Nick87
62
As a junior system admin I was tasked with a data management chore on our NAS device.
By using an Excel workbook as the source (the cells of a specific column of this workbook), I have to use Windows File Manager to search our NAS location for the folders that they have the exact same name as the cells of that Excel column.
Is a way to search a disk location by using names in Excel cells to match the folders with these names from the cells columns?
I attach a image from the column and the cells so you can have a better idea.
microsoft-excel worksheet-function nas
microsoft-excel worksheet-function nas
edited Nov 13 at 14:03
Blackwood
2,66561727
asked Nov 13 at 13:30
Nick87
62
edited Nov 13 at 14:03
Blackwood
2,66561727
asked Nov 13 at 13:30
Nick87
62
edited Nov 13 at 14:03
Blackwood
2,66561727
edited Nov 13 at 14:03
Blackwood
2,66561727
edited Nov 13 at 14:03
Blackwood
2,66561727
2,66561727
asked Nov 13 at 13:30
Nick87
62
asked Nov 13 at 13:30
Nick87
62
asked Nov 13 at 13:30
Nick87
62
62
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
You'd probably need to look atGet-ChildItemand maybeWhere-Object. One could give you a "tree" (not to be confused with the cmdtree) and should be able to filter it for you. If it's not able to filter sufficiently look atWhere-Object. Something along the lines ofgci -Recurse | where Name -like '*1999*'could be the result.
– Seth
Nov 13 at 13:53
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59
add a comment |
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
You'd probably need to look atGet-ChildItemand maybeWhere-Object. One could give you a "tree" (not to be confused with the cmdtree) and should be able to filter it for you. If it's not able to filter sufficiently look atWhere-Object. Something along the lines ofgci -Recurse | where Name -like '*1999*'could be the result.
– Seth
Nov 13 at 13:53
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
You'd probably need to look at
Get-ChildItem and maybe Where-Object. One could give you a "tree" (not to be confused with the cmd tree) and should be able to filter it for you. If it's not able to filter sufficiently look at Where-Object. Something along the lines of gci -Recurse | where Name -like '*1999*' could be the result.– Seth
Nov 13 at 13:53
You'd probably need to look at
Get-ChildItem and maybe Where-Object. One could give you a "tree" (not to be confused with the cmd tree) and should be able to filter it for you. If it's not able to filter sufficiently look at Where-Object. Something along the lines of gci -Recurse | where Name -like '*1999*' could be the result.– Seth
Nov 13 at 13:53
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
As other suggested a bit of scripting in Powershell will make this happen. If not for this project, then the next one. I consider import-csv to be the strongest argument for an admin learning to script. Here's an example that might help you get started
# csvtest.ps1
# assume this file name
$fname = "c:datanaslist.csv"
$fcontent = import-csv $fname
# assume these column names in the csv: name and size
foreach ($row in $fcontent) {
write-host $row.name $row.size
}
answered Nov 13 at 15:22
uSlackr
8,3002445
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
As other suggested a bit of scripting in Powershell will make this happen. If not for this project, then the next one. I consider import-csv to be the strongest argument for an admin learning to script. Here's an example that might help you get started
# csvtest.ps1
# assume this file name
$fname = "c:datanaslist.csv"
$fcontent = import-csv $fname
# assume these column names in the csv: name and size
foreach ($row in $fcontent) {
write-host $row.name $row.size
}
answered Nov 13 at 15:22
uSlackr
8,3002445
add a comment |
up vote
0
down vote
As other suggested a bit of scripting in Powershell will make this happen. If not for this project, then the next one. I consider import-csv to be the strongest argument for an admin learning to script. Here's an example that might help you get started
# csvtest.ps1
# assume this file name
$fname = "c:datanaslist.csv"
$fcontent = import-csv $fname
# assume these column names in the csv: name and size
foreach ($row in $fcontent) {
write-host $row.name $row.size
}
answered Nov 13 at 15:22
uSlackr
8,3002445
add a comment |
up vote
0
down vote
up vote
0
down vote
As other suggested a bit of scripting in Powershell will make this happen. If not for this project, then the next one. I consider import-csv to be the strongest argument for an admin learning to script. Here's an example that might help you get started
# csvtest.ps1
# assume this file name
$fname = "c:datanaslist.csv"
$fcontent = import-csv $fname
# assume these column names in the csv: name and size
foreach ($row in $fcontent) {
write-host $row.name $row.size
}
answered Nov 13 at 15:22
uSlackr
8,3002445
As other suggested a bit of scripting in Powershell will make this happen. If not for this project, then the next one. I consider import-csv to be the strongest argument for an admin learning to script. Here's an example that might help you get started
# csvtest.ps1
# assume this file name
$fname = "c:datanaslist.csv"
$fcontent = import-csv $fname
# assume these column names in the csv: name and size
foreach ($row in $fcontent) {
write-host $row.name $row.size
}
answered Nov 13 at 15:22
uSlackr
8,3002445
answered Nov 13 at 15:22
uSlackr
8,3002445
answered Nov 13 at 15:22
uSlackr
8,3002445
answered Nov 13 at 15:22
uSlackr
8,3002445
8,3002445
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1375029%2fsearching-and-listing-folders-using-as-source-a-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Yes this is fairly easily done in powershell. A great tool to learn for system admins. Powershell can enumerate excel / csv Files and you can write some logic to enumerate the files and folders on the drive and see if it matches up with your list.
– Appleoddity
Nov 13 at 13:34
Could you give me some leads please about some specifics functions /commands about that? Google till now hasnt provided something specific since i want powershell to list me the paths where are the folders since i dont know their exact path. I'd prefer though a more visual way since i have to copy these folders to another place after i find them...
– Nick87
Nov 13 at 13:49
You'd probably need to look at
Get-ChildItemand maybeWhere-Object. One could give you a "tree" (not to be confused with the cmdtree) and should be able to filter it for you. If it's not able to filter sufficiently look atWhere-Object. Something along the lines ofgci -Recurse | where Name -like '*1999*'could be the result.– Seth
Nov 13 at 13:53
It’s not a single command. It’s a series of concepts. How to read excel files; how to traverse directory tree recursively; how to check if string contains a substring in an array; how to copy a folder. You might also check agent ransack or file locator pro. They can do Boolean logic searches.
– Appleoddity
Nov 13 at 13:56
thank you very much for your precious information guys!!! Yes i thought about Get-Childitem function as well since i had use it in another job where i was looking just for files bigger than 300mb on the NAS. So i thought this time i should use it for just matching names from a excel. But i was thinking as well if there is a function or VBA on MS excel where it can be linked directly with file manager of windows and search from a column or a selected number of cells
– Nick87
Nov 13 at 13:59