Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


View this topic in a long page with up to 500 replies per page Create new topic
1 | 2 
220 posts

Master Geek
+1 received by user: 35


  # 1714251 2-Feb-2017 00:30
One person supports this post
Send private message

Helpful hint: When confronted with a wodge of hard to understand XML my favourite tool is XML Notepad 2007 available as a free download from Microsoft. I find the tree view in the editor is helpful understanding the XML structure.

 

 

 





Obsequious hypocrite

1255 posts

Uber Geek
+1 received by user: 533


  # 1714307 2-Feb-2017 08:41
One person supports this post
Send private message

I'd rather set this up with proper conditional formatting formulae, but the easiest way to explain is probably to use a filter. Add a header row to the top of your data, e.g. 'Date' in column A and 'Text' in column B. Select all your data and shade it one colour. Then filter column A to only show rows that contain 'sent' and shade the filtered data a different colour. Then clear the filter. Boom.

 
 
 
 


1255 posts

Uber Geek
+1 received by user: 533


  # 1714314 2-Feb-2017 08:50
Send private message

If you want to use a formula, and assuming the column that contains 'sent' or 'received' is column A and the first row of data is row 1, then select all the data and use the formula::
=NOT(ISERROR(FIND("sent",$A1)))

Repeat for "received".

220 posts

Master Geek
+1 received by user: 35


  # 1716894 7-Feb-2017 22:22
Send private message

The original poster in this thread was trying to extract information that matched certain criteria from an XML file and transfer it to a CSV file.

 

Windows PowerShell can do this stuff without even breaking a sweat. Here is an example. I downloaded an example XML file from Microsoft's MSDN site. It contains a catalogue of books with certain attributes. Here is the link to the file;

 

https://msdn.microsoft.com/en-us/library/ms762271(v=vs.85).aspx

 

To start I load the file contents into a PowerShell XML variable;

 

[xml]$library = Get-Content .\books.xml

 

My method is to traverse the XML down to the books node, pipe the output to a SELECT command to extract the required attributes for each book, pipe to a WHERE command to filter only the records we want, and then pipe the result to the Export-CSV commandlet to create a properly formatted CSV file with the information we want extracted. Here is the command;

 

 $library.catalog.book | select title, author, genre |where {$_.genre -eq "Romance"} |Export-Csv romance.csv

 

All hail PowerShell!





Obsequious hypocrite

1348 posts

Uber Geek
+1 received by user: 159


  # 1717291 8-Feb-2017 17:04
Send private message

Excel can import data from an xml file quite easily. The menu can be found on the data tab under 'from other sources.

Once the data is imported, one can create a new query (from table) which is part of Power BI (get and transform on the data tab in 2016, or by using the plugin on ealier versions). In the query one can perform quite a few transformations or one can use the advanced editor and the M (mashable) language, although is is called something new now.

Also, instead of importing the xml file, one can connect to it as a data source using the 'new query' from file option.




Software Engineer

 




20020 posts

Uber Geek
+1 received by user: 6097

Trusted
Lifetime subscriber

  # 1717315 8-Feb-2017 18:00
One person supports this post
Send private message

Thanks for all the replies. 

 

We attended the DT meeting with the 15 pages of plain text text messages but they didn't even want to see it. They were aware of this dimwit and his antics and since he didn't bother to attend to defend himself, they pretty quickly found in our favour. 

 

I still don't understand how I can get conditional formatting to colour the entire row, that is still something I'd be interested to know. 

 

 


1636 posts

Uber Geek
+1 received by user: 238


  # 1717328 8-Feb-2017 18:32
One person supports this post
Send private message

Congrats on the win. :)

1255 posts

Uber Geek
+1 received by user: 533


  # 1718462 10-Feb-2017 20:44
Send private message

@networkn: I still don't understand how I can get conditional formatting to colour the entire row, that is still something I'd be interested to know. 

 

The formula in my second post above should do it. 


1 | 2 
View this topic in a long page with up to 500 replies per page Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

HPE to acquire supercomputing leader Cray
Posted 20-May-2019 11:07


Techweek starting around NZ today
Posted 20-May-2019 09:52


Porirua City Council first to adopt new council software solution Datascape
Posted 15-May-2019 12:00


New survey provides insight into schools' technology challenges and plans
Posted 15-May-2019 09:30


Apple Music now available on Alexa devices in Australia and New Zealand
Posted 15-May-2019 09:11


Make a stand against cyberbullying this Pink Shirt Day
Posted 14-May-2019 20:23


Samsung first TV manufacturer to launch the Apple TV App and Airplay 2
Posted 14-May-2019 20:11


Vodafone New Zealand sold
Posted 14-May-2019 07:25


Kordia boosts cloud performance with locally-hosted Microsoft Azure ExpressRoute
Posted 8-May-2019 10:25


Microsoft Azure ExpressRoute in New Zealand opens up faster, more secure internet for Kiwi businesses
Posted 8-May-2019 09:39


Vocus Communications to deliver Microsoft Azure Cloud Solutions through Azure ExpressRoute
Posted 8-May-2019 09:25


Independent NZ feature film #statusPending to premiere during WLG-X
Posted 6-May-2019 22:13


The ultimate dog photoshoot with Nokia 9 PureView #ForgottenDogsofInstagram
Posted 6-May-2019 09:41


Nokia 9 PureView available in New Zealand
Posted 6-May-2019 09:06


Motorola Solutions joins local partners to deliver advanced communications network in New Zealand
Posted 30-Apr-2019 21:50



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.