Excel to XML ? 1

[anandkuppa]

Hi

I have got an intranet site in my company and regularly we have some sales information which needs to be posted onto a database table. This sales information is available in Excel file. Currently, i just ask the sales team to send the XL file to me and i do a DTS transfer from this XL file to the SQL Server table. I would like to automate this process and let the user upload the excel file and later on "push" it to the SQL Server table. I was thinking that if there was a way of transforming the XL data into an XML file, I can then read the XML file into the database. The reason why i would like to convert it into an XML file is for the sales manager to review the XL file posted by the sales team before he sends it to the database.
Is there any way i can achieve this ? (XL spreadsheet to XML file).
Any help will be appreciated.

Thanks,
Anand

http://www.transputec.com

 

[JJR]

Hi,

If you use a MS programming language like VB, it can easily be done by accessing the excel file as a database using MS ActiveX Data Objects(ADO) and OLEDB and saving your data as an MSXML DomDocument object (using the Save method of the ADO Recordset object). With ADO you can also immediately insert the data in your SQL database.

Let me know if you need sample code.

Jordi Reineman

 

[anandkuppa]

Thanks JJR..

Would appreciate if you could send the code as it would serve as a good guide for me.


Thanks again

 

[lagay]

If you have Office XP, one of the file types in the Save As option is XML Spreadsheet. This will convert the Excel spreadsheet into an XML file.

 

[dandl]

I am embarking on a very similar project, could you send me the code aswell?

Thanks dandl
lcumberbatch@htwebb.co.uk

 

[JJR]

Hi all,

Not been here for a while....

Ok, you can open a excel sheet like it's a database and transform it to XML using the following VB code:

Dim oCon As ADODB.Connection
Dim sSql As String
Dim oRs As ADODB.Recordset
Dim oXml As MSXML2.DOMDocument40

Set oCon = New ADODB.Connection
oCon.Provider = "Microsoft.Jet.OLEDB.4.0"

'Don't forget the ; after the sheet location!
oCon.ConnectionString = "

Excel sheet location

;" & _
                            "Extended Properties=Excel 8.0;"
oCon.Open

Set oRs = New ADODB.Recordset
' Dont forget the $ after the sheet name!!
' Braces ([]) must be placed around the sheet name 
' First row in sheet must contain fieldnames!
sSql = "Select * from [

Sheet name

$]"
oRs.ActiveConnection = oCon
oRs.Open sSql, , adOpenDynamic, adLockOptimistic

Set oXml = New MSXML2.DOMDocument40
oRs.Save oXml, adPersistXML
oXml.Save "c:\ExcelToXML.xml"

Set oXml = Nothing
oRs.Close
Set oRs = Nothing
oCon.Close
Set oCon = Nothing

Good luck!

Jordi Reineman