Excel Assistance

Forums › Microsoft Windows › Excel Assistance

1 | 2

ObidiahSlope

260 posts

Ultimate Geek
+1 received by user: 66

#1714251 2-Feb-2017 00:30

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

andrew027

1286 posts

Uber Geek
+1 received by user: 557

#1714307 2-Feb-2017 08:41

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.

andrew027

1286 posts

Uber Geek
+1 received by user: 557

#1714314 2-Feb-2017 08:50

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

ObidiahSlope

260 posts

Ultimate Geek
+1 received by user: 66

#1716894 7-Feb-2017 22:22

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

TwoSeven

1712 posts

Uber Geek
+1 received by user: 304

Subscriber

#1717291 8-Feb-2017 17:04

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
(the practice of real science, engineering and management)
A.I. (Automation rebranded)
Gender Neutral
(a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

...they/their/them...

networkn

Networkn
32864 posts

Uber Geek
+1 received by user: 15454

ID Verified

Trusted

Lifetime subscriber

#1717315 8-Feb-2017 18:00

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.

Lenovo

Shop now for Lenovo laptops and other devices (affiliate link).

JayADee

2236 posts

Uber Geek
+1 received by user: 483

#1717328 8-Feb-2017 18:32

Congrats on the win. :)

andrew027

1286 posts

Uber Geek
+1 received by user: 557

#1718462 10-Feb-2017 20:44

@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