PDA

View Full Version : Excel persisted range to SQL Server 2005



stanl
01-20-2007, 11:04 AM
Bear with me on this. May not be advanced to you as you read, but is to me. [output files are in attached zip]

1. I can persist a specific range in an Excel file as XML with [for example]
Range("B19:G64").Value(xlRangeValueMSPersistXML) to a file which will contain an xml schema and can be treated as a normal recordset. However, even with row headers in the range, the persisted fields are referred to as col1...col2...col[n], but will reference an rs:field value [rngxml.xml in attached zip]

2. SQL Server 2005 supports the bulk load COM Object for xml files, so a sample code snippet would look like



Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.2.0")
oBL.ConnectionString = "provider=SQLOLEDB.1;server=(local)\VSdotNet;" & _
"database=test;Trusted_Connection=Yes;"
oBL.KeepIdentity = False

oBL.Execute "[xsd/schema file]", "[xml file]"


3. [this would be called an AAARRRGGGHH!!!] Bulk inserts require that a schema be included along with the xml file. Problem is - by definition a persisted XML file includes a schema [think of Pink Floyd -"if you don't eat your meat, how can you have any pudding, and how can you have any pudding, if you don't eat your meat?"]

4. I actually found a Google hit for SQL Bulk Inserts and persisted recordsets, but Page Cannot Be Found.

5. I did think 'Aha, send the schema as ""' - No, if you come they won't build it.

6. So, given the persisted xml rngxml.xml - bulk insert it into a table named Cars in an SQL Server 2005 Table, so that when you later issue "SELECT * FROM Cars;" using the SQLOLEDB Provider and re-persist that file as output.xml [in attached zip] - a fully qualified recordset with field names.

.... Oh and #6 implies not having to manually create a schema.

Stan

stanl
01-20-2007, 02:53 PM
Wrote the most amazing work around :clap: , but still would appreciate comments on the problem as stated. Stan

Ken Puls
01-20-2007, 08:49 PM
Hey Stan,

I can't help you with your issue, but I may be playing with SQL server and Excel together soon. I'd be curious to know your workaround, in case it becomes an issue for me.

Also have to say that this is the first time I've ever seen Pink Floyd lyrics used to explain a VBA problem! LOL!

stanl
01-21-2007, 04:31 AM
Also have to say that this is the first time I've ever seen Pink Floyd lyrics used to explain a VBA problem! LOL!

Normally I would use Firesign Theatre
How can you be in two places at once when you're not anywhere at all but perhaps not as recognizable.

Importing Excel into SQL Server via ADO is a straightforward SELECT INTO.. IN Statement. Persisted range data is a special circumstance. I had posted some general information about persistence in Excel under Potential KB Articles. Perhaps this would meet criteria for a specific knowledge base entry.