Import XML when extension is XLS

[CptTom]

I have a spreadsheet that after much consternation turns out to actually be an XML, not an XLS which the current extension is. There are over 25 fields, what is the best way to bulk insert it?

 

[Will192]

I would just do a xp_cmdshell and rename the file to an xls extension.

Live to Throw
Throw to Live

 

[LNBruno]

Hmmm...Tek Tips search function must not be working again?

For 2000:

http://www.tek-tips.com/threadminder.cfm?pid=961

For 2005:

http://www.tek-tips.com/viewthread.cfm?qid=1299341

< M!ke >
I am not a hamster and life is not a wheel.

 

[LNBruno]

Sorry about that; the thread for 2000 is

http://www.tek-tips.com/viewthread.cfm?qid=1314015

< M!ke >
I am not a hamster and life is not a wheel.

 

[AlexCuse]

XML Spreadsheet files have a lot of formatting stuff in them that you would probably need to parse out. If you are using SSIS, I know that in .net you can open the xml spreadsheet and save it as a standard xls.

I imagine you can somehow do this in vbScript as well, but it is probably not nearly as easy.

I don't think you will be able to bulk insert this, unless you convert it to a standard xls file. I rarely So you'll either need to use DTS/SSIS to change the file, then import or just open it and do a good old 'save as' before doing a normal bulk insert.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom