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.

excel column

edited Nov 13 at 14:03

Blackwood

2,66561727

asked Nov 13 at 13:30

Nick87

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-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

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

As a junior system admin I was tasked with a data management chore on our NAS device.

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.

excel column

edited Nov 13 at 14:03

Blackwood

2,66561727

asked Nov 13 at 13:30

Nick87

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-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

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

As a junior system admin I was tasked with a data management chore on our NAS device.

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.

excel column

edited Nov 13 at 14:03

Blackwood

2,66561727

asked Nov 13 at 13:30

Nick87

As a junior system admin I was tasked with a data management chore on our NAS device.

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.

excel column

microsoft-excel worksheet-function nas

edited Nov 13 at 14:03

Blackwood

2,66561727

asked Nov 13 at 13:30

Nick87

edited Nov 13 at 14:03

Blackwood

2,66561727

asked Nov 13 at 13:30

Nick87

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

asked Nov 13 at 13:30

Nick87

asked Nov 13 at 13:30

Nick87

asked Nov 13 at 13:30

Nick87

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-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

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 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

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

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

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 |

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 |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

# 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

# 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

# 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

# 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

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Jtdylktuy