Export CSV File to XML using Excel VBA

Export CSV File to XML using Excel VBA

CSV (Comma Separated Value)

Light weight and fast data storing method. The File can be opened in any text editor or Excel Application. CSV can be read/write using various programming languages including VBA. But Iterating each cell or each value is very time consuming as CSV mostly contains data in millions. Performing loop may lead to non responding status or may take long time.

XML

XML is a markup language similar to HTML. It stands for Extensible Markup Language and is a W3C recommended specification as a general purpose markup language.

Problem statement

You need to transform CSV data into XML for some application purpose which supports or reads/writes in XML format. Because reading/writing or quarrying from XML is very fast using XML DOM or other technologies.

Solution

I see few line code which will transform entire CSV to XML with well formatted tagging. Using ADODB we can achieve it in few lines and the transforming operation is super fast for millions of records.

Code example

Sub ExportCSVtoXML(strPath As String, strFile As String, strOutputFile As String)
	Const adOpenStatic = 3
	Const adLockOptimistic = 3
	Const adPersistXML = 1
	Dim rs
	Dim cn

	Set cn = CreateObject("ADODB.Connection")
	Set rs = CreateObject("ADODB.Recordset")

	'connection string for >=2007
	strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath _
			& ";Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""

	cn.Open strCon

	rs.Open "Select * from " & strFile, cn, adOpenStatic, adLockOptimistic

	If Not rs.EOF Then
		rs.MoveFirst
		rs.Save strOutputFile, adPersistXML
	End If
	rs.Close
	cn.Close
	Set rs = Nothing
	Set cn = Nothing
End Sub

Let’s understand purpose of the variables which we have used in above code:

  • strOutputFile: variable has the path for XML file in which we need to write CSV Data
  • strPath: is the path where CSV file is kept
  • strFile: the CSV file name

Next >> Box Plot Chart in Excel with 5 steps

Leave a Reply

Your email address will not be published.