How to Convert CSV to XML in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Convert CSV to XML in Excel


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.

Convert CSV to XML in Excel


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.

Convert CSV to XML in Excel


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.

convert csv to xml in excel

 

  • Now, this will open the XML Source window, and there click on XML Maps.

Convert CSV to XML in Excel

  • Next, in the new XML Maps window, click on Add.

Convert CSV to XML in Excel

  • 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.

Convert CSV to XML in Excel

  • Then, in the new message box, click OK.

Convert CSV to XML in Excel

  • 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.

Convert CSV to XML in Excel

  • After that, in the new Export XML window, give the file a name and click on Export.

Convert CSV to XML in Excel

  • 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo