In this tutorial, I am going to show you step-by-step procedures to convert CSV to XML in Excel. CSV files store data in a comma-separated values format. Whereas XML stores data in a more complex format than a table. Thus, converting from CSV to XML is relatively straightforward since XML can handle more complex data structures.
Convert CSV to XML in Excel: Step-by-Step Procedure
The dataset we are using for this tutorial is relatively simple. It has only two columns namely the Name of students and their Locations. But you can use as many columns as you required and the steps would still be the same. As our final data will be in XML format, we can use any size of CSV file to convert.
Step 1: Opening CSV File in Excel
Once you have the CSV file ready, then you just need to open it using Excel to convert that CSV file to an XML file. Also, make sure the data is clean and the file is free from any corruption.
- Firstly, open the CSV file in Excel.
- Now, note down the heading of each column. This will be required to create the XMLÂ template.
- Here, for my dataset, they are the Name and Location columns.
Step 2: Creating XML Template in Notepad
This is one of the most important steps to convert a CSV file to an XML file in Excel. You need to create a simple XML template for your unique dataset. You can use any text editor you like. Here, I am using Notepad for windows 10.
- Now, create a new template file with the extension .xml and open the file using any text editor.
- Then, type the following XML code in the text editor.
- Note that, you have to replace the words Name and Location with your own column headers.
- Also, you can change the words location_data and location to suit your dataset.
Step 3: Importing Template into Excel
After you have created the template for your dataset, you need to bring it inside Excel to convert it to an XML map from CSV.
- To begin with, go to the Developer tab and under the XML section click on Source.
- Now, this will open the XML Source window, and there click on XML Maps.
- Next, in the new XML Maps window, click on Add.
- Here, in the Select XML Source window, select All Files from the File Types drop-down.
- Now, select the XML template file that you created and click Open.
- Then, in the new message box, click OK.
- Next, in the new window, you will see that Excel has added the template file as an XML map.
- Here, press OK.
- As a result, you will see the column headers appear as a tree structure in the window XML Source.
- Now, from the XML Source window click and drag the Name section and drop it in cell B4 where the column header lies.
- Similarly, drag and drop the Location section to cell C4.
- As a result, Excel will map the dataset and format it as shown below.
Step 4: Exporting File in XML Format
In this final step, we will export the XML file and save it at a suitable location. Note that, Excel will change the format of the CSV data that we used to convert.
- Next, navigate to the Developer tab, and from the XML section click on Export.
- After that, in the new Export XML window, give the file a name and click on Export.
- Consequently, Excel will export and save the file in XML format and you can open the file to check if the operation was successful.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you were able to follow the steps above to convert CSV to XML in Excel. If you get stuck at any step, I would suggest going through the tutorial a few more times. You can take these steps and use them for datasets with a large number of columns. If you have any queries, please let me know in the comments.
