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.

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.

A CSV File Opened in Notepad

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.

Importing a CSV File in 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.

A CSV File Imported in an Excel Sheet



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.

Opening the XML Source Task Pane in Excel

XML Source task pane appears.

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

Opening the XML Source Task Pane in Excel

XML Maps dialog box appears.

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

Adding an XML Schema to 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.

Importing an XML Schema Using Select XML Source Window

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.

An XML Schema Added to the XML Maps Dialog Box

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.

Mapping XML Schema with Excel Dataset

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.

Mapping XML Schema with Excel Dataset

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.

Convert CSV to XML by Exporting Excel Table as XML File

Export XML window appears.

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

Step_4: Then hit the Export button.

Convert CSV to XML by Exporting Excel Table as XML File

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

Convert CSV to XML by Exporting Excel Table as XML File

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

Convert CSV to XML by Exporting Excel Table as XML File

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

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *