Network Assessment Tool Plus

Post series: Overview | Running the tool | Processing the data

This post covers how to process and import the data into PowerBI that we gathered earlier in the Network Assessment Plus process

Having run the Assessment Tool we now need to gather, process and import the results into PowerBI.

The looping script will have written out two files to its current path:

NetworkTest_PCNAME_ExtraInfo.csv – This contains the PC Name, OS culture, example date/time format, network interface, IP address

NetworkTest_PCNAME_Results.tsv – This is the combined results for of all the tests run from that computer

Import TSV into Excel

Open Excel and open the results.tsv file
Excel should fire up it’s “text import wizard”
Tick the “my data has headers” box
Move to the next screen & make sure the delimiter is set to “Tab”
From here you can just hit Finish and jump out into Excel which should look something like this:
Check that the data looks good by expanding out the columns and then add “site” as a header to Column H. Fill in the column with a short name that represents where this data came from
Check down to the bottom of the file and make sure everything looks good

Next we want to un-mangle the date / time column as Excel will truncate it if we export at this point..

Note: If you have collected multiple files from different parts of the world you want to standardise the date/time format across your files at this point so that PowerBI will treat them all the same when imported. For this example we are assuming UK format, because hey, that’s where I’m from Smile

Select Column A, then select “More number formats” in the Type drop down
Then set the type to “Custom” and enter “dd/mm/yyyy hh:mm:ss” (or whatever you want for your culture setting”)
Hit Okay and you should see the full date/time appear in Column A
Now we’re done in Excel and can save the file out as a CSV
Open the file in a text editor and check things look good

Now repeat the process for any other files you want to included in the analysis. Make sure you don’t have any overlapping site codes etc.. or your results will get muddled together.

Combine and import

Once all the files have been processed you want to combine them into a single .CSV I suggest using Network_Assessment_Combined.csv from the root of the tool download
- Don’t forget to delete the existing contents & don’t include the headers from the extra files you want to use.
Once the files are all combined save back the CSV and hop into PowerBI.
Open the latest “Network_Assessment_Plus_PowerBI_vX.pbix” file
From the “Edit Queries” menu select “Data Source Settings”
Click “Change Source”
Select your new “combined” file and click okay then close
You should see a yellow information box for pending changes, hit apply and watch the data refresh
All things being well you should see the new site names appear on the left of the output