A Complete Guide to Convert a CSV File to an XML File in Excel

CSV and XML both are used to store data. A CSV file stores data by separating them using commas from each other. On the other hand, an XML file uses tags to store data within it. Sometimes, you may need to convert your CSV file into an XML file. Well, it’s quite a straightforward procedure to do that. Here, you will learn to convert a CSV file to an XML file in Excel. So without any delay, let’s jump right into it.

Table of Contents

About the Setup

A CSV (Comma-Separated Values) file stores information by separating them using commas. CSV files can be opened in spreadsheet programs, such as Microsoft Excel or Google Sheets. You can identify a CSV file by looking at its extension which is (.CSV).

However, I’ve taken a CSV file named Employee Details.CSV, as shown in the picture below. Now I’m going to show you to convert this CSV file to an XML file using Microsoft Excel.

First Step: Open Your CSV File in Excel

First, you need to open the CSV file in Microsoft Excel.

To open a CSV file,

Just double-click on the CSV file.

Also, you can right-click on the CSV file and then go to Open with ⋙ Excel to import the CSV file into Microsoft Excel.

After importing the CSV file, it will look like the following picture. Here, you will notice that each value separated by a comma in a CSV file has been inserted into different columns in Excel.

2 VBA Codes to Open an XML File in Excel

Second Step: Create an XML Schema

To convert your CSV file into an XML file, you need to specify how your data will be stored in an XML file. For this purpose, you need to create an XML schema. The XML schema is a template of an XML file where you specify how data will be converted from an Excel file to an XML file.

I’ve already published a detailed guide on creating an XML schema for Excel files. You can read it first, in case you don’t know how to create an XML schema for exporting Excel files as an XML file.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<record>
		<Name>Fronek</Name>
		<Country>Portugal</Country>
		<Age>52</Age>
	</record>
	<record>
		<Name>Janman</Name>
		<Country>China</Country>
		<Age>69</Age>
	</record>
</dataset>

Third Step: Import XML Schema Inside Excel

So far, I have imported my CSV file inside MS Excel and created an XML schema based on my Excel data.

Now it’s time to connect the XML schema with the Excel data. The process of connecting the XML schema with the Excel data is called mapping.

Now let me show you how to map an XML file with an Excel dataset.

Step_1: First, go to the Developer tab.

If you don’t have the Developer tab in the ribbon, read 2 ways to add the Developer tab in Excel.

Step_2: Then click on the Source command in the XML group.

XML Source task pane appears.

Step_3: Click the XML Maps button at the bottom of the XML Source task pane.

XML Maps dialog box appears.

Step_4: Click on the Add button in the XML Maps dialog box.

Step_5: Next, browse your folders to locate the XML schema file in the Select XML Source window.

Step_6: After selecting the XML schema, hit the Open button.

Then a rectangular dialog box will appear.

Step_7: Click OK on it.

Finally, you will see your XML schema in the XML Maps dialog box.

Step_8: Select the XML schema and hit the OK button.

Finally, you will see that the XML schema has been imported into the XML Source task pane as in the picture below.

Fourth Step: Map XML Schema with Excel Dataset

To connect the XML schema with the Excel dataset,

Step_1: Select the first column header name in the Excel dataset.

For this instance, the first column header is Name.

Step_2: After that, double-click on the same column header in the XML Source task pane.

In this case, after selecting the Name column header in the Excel sheet, double-click on the Name column header in the XML Source task pane.

If everything is alright, the first column in the Excel sheet will turn into a table. This means the initial process of mapping is successful.

Step_3: Now repeat the same procedure for all the rest of the columns.

When all the columns in the Excel sheet will turn into an Excel table, you can be sure that the mapping has been done correctly.

Thus, if you select any column header name in the XML Source task pane, the corresponding column in the Excel sheet will be automatically highlighted.

Fifth Step: Convert CSV to XML by Exporting Excel Table as XML File

Now it’s time to export the Excel Table as an XML file.

To do that,

Step_1: Go to the Developer tab again.

Step_2: Hit the Export button in the XML group.

Export XML window appears.

Step_3: Name your XML file in the File Name box.

Step_4: Then hit the Export button.

Finally, the Excel Table has been exported as an XML file. You can check this out in the folder where you’ve exported it.

Open the XML file in Notepad. You can see how the CSV file has been converted into an XML file now.

Conclusion

I’ve given a complete guide on converting a CSV file to an XML file in Microsoft Excel. I hope you’ve found this article really helpful. If you have any questions regarding converting a CSV file into an XML file in Excel, please let me know in the comment section below. Thanks.

Related Articles