We can also load Siebel Data & LOV using COM objects. It works like a charm. The obvious advantages are
1. LOVs can be directly exported out of Siebel into csv and copied into an excel. This excel houses a macro with COM object.
2. Once you build a COM excel, you can use it in different environments by changing the datasource.
3. It only takes few minutes to load LOVs in this process, and it can load up to 65k+ data at a time.
Siebel bookshelf has some documents on how to build COM objects. That will get you started.
There're two ways to approach COM. One way is to use an existing Siebel connection/Siebel database object instance to access the LOVs in BC layer. This is light-weight, but it requires an open session in Siebel, and I am not sure what happens if you keep two Siebel sessions (one Production and one Sandbox for ex.) open.
The other way is to create Siebel data connection via COM itself. This is bit resource-intensive and you need to use Siebel dlls in the macro. In other words, the local computer needs to have a Siebel installed. However, this is safer and very easy to do. Bookshelf discusses in details on how to create this.
To effectively use this macro, you can build separate functions to create siebel connection, read data from excel, and then disconnect from Siebel. In some of the macros I use, I actually keep an extra module to keep a count of inserts, updates and failures. The obvious advantage is that you can simply copy the stats from macro, and mail the client. Remember, no data is complete without stats!!
3.Notice how various modules are created to organize the code, and how connection credentials are parameterized
Siebel provides Application Deployment Manager (ADM) to load LOVs and similar items.
Application Deployment Manager (ADM) functionality available in Siebel is often used to reduce manual effort of moving various entities between environments.
ADM provided by Siebel can move them automatically as ease off from manual job of moving and tracking, ADM navigation Site Map > Application Deployment Manager