Comparing CSV files

PowerShell Help
1

Hello there, Next question.

I have 2 scripts, 1 is working, one is not. But I don’t know why one is not working. It looks ok for me.
first i will use the working one:

I have a CSV file on my computer. The first few lines are these:
A;B
00-00-0C;Cisco Systems, Inc
00-00-0D;FIBRONICS LTD.
00-00-0E;FUJITSU LIMITED
00-00-1B;Novell, Inc.

I import this file like this:

$map = @{}
Import-Csv C:\tmp\mac-vendors-export.csv -Delimiter ';' | ForEach-Object {
    $map[$_.A] = $_.B
}

What i want allready have achieved is to scan my network. retrieve mac addresses, and then compare it with this CSV, and show the corresponding column B result.

VendorList = $vendorMac | ForEach-Object {
    [pscustomobject]@{
        MAC      = $_
        Vendor = $map[$_]
    }

BUT, i have to go a step further. because in real live i don’t have this file on my computer. So i have to download it, and compare it after this. But the downloaded file is a litte different formatted.

so my code look like this:
invoke-webrequest -uri "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0" -outfile $Env:temp\mac-vendor.csv

the file look like this:
Mac Prefix,Vendor Name,Private,Block Type,Last Update
00:00:0C,“Cisco Systems, Inc”,false,MA-L,2015/11/17
00:00:0D,FIBRONICS LTD.,false,MA-L,2015/11/17
00:00:0E,FUJITSU LIMITED,false,MA-L,2018/10/13
00:00:1B,“Novell, Inc.”,false,MA-L,2016/04/27
00:00:23,ABB INDUSTRIAL SYSTEMS AB,false,MA-L,2015/11/17


Import-CSV $ENV:Temp\mac-vendor.csv -Delimiter ',' -Header 'MAC','Vendor' | Select-object 'MAC','Vendor' -Skip 1 | ForEach-Object {
    $map[$_.MAC] = $_.Vendor
}   
    


$VendorList = $vendormac | ForEach-Object {
    [pscustomobject]@{
        MAC      = $_
        Vendor = $map[$_]
    }
}

I see when i show the $vendorlist variable i receive no vendors
PS C:\WINDOWS\system32> $VendorList

MAC Vendor

74-83-c2
d2-51-e5
60-ab-14
18-e8-29
bc-cf-4f
2e-d9-2c
00-11-32
c0-3f-d5
18-e8-29

And in the working script it’s filled with information

so it must be somewhere in this part i think

$vendormac | ForEach-Object {
    [pscustomobject]@{
        MAC      = $_
        Vendor = $map[$_]
    }
}

I hope my question is clear enough

2

Before we proceed … please keep in mind … we cannot see your screen and we cannot read your mind … :wink:

Please go back, edit your question again and fix the formatting of your code, console output and sample data.
To do so use the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

3

ah that looks better!

4

Hmmm … no comment on that. :smirk:

So you want to add the vendor information to the list of MAC addresses you have from the scan of your network, right?

I’d use another approach. Instead of preparing a lookup hashtable I’d use the CSV data right away.

Could you share some sample input data from your network scan and some according vendor data?

And BTW: the format of your MAC addresses seems to be different. Once you have colons and once dashs. 74-83-c2 vs 00:00:0E!! :point_up_2:t4: :wink:

1 Like
5

First thing, you don’t specify headers when the csv already has headers, that’s going to screw up the data. Second thing, your vendorlist shows dashes in the MAC addresses, this will not match the link you’re trying to download as they are colons.

First example, simply convert to csv as it is

Invoke-RestMethod -uri "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0" -UseBasicParsing | ConvertFrom-Csv

However when you look at this, you’ll see the properties are not the friendliest (they include spaces.) You can fix these with some calculated properties.

$url = "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0"

Invoke-RestMethod -uri $url -UseBasicParsing |
    ConvertFrom-Csv | Select-Object @{n='MAC';e='Mac Prefix'},
                                    @{n='Vendor';e='Vendor Name'},
                                    Private,
                                    @{n='BlockType';e='Block Type'},
                                    @{n='Updated';e='Last Update'}

Or with a PSCustomObject

$url = "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0"

Invoke-RestMethod -uri $url -UseBasicParsing |
    ConvertFrom-Csv | ForEach-Object {
        [PSCustomObject]@{
            MAC       = $_.'Mac Prefix'
            Vendor    = $_.'Vendor Name'
            Private   = $_.Private
            BlockType = $_.'Block Type'
            Updated   = $_.'Last Update'
        }
    }

From there you can just select the two columns you want. However if you wanted to shortcut to this end, you could simply do

$url = "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0"

$output = Invoke-RestMethod -uri $url -UseBasicParsing

$output -split '\r?\n' | Select-Object -Skip 1 | ConvertFrom-Csv -Header Mac,Vendor,a,b,c | Select-Object Mac, Vendor

Now as we mentioned, the different MAC formats should be dealt with. If you want to change the colons to dashes, add to your calculated properties or PSCustomObject

$_.'Mac Prefix' -replace ':','-'

Like so

$url = "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0"

Invoke-RestMethod -uri $url -UseBasicParsing |
    ConvertFrom-Csv | Select-Object @{n='MAC';e={$_.'Mac Prefix' -replace ':','-'}},
                                    @{n='Vendor';e='Vendor Name'},
                                    Private,
                                    @{n='BlockType';e='Block Type'},
                                    @{n='Updated';e='Last Update'}

Or

$url = "https://maclookup.app/downloads/csv-database/get-db?t=22-12-28&h=a6f90f116bc4364ad17817cff62fd160fbff73c0"

Invoke-RestMethod -uri $url -UseBasicParsing |
    ConvertFrom-Csv | ForEach-Object {
        [PSCustomObject]@{
            MAC       = $_.'Mac Prefix' -replace ':','-'
            Vendor    = $_.'Vendor Name'
            Private   = $_.Private
            BlockType = $_.'Block Type'
            Updated   = $_.'Last Update'
        }
    }

And finally, you don’t have to create a hash map. You can access the properties of the data right from the objects created by the csv cmdlet. There are reasons why one would need or choose to do a hash table, just wanted to make sure you knew you didn’t have to.

1 Like