Trying to extract number from XML

[sachin86]

Hello,

Thank you so much for your time and help! I am trying to extract tax data from a large XML that contains several things. Here is an example of a piece of data:

<Order>
<OrderID>105-8920931-7716261</OrderID>
<PurchaseDate>2011-05-01T04:39:41+00:00</PurchaseDate>
<LastUpdatedDate>2011-05-01T11:38:02+00:00</LastUpdatedDate>
<OrderStatus>Shipped</OrderStatus>
<SalesChannel>merchant</SalesChannel>
<FulfillmentData>
<FulfillmentChannel>Merchant</FulfillmentChannel>
<ShipServiceLevel>Standard</ShipServiceLevel>
<Address>
<City>SHERMAN</City>
<State>TX</State>
<PostalCode>75090-3488</PostalCode>
<Country>US</Country>
</Address>
</FulfillmentData>
<OrderItem>
<ASIN>B0022R74BC</ASIN>
<SKU>MN-UPDC-Q1H3</SKU>
<ItemStatus>Shipped</ItemStatus>
<ProductName>Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades</ProductName>
<Quantity>2</Quantity>
<ItemPrice>
<Component>
<Type>Principal</Type>
<Amount currency="USD">140.0</Amount>
</Component>
<Component>
<Type>Tax</Type>
<Amount currency="USD">8.76</Amount>
</Component>
<Component>
<Type>Shipping</Type>
<Amount currency="USD">23.99</Amount>
</Component>
<Component>
<Type>ShippingTax</Type>
<Amount currency="USD">1.5</Amount>
</Component>
</ItemPrice>
</OrderItem>
</Order>


I have a list of thousands of these in one single column (one after the other). What I need to extract is the "Tax" amount collected. In this case, Tax is represented by:

<Component>
<Type>Tax</Type>
<Amount currency="USD">8.76</Amount>
</Component>
<Component>



What I want is an easy way to extract the 8.76 and perhaps put it in a separate column or some way in which I can add all of the taxes collected in an XML file that contains thousands of these individual transactions.

Your help would be much appreciated! Thanks so much for a great forum!!

 

[hiker95]

sachin86,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[hiker95]

sachin86,


Sample raw data in worksheet Sheet1 before the macro:

Excel Workbook
	A	B	C
1	105-8920931-7716261
2	2011-05-01T04:39:41+00:00
3	2011-05-01T11:38:02+00:00
4	Shipped
5	merchant
6
7	Merchant
8	Standard
9
10	SHERMAN
11	TX
12	75090-3488
13	US
14
15
16
17	B0022R74BC
18	MN-UPDC-Q1H3
19	Shipped
20	Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades
21	2
22
23
24	Principal
25	140
26
27
28	Tax
29	8.76
30
31
32	Shipping
33	23.99
34
35
36	ShippingTax
37	1.5
38
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	105-8920931-7716261		Tax
2	2011-05-01T04:39:41+00:00		8.76
3	2011-05-01T11:38:02+00:00
4	Shipped
5	merchant
6
7	Merchant
8	Standard
9
10	SHERMAN
11	TX
12	75090-3488
13	US
14
15
16
17	B0022R74BC
18	MN-UPDC-Q1H3
19	Shipped
20	Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades
21	2
22
23
24	Principal
25	140
26
27
28	Tax
29	8.76
30
31
32	Shipping
33	23.99
34
35
36	ShippingTax
37	1.5
38
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetTax()
' hiker95, 07/17/2011
' http://www.mrexcel.com/forum/showthread.php?p=2792897
Dim c As Range, firstaddress As String, NR As Long
Application.ScreenUpdating = False
Range("C1") = "Tax"
With Columns(1)
  Set c = .Find("Tax", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      If IsNumeric(c.Offset(1)) Then
        NR = Range("C" & Rows.Count).End(xlUp).Offset(1).Row
        Range("C" & NR).Value = c.Offset(1).Value
      End If
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the GetTax macro.

 

[steve case]

Why not just run a simple formula down a blank column?

=if(A1="TAX",A2,"")
.
.
.
.
.
.

 

[Rick Rothstein]

As long as the tax amount is always listed immediately under the word "Tax", I think this formula will get the sum for you...

=SUMIF(A1:A10000,"Tax",A2:A10001)

Note that the two specified ranges are the same size and offset one row from each other.... change the top end of the ranges so they will cover all of your data.

 

[sachin86]

Hello!

Thank you all so much for your help and effort! I really appreciate it! Unfortunately, it looks like because I did not know how to paste my code into the forum, the macros and other suggestions did not work. I have downloaded the Jeanie tool so I can paste properly. Here is the actual code I need to extract the tax number from (again, in this example it is $8.76).

Excel Workbook
	A
439
440
441	*105-8920931-7716261
442	*2011-05-01T04:39:41+00:00
443	*2011-05-01T11:38:02+00:00
444	*Shipped
445	*Merchant
446	*
447	* *Merchant
448	* *Standard
449	* *
450	* * *SHERMAN
451	* * *TX
452	* * *75090-3488
453	* * *US
454	* *
455	*
456	*
457	* *B0022R74BC
458	* *MN-UPDC-Q1H3
459	* *Shipped
460	* *Merkur Futur Double Edge Safety Razor #700 + 5 Free DE Razor Blades
461	* *2
462	* *
463	* * *
464	* * * *Principal
465	* * * *140.0
466	* * *
467	* * *
468	* * * *Tax
469	* * * *8.76
470	* * *
471	* * *
472	* * * *Shipping
473	* * * *23.99
474	* * *
475	* * *
476	* * * *ShippingTax
477	* * * *1.5
478	* * *
479	* *
480	*
481
482
4282070483

Now, please note that I need to calculate both the TAX and the SHIPPINGTAX amounts. In this case, that is 8.76 for TAX and 1.50 for SHIPPINGTAX.

Can you please help now that I have pasted it properly? I am so thankful I found this place!

 

[xenou]

This may be a good candidate for running a xml query directly on the xml source.

 

[snoopyhr]

Give this a try,

Excel Workbook
	A	B	C
1	Principal	*	*
2	140	*	*
3	*	*	*
4	*	*	*
5	Tax	*	8.76
6	8.76	*	*
7	*	*	*
8	*	*	*
9	Shipping	*	*
10	23.99	*	*
11	*	*	*
12	*	*	*
13	ShippingTax	*	*
14	1.5	*	*
15	*	*	*
16	* tax	*	34
17	34	*	*
Sheet8

 

[snoopyhr]

just saw your last post , edit the formula to this

=IF(TRIM(ISNUMBER(SEARCH("tax",A5,1))),A6,"")

 

[snoopyhr]

If you want Tax source on one column and Tax amount adajcent to it then try this :

Excel Workbook
	A	B	C	D
1	Principal	*	*	*
2	140	*	*	*
3	*	*	*	*
4	*	*	*	*
5	Tax	*	Tax	8.76
6	8.76	*	*	*
7	*	*	*	*
8	*	*	*	*
9	Shipping	*	*	*
10	23.99	*	*	*
11	*	*	*	*
12	*	*	*	*
13	ShippingTax	*	ShippingTax	1.5
Sheet8