Links: Enterprise Solutions, Business Intelligence and Appliance Solutions
Episode 5 | Dell Boomi HTTP to CSV
This episode combines the previous processes to extract and map an XML file from the USPTO Web site to a CSV file on a local disk.
The USPTO project uses Dell Boomi, Dell Quickstart Data Warehouse Appliance and Toad products to analyze publicly available data. For more information about the goal and scope of the project, as well as a breakdown of the episodes.
Dell Boomi HTTP to CSV process
In Episode 3, we created a Dell Boomi process to extract data from USPTO. In Episode 4, we highlighted mapping from XML to CSV. Now, we combine these processes to connect to the HTTP file and translate it directly to CSV. The following sections outline this episode:
- Combine processes
- Copy First Project
- Add map from XML to CSV project
- Modify XML profile
- Format malformed XML
- Comment headers
- Add root tag
- Run process
Most components used in this episode are the same components from the first project. Begin by copying the first process. Then add in the map components from the second process. Adding the map components loads the data to CSV. These steps bring us one step closer to loading the data into the database.
To copy the previous projects:
Figure 1 : select the blue triangle next to First project and select Copy
- In the Component Explorer, select the blue triangle next to First project and select Copy.
2.In the Copy Component window, select the same folder used in the previous project (in this example Dell-REP), and clear the Copy component dependents? check box.
Figure 2 : Selecting Dell-Rep folder
4.In Component Explorer, double-click the process named First Project 2.
5.Rename the process to HTTP to CSV.
Adding a map
To add a map:
Figure 3 :
- Drag and drop a map shape to the newly created process.
- Click the icon to view all components.
- Select the XML to CSV map created in the previous process
Select the XML to CSV map created in the previous process
Modifying the filename
The process output is a CSV file. The first task is to modify the fileName shape.
- Highlight the fileName shape and click Configure.
- Change the name to fileName test.csv and then select the icon under Parameters field.
- Change the filename to test.csv and select OK.
- Click OK.
Modifying the XML profile
Next, modify the map to parse all the patents in the file we downloaded from the site. For the Dell Boomi XML parser to extract data, the XML file must have a single parent element that encapsulates the data. To do this, add the element to the file. After adding the element to the file, modify the XML profile.
To modify the XML profile:
Figure 4 :
- Go to Component Explorer and double-click the USPTO_XML profile.
Go to Component Explorer and double-click the USPTO_XML profile.
- You must manipulate the nodes because there is no way to add parent nodes to the current top parent node.
Figure 5 :
- In the us-patent-grant element drop-down menu, select Add Child Element. This creates an element called element at the bottom of the profile.
and rename it to us-patent-grant.
Figure 6 :
- Select element and rename it to us-patent-grant.
- Set Min Occurs to 1 and Max Occurs to unbounded. This change makes sure that you retrieve multiple patents.
Set Min Occurs to 1 and Max Occurs to unbounded
Figure 7 :
- Select the topmost us-patent-grant element and rename it root.
- Set the Min Occurs and Max Occurs to 1. The profile should now look like Figure 6
Set the Min Occurs and Max Occurs to 1
Figure 8 :
- Move all elements except root under the newly created us-patent-grant element by dragging the elements to the us-patent-grant element. Once the elements are added, a plus sign appears.
Figure 9 :
- Drop the element and it should now be added as a child element to us-patent-grant.
Drop the element and it should now be added as a child element to us-patent-grant
Figure 10 :
- Repeat this process for all remaining elements until the profile looks like Figure 9
Final view of the us-patent-grant element
Formatting malformed XML
To deal with the malformed USPTO XML, add two shapes:
- One shape to deal with the multiple DTD headers
- One shape to add to the
Before creating shapes, adjust the process to make room.
To adjust our process to make room:
Your process should look like Figure 10
- Disconnect the arrow from the Unzip and fileName shapes.
- Connect the arrow from XML to CSV to filename.
Figure 11 : Making room by adjusting our process.
The next step is commenting out two tags. This is similar to how we manually removed them in the previous episode. This time, use XML style comments to remove them.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE us-patent-grant SYSTEM "us-patent-grant-v42-2006-08-23.dtd" [ ]>
To comment headers:
<!-- xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE us-patent-grant SYSTEM "us-patent-grant-v42-2006-08-23.dtd" -->
- Hghlight the shape and select Configure.
- Change the name to Unzip/Comment so it reflects its new purpose.
- To add a step, under Processing Step, select the (+) icon.
- In the drop-down list, select Search/Replace, and then enter the following information:
Text To Find: \<\?xml
Replace With: <!--
*Note: Modify the string <!--?xml to \<\?xml because the underlying code running Dell Boomi needs escape characters for special characters to process the string. Similarly, in the next step the string [ ]--> is modified to \[ \]\>.
- Add a second step by selecting the (+) icon and selecting a Search/Replace process with the following text boxes:
Text To Find: \[ \]\>
Replace With: -->
Figure 12 : Data Process Properties
Adding a root element
Use a Message shape to add the root element to our document.
To add a root element:
- Drag-and-drop the shape into the process and name it Add Root.
- In the Message Properties window, under the Parameters field, click the (+) icon.
- In the Parameter Value window, from the drop-down list, select Current Data.
- Click OK.
- In the Message Properties dialog box, in the Message text box, add the following:
Figure 13 : Message Properties dialog box
Figure 14 : The completed process should look like this.
- Clik OK
- Click Save and Close.
The Run process
The addition of the Search/Replace and Message steps greatly affect the memory usage of the Atom. The Dell Boomi support team is looking for a solution. The team modified the memory allocated to the Atom for the process to complete. If you see a Java heap error, follow the steps in the Modify Atom section.
Once you save and deploy your process, the test.csv is stored in your local directory. The filename depends on whether you have run the previous process multiple times. The file should be approximately 338kb and have multiple entries for each patent. For direction on how to deploy a process, refer to the link Boomi Getting Started .
When successfully run, the test.csv should look like the document snippet in Figure 15.
Figure 15 : A successfully run test.csv.
The following steps are found on the Dell Boomi Community page. You can find these steps by going to the Help & Feedback drop-down list in the upper right corner of the page and selecting Support & Community. This launches a separate User Community page where you can use the Search Community search bar. For this example, the instructions are modified to reflect the amount of memory used to make the process work correctly.
To increase the memory:
- Stop the Atom.
- Navigate to: \Boomi AtomSphere\<Atom name>\bin.
- Using a text editor, such as Notepad, open the atom.vmoptions file.
- Change -Xmx512m to –Xmx16384m.
- Save the file and restart the Atom.
*Note: For this project, we are using the Dell Quickstart Data Warehouse Appliance, which has 96GB to 128GB of installed RAM. This modification to the 16GB of RAM allocated to the Atom was not an issue for our team. If you are following these episodes on a system with memory close to 16GB, this procedure may cause issues.