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