using the excel 10 lib to convert xml to csv
Hey Bernie:
Dim cn
Dim cmd
Dim strmin
Dim strmout
Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set strmin = CreateObject("ADODB.stream")
Set strmout = CreateObject("ADODB.stream")
cn.Provider = "SQLOLEDB"
cn.Open "server=;INITIAL CATALOG=yourdatatbase;Integrated Security=SSPI;"
Set cmd.ActiveConnection = cn
SQLxml = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
SQLxml = SQLxml & "<sql:query>"
SQLxml = SQLxml & " SELECT * FROM " & your tablename & " FOR XML AUTO,
Elements"
SQLxml = SQLxml & "</sql:query>"
SQLxml = SQLxml & "</ROOT>"
CMD.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
strmIn.Open
strmIn.WriteText SQLxml
strmIn.Position = 0
Set cmd.CommandStream = strmIn
strmOut.Open
strmOut.LineSeparator = -1
cmd.Properties("Output Stream").Value = strmOut
cmd.Properties("Output Encoding").Value = "UTF-8"
cmd.Execute , , 1024
strmOut.Position = 0
Dim MyXML
MyXML = "<?xml version='1.0' encoding='ISO-8859-1'?>" & vbcrlf
MyXML = MyXML & "<data>" & vbcrlf
MyStream=replace(strmOut.readtext, "<ROOT
xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">", "")
MyStream = Replace(MyStream, "><", ">" & vbCrLf & "<")
MyStream = Replace(MyStream, "</ROOT>", "</data>")
MyXML = MyXML & MyStream
Set FSO = CreateObject("Scripting.FileSystemObject")
Set txtstream = FSO.OpenTextFile("C:\.xml", 2, True, -2)
txtstream.Write MyXML
txtstream.Close
Set txtstream = Nothing
Set FSO = Nothing
Dim ex As New Excel.Application
ex.Workbooks.OpenXML "C:\xml.xml"
ex.Visible = True
Quote:
> I need some help converting an xml file to .csv using the excel 10
library.
> Please show me a quick view of the appropriate code in vb .net. Also, is
> there a way to convert a table directly from a sql server 2000 table to
> .csv? My guess is it has to be converted first to a .xml file.
> Thanks for your help.
> Bernie Yaeger
