Tutorial

Tutorials

How To Do XML to CSV Conversion in Less Than 10 Minutes

Transcript

Welcome and thank you for joining me for this video demo where I’m going to demonstrate how to convert an XML formatted file to a CSV formatted file in just four easy steps. But you can do 1,000 things with Adeptia Connect, such as integrating two cloud applications, automating file transfers between companies, or mapping data between different file formats. If you want to find out how to reduce integration projects from weeks to minutes, or have any other questions, call us at 312-229-1727. For now, let me show you how to perform this simple XML to CSV conversion. I’m already logged into Adeptia Connect, and within the connections page, I can click on this Add Connection button. As part of the source application drop down menu, you can pick the appropriate one if you want to automate from a Box, Dropbox, Google Drive folder, etc. Or here I’ll just simply select a file upload. So I’ll upload the source XML file to be converted and then place that in the destination application of “Box.” Well, place that CSV file within one of my Box folders here, and I’ll click on the Next button. As part of the connection definition, you can see there’s a default name and description, I’ll just modify the name slightly to have an XML to CSV conversion. I’ll choose to change my data format as Yes – so I want to convert -and you can see up above it will change this to four easy steps, and that’s all that’s needed to define the connection, and I’ll click on the Save and Next button. As part of the source configuration, the event will be on file upload. So when I manually upload a file, I’ll choose the source layout of XML, I’ll define a new source file layout, so I’ll click this “+” icon, and give this a meaningful name, so here this will just be my source XML layout, and you can choose to create a new layout and click on the “Next” button. Here you can choose to upload the actual XML file that you want to be converted, or some templated file or XSD file, so once I click on browse, I’ll click on this account file .XML, so here I’ll just show it to you quickly. So just a simple XML file with a single record, but this can be a complex hierarchy, or multiple record XML file that you may be dealing with. So here you can simply upload it as I’m doing here, you can see the name has been provided in the path and click on “Save,” and you can choose to save it as a template, so you or other users within your company can reuse it later. And you can click on “Save and Next.” That brings you to the destination configuration, and the first thing for this Box destination configuration is to provide Box account information. So here you can click on this “+” icon to create a new account, and then you can give a meaningful name, so this can be your business Box account. So this can be a user or group type Box account, so here I’ll just give the appropriate name and click the “Continue” button, and here it brings up a popup window, so you’re actually just logging into your Box account. So here I’m just logging onto my Box account, I’m going to click “Authorize,” and you can see you’re just granting access to Box to allow Adeptia Connect to read and write all files and folders, and then once you do that, you can see that’s all that’s needed to tie your Box account to be able to communicate with Adeptia Connect and do this file transfer and conversion. Here the data should be delivered, so we’re creating a new file in the Box account, and then the specific file you want to place it in, you can click on this “Browse” button. And here you’ll bring up the folders within your Box account. This is the CSV files folder that I want to load the output files that are being translated into CSV from XML. Similar to the source layout, I’ll create a new target file layout by clicking on that “+” icon, and give an appropriate name. In this case, this’ll just be my target CSV layout. I’ll select a new layout, I’ll keep that selected, and click “Next.” Here as part of the target CSV layout, I want a file header present in the output file. So I’ll select that. For CSV files, the comma and the new line are already selected for you, so no need to select anything else from the dropdown menu, and here we can upload a sample file. I have a sample file already created, so you can actually have a templated CSV layout file with all the field names, comma separated to be able to upload so if I click on “Browse,” navigate to the CSV layout I just showed, you can see down below the field definition is automatically populated for you, but you do have the option to define this manually. So you can define all of these column and field definition names, as well as have additional information of inserting more columns, modifying the name or the attribute that’s associated with any one of those fields, but here I’ll just use what was uploaded from that sample file and click “Save.” And that’s all that’s needed to be able to save this as a template, similar to the source layout, and now we’re complete with our destination configuration. And now the final step, we define the CSV layout that we want to map to, so if I click on “Save and Next,” it brings us to the field mapping, and our final step, so here this will be our XML to CSV conversion map, or giving it a meaningful name, and if you scroll down below, you can see the source layout from the XML file is in the left-hand column, the destination layout of your CSV file that you want to create and convert to will be in the middle under the destination column, and all the mapping rules that are applied based off of XML to CSV will be populated in the right column. So if the hierarchy and element names are similar, you can actually utilize this “Auto Map” button. So if I click on this “Auto Map” button, you can confirm to match map matching source and target field, I click yes, if there’s anything that’s overwriting based off of previous mappings, you can select “Yes” or “No.” Here I’ll just say yeah, overwrite the record mapping, you can see all the mappings from source to destinations have been applied via the “Auto Map,” and there’s just a few that need some one-to-one mapping. So to map any source element, you can click on it, and you can see it’s highlighted, and here I’m going to map this to address one, this “Addr1.” And once I click and select this, you can see the mapping rule is now populated. So for example, I can map region, I’m clicking region, selecting state to map to, and I can choose the same thing for date of birth and DOB. You can also create expressions for complex mapping rules, so for example, “Addr2” has nothing mapped to it, but maybe I want to utilize address line one, so I map that over, you can see it’s mapped, and also “City,” another source element. I’ll click and select. And now you can see I have two elements mapped to the single “Addr2” element, I can define an expression for this, so I can click on the “Define Expression” button, you can see the Math, String, Date functions are available on the left-hand side, as well as conditional when-if conditions, along with operators – Boolean operators – to utilize in any of your mapping rules. So here maybe I want to concatenate these two source elements, so I can click on the “Concat” function, it’s brought up above, and then I am simply selecting where I want to place the source elements with my mouse cursor, and then I’m clicking on the source element, and it’s brought up in the mapping rule, and here I’ll click on “City,” and this is where you can utilize these mapping functions to build complex expressions as needed. But here I’ll just keep it simple for now and click on “Save.” But here, once you complete the mapping, you can actually test the mapping out by clicking this data view. So if I click on the data view, you can see you have an “Upload Data File” button that you can navigate to your source file, upload that file, and then click on the “Test Mapping” button. Once you test out the mapping and have all your mapping rules defined as needed, we can go ahead and click on “Save and Exit.” You can choose to turn it on, so you can confirm to turn on the connection. You can see the newly created connection within your connection management page, it’s already on, and you can click on this arrow icon, choose “Send File,” navigate to your source XML file and select it, and once you upload that, you’re brought to the dashboard page here, and I can do a refresh, and you can see that this XML to CSV conversion connection has successfully run with the checkmark. You can click on the name to see the details, and click on any one of these icons to see the source, the processing within the Adeptia Connect, and then the target destination of Box or your appropriate application. I can log into my Box account and navigate to the folder where I chose the CSV files folder. You can see this account file has been generated, so I can download that. And then view that to make sure the XML has been correctly converted to CSV, and you can see there’s a single record that was converted to CSV. So again, any file, whether it’s single record or multiple record file, you can utilize this same connection as long as it’s the same format, both XML and CSV, that you want to use for this conversion. And that completes the XML to CSV conversion process. Remember, you can do 1,000 things with Adeptia Connect. If you want to find out how to reduce integration projects from weeks to minutes, or have questions, call us at +1 312-229-1727. Happy converting, thanks for joining me, and have a great day.