Solved

How do I detach/Save/Open a ZIP file that has been embedded in an XML output file?

Posted on 2011-02-14
25
1,109 Views
Last Modified: 2012-05-11
Greetings All...

I have an XML file that is being returned from an API call to eBay. The call requests SOLD data and the function returns the data as a Base64 encoded, embedded file. The XML tags declare this data as a ZIP file....

While I know how to read and parse XML test files from within VB6, this is my firat encounter with an embedded 'DATA' file.

I need to be able to 'Un-bundle'  this data so that I can read and parse this 'Sold Data' into a MySQL database...

Thanks in advance for any help that you can provide.

rrbecker
0
Comment
Question by:rrbecker
  • 13
  • 8
  • 2
  • +1
25 Comments
 
LVL 6

Expert Comment

by:PJBX
Comment Utility
This is for images, but should be similar to extract the PDF:
http://www.tek-tips.com/viewthread.cfm?qid=1402602&page=5
0
 
LVL 6

Expert Comment

by:PJBX
Comment Utility
Sorry Zip file
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi PJBX... thanks, I'll work on it tomorrow looks like it will work.... It is my Bed time,,,heheh
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi PJBX.... Ok I have been working with that sample and it looks like it should work but I guess that I just don't know how to set up the input file...

The input file that I am using has the following structure:

----------------------------------------------
--MIMEBoundaryurn_uuid_158243C78DE731D96C129778318327331577
Content-Type: application/xop+xml; charset=utf-8; type="text/xml"
Content-Transfer-Encoding: binary
Content-ID: <0.urn:uuid:158243C78DE731D96C129778318327331578>

<?xml version='1.0' encoding='UTF-8'?><downloadFileResponse xmlns="http://www.ebay.com/marketplace/services"><ack>Success</ack><version>1.1.0</version><timestamp>2011-02-15T15:19:43.273Z</timestamp><fileAttachment><Size>159915</Size><Data><xop:Include xmlns:xop="http://www.w3.org/2004/08/xop/include" href="cid:urn:uuid:336955AC48A62F315C1297783203934"/></Data></fileAttachment></downloadFileResponse>
--MIMEBoundaryurn_uuid_158243C78DE731D96C129778318327331577
Content-Type: application/zip
Content-Transfer-Encoding: binary
Content-ID: <urn:uuid:336955AC48A62F315C1297783203934>

PK?????? - THE ATTACHED ZIP FILE/DATA IS HERE AND STARTS WITH 'PK'-

--MIMEBoundaryurn_uuid_158243C78DE731D96C129778318327331577--
----------------------------------------------------------------------------------------------

Effectively the ZIP file is contained between the --MIMEBoundaryur--- boundary tag.

When I strip out the Boundary Tags (with Notepad) I get an error and the function 'crashes'

I could attach the file if that would be of any use to you....

Thank you for you help in this....

rrbecker
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I could attach the file if that would be of any use to you....

I am not sure if I can help you but may I see the file? Let me have a shot at it.

Sid
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi Sid... Thanks a bunch... let me create a new one, I have butchered the other ones...

I'll attach my code as well...

--------------------Code------------------------------
xmlHTTP.send (StartJobRequest$)
   
    'Get the server's response
    sResponse = xmlHTTP.responseText
   
    VarStringToTextFile "C:\ebayresponseFile.xml", sResponse
    'sResponse = TextFileToVarString("C:\ebayresponseFile.txt")
   
    Dim sFile As String
   
    sFile = "C:\ebayresponseFile.xml"
    Call XMLSaveBinaryFile(XMLDecodeBase64(sResponse), sFile)
------------------------------------------------------------------------------------------

--------------------Functions----------------------------------------------------------
Public Function XMLDecodeBase64(ByVal strData As String) As Byte()
    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement
   
    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
    objNode.dataType = "bin.base64"
    objNode.Text = strData
    'objNode.xml = strData
    XMLDecodeBase64 = objNode.nodeTypedValue
   
    Set objNode = Nothing
    Set objXML = Nothing
End Function

Public Sub XMLSaveBinaryFile(ByRef vConvertedImage As Variant, ByVal sFileName As String)
    Dim strmImage As ADODB.Stream
    Dim options As ADODB.SaveOptionsEnum
   
    Set strmImage = New ADODB.Stream
    strmImage.open
    strmImage.Type = adTypeBinary
    strmImage.Write vConvertedImage
    strmImage.flush
    strmImage.SaveToFile sFileName, adSaveCreateNotExist
    strmImage.Close
   
   
    Set strmImage = Nothing
End Sub
--------------------------------------------------------------------------------

Thanks Sid
ebayresponseFile.txt
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hey Sid... the lines that say the file is a XML file (sFile = "C:\ebayresponseFile.xml") is incorrect. It is saved and imported as a TXT file.... sorry

Rick
rrbecker
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
I find this all a bit worrying. The "ebayresponseFile.txt" seems to be the result of an HTTP Request, in fact a multi-part response - it has a header which declares a mime and a body which looks like, and sort of according to the header, is an XML file. But it is NOT a conformant XML file since it contains binary values, specifically hex 03 and hex 06, which are NOT allowed in XML files (only hex 09,0D,0A and possibly 15 are allowed under hex 20). This eliminates the possiblity (and indeed it is not mentioned in the mime) that it is base64 encoded, because the latter is even more restrictive on the characters being used.

But what is even more strange is that the data looks like a PK Zip file (the PK at the start of the binary data) but there does not seem to be any data greater than hex 80. I would be very surprised indeed if the data was a correctly zipped without actually having to use such characters. In fact there are an awful lot of hex 3Fs so it looks like it's been through something which has reduce it to 7 bit.

Once this problem of the integrity of the data has been cleared up, the best approach is to extract the data "by hand" (NOT use an XMLDOM since it won't parse) and pass it to some object which can uncompress PK data (which most compression iobjects can).
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
PS: an open URL where I could access the original data would be handy, then I could look at it at source and not through various tool transformations.
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BigRat....  Thanks for stepping in...

This is the result of an API call the is established by eBay to extract a SoldReport. The data (once unbundled)  should be in the form of an Excel file with column headers as the first line.

I do not have access to this data except through this API or through an eBay User interface.

I will would like to provide the entire VB6 function but the 'User Token' has passwords embedded into it.
However I will provide that in and email to you if you feel that it is, or will be necessay, to provide a solution...

Anyway here is the code I am using that extracts the data that I have already attached:

Please Note that a lot of code is Commented out... Results of my hacking... :)

Thanks in advance....

Rick
rrbecker
Private Sub btnDownLoadReport_Click()
    'On Error GoTo MyTmpError
    
    Dim xmlHTTP As New MSXML2.ServerXMLHTTP40
    Dim MyBase64 As New Base64
    
    Dim XMLNodeValue As String
    Dim sContactURL As String
    Dim sUploadContents As String
    Dim sResponse As String
    
    Dim csUserNamePassword As String
    Dim sFileName As String
    
    Dim sEntityBody As String
    Dim MyTokenString As String
    
    Dim MULTIPART_BOUNDARY As String
    Dim sResponseArray() As String
    
    Dim RtnVal As Integer
    Dim RtnValue As Integer
    
    RtnValue = MsgBox("Are You Sure You Want to Generate a SoldReport File?" & vbNewLine & vbNewLine & sFileName$, vbYesNo, "Upload Amazon List")
    If RtnValue = vbNo Then
        Exit Sub
    End If
    
    'set eBay token value
    If Me.ValuetuensListOption.Value = True Then
        MyTokenString = "TOKENS REMOVED"
    Else
        MyTokenString = "TOKENS REMOVE"
    End If
    
    'The URL to post to
    'sContactURL = "https://bulksell.ebay.com/ws/eBayISAPI.dll?FileExchangeUpload"
    sContactURL = "https://webservices.ebay.com/BulkDataExchangeService"
     
    'Now Open the a new request
    Call xmlHTTP.open(bstrmethod:="POST HTTP/1.0", bstrurl:=sContactURL, varAsync:=False)
    
    'Set up required headers
    'xmlHTTP.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & MULTIPART_BOUNDARY
    
    
    '#################################################################
    ' ALL POSSIBLE fields
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '<?xml version="1.0" encoding="utf-8"?>
    '<startDownloadJobRequest xmlns="http://www.ebay.com/marketplace/services">
    '  <downloadJobType> token </downloadJobType>
    '  <downloadRequestFilter> DownloadRequestFilter
    '    <activeInventoryReportFilter> ActiveInventoryReportFilter
    '      <auctionItemDetails> AuctionItemDetails
    '        <includeBidCount> boolean </includeBidCount>
    '        <includeReservePriceMet> boolean </includeReservePriceMet>
    '      </auctionItemDetails>
    '      <fixedPriceItemDetails> FixedPriceItemDetails
    '        <includeVariations> boolean </includeVariations>
    '      </fixedPriceItemDetails>
    '      <includeListingType> IncludeListingType </includeListingType>
    '    </activeInventoryReportFilter>
    '    <feeSettlementReportFilter> FeeSettlementReportFilter
    '      <startTime> dateTime </startTime>
    '    </feeSettlementReportFilter>
    '    <siteFilter> SiteFilter
    '      <globalId> token </globalId>
    '      <!-- ... more globalId nodes here ... -->
    '    </siteFilter>
    '  </downloadRequestFilter>
    '  <recurringJob> boolean </recurringJob>
    '  <UUID> string </UUID>
    '</startDownloadJobRequest>
    
    '<?xml version="1.0" encoding="utf-8"?>
    '<startDownloadJobRequest xmlns="http://www.ebay.com/marketplace/services">
        '<downloadJobType>SoldReport</downloadJobType>
        '<UUID>f7f28d10-83bd-11df-8395-0800200c9a66</UUID>
    '</startDownloadJobRequest>
    
    
    'Dim oHttReq As New XMLHTTPRequest
    'Call oHttReq.open("POST", URL, False)
    'Call oHttReq.send(XMLDocument)
    'sContactURL = "http://www.ebay.com/marketplace/services"
    sContactURL = "https://storage.ebay.com/FileTransferService"
    'sContactURL = "https://webservices.ebay.com/BulkDataExchangeService"
     
    'Now Open the a new request
    'Call xmlHTTP.open(bstrmethod:="POST HTTP/1.0", bstrurl:=sContactURL, varAsync:=False)
    Call xmlHTTP.open("POST", sContactURL, False)

    '###################################################################
    'create the xml request to Generate a report
    ''''''''''''''''''''''''''''''''''''''''''''''
    xmlHTTP.setRequestHeader "HTTP_USER_AGENT", "Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)"
    xmlHTTP.setRequestHeader "X-EBAY-SOA-OPERATION-NAME", "downloadFile"
    xmlHTTP.setRequestHeader "X-EBAY-SOA-SECURITY-TOKEN", MyTokenString$
    xmlHTTP.setRequestHeader "X-EBAY-SOA-SERVICE-VERSION", "1.0.0"
    
    '<?xml version="1.0" encoding="utf-8"?>
    '   <downloadFileRequest xmlns:="http://www.ebay.com/marketplace/services">
    '       <taskReferenceId>5011631544</taskReferenceId>
    '       <fileReferenceId>5008335404</fileReferenceId>
    '   </downloadFileRequest>>


    Dim StartJobRequest As String
    Dim ThisUUID As String
    
    ThisUUID = ""
    ThisUUID = GetGUID
    
    StartJobRequest = ""
    StartJobRequest = StartJobRequest$ & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & "?>" & vbNewLine
    StartJobRequest = StartJobRequest$ & "<downloadFileRequest xmlns=" & Chr(34) & "http://www.ebay.com/marketplace/services" & Chr(34) & ">"
    StartJobRequest = StartJobRequest$ & "<taskReferenceId>5011631544</taskReferenceId>"
    StartJobRequest = StartJobRequest$ & "<fileReferenceId>5008335404</fileReferenceId>"
    StartJobRequest = StartJobRequest$ & "</downloadFileRequest>"
    
    MsgBox StartJobRequest$
    xmlHTTP.send (StartJobRequest$)
    
    'Get the server's response
    sResponse = xmlHTTP.responseText
    
    VarStringToTextFile "C:\ebayresponseFile.txt", sResponse
    'sResponse = TextFileToVarString("C:\ebayresponseFile.txt")
    
    Dim sFile As String
    
    sFile = "C:\ebayresponseFile.txt"
    Call XMLSaveBinaryFile(XMLDecodeBase64(sResponse), sFile)
    
    
    MsgBox sResponse
    'If InStr(sResponse$, "File upload successful") > 0 Then
    '    Screen.MousePointer = vbDefault
    '    PostListingsToEbay = "SuccessfulUpload"
    '    Exit Sub
    'Else
    '    Screen.MousePointer = vbDefault
    '    PostListingsToEbay = "UN-SuccessfulUpload"
    '    Exit Sub
    'End If
    
    Screen.MousePointer = vbDefault
    
    Exit Sub

MyTmpError:
    Beep
    MsgBox ("Error Occured: " & Err & " " & Err.Description)
    Screen.MousePointer = vbDefault
    Exit Sub
    
End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
OK, we'll start from there.

Now around line 126 we do sResponse = xmlHTTP.responseText
This puts the complete body of the response into sResponse as a byte string.

The next line I don't understand :-

    VarStringToTextFile "C:\ebayresponseFile.txt", sResponse

and this MIGHT be the first problem.

The next lines I also don't quite understand :-

    sFile = "C:\ebayresponseFile.txt"
    Call XMLSaveBinaryFile(XMLDecodeBase64(sResponse), sFile)

It seems as if there is a routine XMLDecodeBase64 which will do something to the returned data. It is this routine, or the bit before, which has corrupted the ebayresponseFile.txt which you have posted.

I'd like to have the decode routine, but more importantly I'd like to have the contents of sResponse BEFORE any processing has been done (with FSO perhaps) if I can't have access to a URL.

Any chance?

PS: It's Friday, I'm inEurope and it's almost 15:30!
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BigRat...

The XMLDecodeBase64 functions are included in this Post... a couple of post up from here.

the VarStringToTextFile "C:\ebayresponseFile.txt", sResponse just copies the response to a file so that I can include it in the call to XMLSaveBinaryFile(XMLDecodeBase64(sResponse), sFile)

And BTW... the call to XMLSaveBinaryFile(XMLDecodeBase64(sResponse), sFile) is just an attempt to extract the ZIP file.

the REAL data is the the sResponse = xmlHTTP.responseText. this is the data of Interest....

provide me an email and I will email the TOKEN....

thanks...

I know the week-end is coming up so we can work on this on monday... ok?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Expert Comment

by:BigRat
Comment Utility
OK, the E-mail is in my Profile - just click on my name
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BR... I had a not so good weekend.. I'll put together a piece of code together that includes the TOKEN. I will then email that you.

The code will only include that which results in a 'successful' call to eBay and eBay's response. All the other stuff that I had used to try and extract the 'ZIP' file will be eliminate...

Thanks again for you offer of help... and please bear with me...

Rick
rrbecker
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
Hi Ricky! I have received the e-mail, but since I don't have the relevant software installed I really can't run it. What I was wanting was the response from the call in a file. Is that possible?
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
PS: Don't worry about time nor points - I've got hooked on the problem now!
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BR....

hmmmm there in-lies the problem...  the data that posted here IS the data that i am getting back....

however this might help: here are 2 links to ebay that explains (sort of :) ) the API call. The first one references the call it self the second one is a 'Definition' of the 'Attached File'

http://developer.ebay.com/DevZone/file-transfer/CallRef/downloadFile.html
http://developer.ebay.com/DevZone/file-transfer/CallRef/types/FileAttachment.html

Maybe we could try some 'Screen Sharing' ??

Rick
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BR.... did ya give up on me?

Do you use Skype? Can I share my Screen with you sometime?


Let me know...

Rick
0
 
LVL 27

Accepted Solution

by:
BigRat earned 500 total points
Comment Utility
No Ricky, I was waiting for a response to my previous question. I have read the documentation, but what I previously saw from you was some binary data in the fields.

If the Docu is correct then I'd do something like this :-

    DIM sDOM
    sDOM = xmlHTTP.responseText
    IF sDOM.selectSingleNode('/downloadFileResponse')=nothing THEN
          -- error message returned
    END IF

    DIM Data = sDOM.selectSingleNode('/downloadFileResponse/fileAttachment/Data');
    IF Data = NOTHING then
        -- there must be an error tag instead - output that
    END IF

    -- now we only need a routine to decode base64 data
     DIM Str
     Str = decodeBase64(Data.text);

is roughly how it would go (PS I can't write VB script to save my life) but that is the case for simple file download. After the Str has been obtained one needs a ZIP routine tio unpack a PKZIP format.

At the bottom of the page there is a download for BULK data transfer. The first part of the multi sectioned response is an XML file which contains as the file attachment data an ID which is the content ID of the section which actually contains the file IN BINARY. The latter interface, which is difficult to decode, has the advantage of not bloating the binary data by base64 conversion.

In the bulk case, one must take the entire response text (which could be a very long string), split the sections apart. Load the first section into an XML DOM, loop on the attachments extracting the Content-Id and then picking the relevant section and decoding that.

Well what is it to be?
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi.. BR,

In the links there is a definition of the 'Data' and 'Fileattachment' it states

 "fileAttachment       FileAttachment       Always       A zipped report file that is encoded in Base64 Binary format and included in the response according to the SOAP MTOM standard.

So the 'Attachmant' is SOAP compliant and I know nothing about SOAP.. I assume that this is where my problems  lie and why I am having trouble. Doe this piece of information help you any?

Rick
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi again BR... I'll work on your suggestion and let you know how it goes...

Thanks again for  your help

Rick
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
>>Does this piece of information help you any?

Yes and no. An MTOM response is a multipart related mime which needs to be decoded in order to get at the data. I have search around a bit for a control for such a decode and have found none for VB, just a load of Java stuff. The processing is going to be a bit messy.

First I would search the responseText preoperty and extract the boundary. This follows an RFC of which this link explains it http://www.w3.org/Protocols/rfc1341/7_2_Multipart.html

The boundary therefore must be extracted from a "Content-Type" line, indeed the first one encountered. Note that the Content-Type: key word, like the boundary keyword is case insenstive whereas the boundary must be extracted and used AS IS.

After each --boundary line (two minus signs in front) comes a set of lines and a blank line and then some data upto the next --boundary, call this a section or part. The first section of data is the XML data and this corresponds to the XML data listed above. The next section or indeed sections contain BINARY data which is the contents of the zip file. There is NO base64 encoding.

Now think about that for a start. Ideally one would like to have a component which does all of this decoding and one just queries the number of attachments, their type and extracts the data. This thing would handle all the complexities of the transmission. In fact the way in which this is intended to be processed is on the fly - that is one stores the files away without buffering the complete message in memory. For this the Windows WSE system was created. And if you are interested in using that here is an example of receiving a file http://msdn.microsoft.com/en-us/library/aa529330.aspx




0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BR....

Thanks for the great info. Things are pretty hectic and I am working on another part of this 'Over All' function. I will get on these suggestions probably tomorrow (Friday). I again will let you know how it goes.

Thanks for everything and especially thanks for Sticking with me....

Rick
0
 
LVL 2

Author Comment

by:rrbecker
Comment Utility
Hi BR....

Well I have not made any progress on this project and I have now been pulled off to do another project.

I am going to Close this request and award you the Points for your help.

Nobody  else has 'Chimed In' so I have to assume that everyone is as Clue Less as I am.

Thanks for working with me and if I Re-Open this topic maybe you will be available to help again. I know there is a solution out there some where.

Thank again,

rrbecker
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now