Roy Ashbrook

let go or be dragged
posts - 196, comments - 29, trackbacks - 8

Wednesday, September 10, 2014

Simple RIAK and C# Example

 

Install It:

  1. Have a linux box somewhere. i spun up a new centos 7.0 droplet on digital ocean for 5 bucks and turned it off when i was done with it.
  2. http://docs.basho.com/riak/latest/ops/building/installing/rhel-centos/
    1. Basically, I logged into my new machine and ran:
    2. sudo yum install http://yum.basho.com/gpg/basho-release-5-1.noarch.rpm
    3. sudo yum install riak
  3. If you want this to be available outside of your machine, you need to modify the ip address riak is bound to.
    1. If you were logged in as root, go to the /etc/riak folder and edit the app.config
    2. Modify the "http" area to have your public ip address as well as the 'PB' area up top.
  4. Run "riak start" to start up riak
  5. Check http://<your ip>:8098/riak/status should return some json

 

Use it:

  1. Open LINQpad (or whatever you want)
  2. NuGet search for riak
  3. Install CorrugatedIron
  4. Create a app.config file that is something like this:
    1. <configuration>
      <configSections>
          <section name="riakConfig" type="CorrugatedIron.Config.RiakClusterConfiguration, CorrugatedIron"/>
      </configSections>
      <riakConfig nodePollTime="5000" defaultRetryWaitTime="200" defaultRetryCount="3">
          <nodes>
              <node name="mynodename" hostAddress="actualnodeaddress" pbcPort="8087" restPort="8098" poolSize="0" />
          </nodes>
      </riakConfig>
      </configuration>
    2. I think this file can be named anything really, I actually called it riak.config and included a path down below.
  5. try some c# code like this:
    1. void Main()
      {
      //config file location
      var fl = @"path to my app.config";
      //name of the config section in the config file above
      var cs = "riakConfig";
      //name of our riak bucket to store things in
      var b = "mybucket";
      //connect to the cluster using our config file
      var clst = RiakCluster.FromConfig(cs,fl);
      //create a client
      var clnt = clst.CreateClient();
      //create an object to store
      var kvp = new KeyValuePair<string,string>("0","zero");
      //put it in a bucket in riak, key it, and drop the object in there
      var o = new RiakObject(b,kvp.Key, kvp);
      //put the object in riak
      clnt.Put(o);
      //go get the object using the same key as above
      var r = clnt.Get(b,kvp.Key);
      //dump the value we got back
      r.Value.GetObject<KeyValuePair<string,string>>().Dump();
      }

posted @ Wednesday, September 10, 2014 12:49 AM | Feedback (0) |

Friday, July 11, 2014

Pulling a Sharepoint 2007 list into Excel as Raw XML

I recently had a need to mash-up some data from a SharePoint 2007 list in an Excel document I was working on. I already knew that I could work with SharePoint 2007 data in Excel by using the following instructions from Microsoft:

  1. Do one the following on a SharePoint site:
Windows SharePoint Services 3.0
  1. If your list is not already open, click its name on the Quick Launch. If the name of your list doesn't appear, click View All Site Content, and then click the name of your list.
  2. On the Actions menu Actions menu, click Export to Spreadsheet.
  3. If you are prompted to confirm the operation, click OK.
Windows SharePoint Services 2.0
  1. If your list is not already open, click Documents and Lists, and then click the name of your list.
  2. On the page that displays the list, under Actions, click Export to spreadsheet.
  1. In the File Download dialog box, click Open.
  2. If you are prompted whether to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.
  3. Do one of the following:
  • If no workbook is open, Excel creates a new blank workbook and inserts the data as a table on a new worksheet.
  • If a workbook is open, do the following in the Import Data dialog box that appears:
    1. Under Select how you want to view this data in your workbook, click Table, PivotTable Report, orPivotChart and PivotTable Report.
    2. Under Where do you want to put the data, click Existing worksheet, New worksheet, or New workbook.

If you click Existing worksheet, click the cell where you want to place the upper-left corner of the list.

  1. Click OK.

http://office.microsoft.com/en-us/excel-help/import-data-from-a-sharepoint-list-HA010131473.aspx

 

That’s all very well, but in my case I had an existing spreadsheet with quite a few tables and an existing excel model. I didn’t want to go through these gyrations. Isn’t there some way to just get the data right off the list as a web page?

Yes. Enter the “Import XML data” feature in Excel. MS Offers the following following guidance for getting XML data from a web service:

Import XML data from a Web service

To do the following procedure, you must have access to a server that is running pnSTS11. A default installation of pnSTS11 provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available from the Downloads on Microsoft Office Online.

  1. On the Data menu, point to Import External Data, and then click Import Data.
  2. Do one of the following:

ShowOpen an existing data source

ShowCreate and open a new data source connection

  1. Select one of the following options:
  • XML list in existing worksheet

The contents of the XML data file are imported into an XML list in the existing worksheet at the specified cell location.

  • XML list in new worksheet

The contents of the file are imported into an XML list in a new worksheet starting at cell A1.

  1. If the XML data file does not refer to a schema, then Excel will infer the schema from the XML data file.
  2. To control the behavior of XML data, such as data binding, format, and layout, click Properties which displays the XML Map properties dialog box. For example, existing data in a mapped range will be overwritten when you import data by default, but you can change this.

http://office.microsoft.com/en-us/excel-help/import-xml-data-HP001042351.aspx#BMimport_xml_data_from_a_web_service

 

But how to get the URL to call for the XML data? The following link holds the key:

http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&View={2}&XMLDATA=TRUE

You have to replace {0} with your site URL and the {1} with your GUID for the list ID on SharePoint and {2} with the view GUID. How to get these? Well the easiest way (I think) is to go to your list, then select the view you want. Click view dropdown and select “Modify this view.” This will open the link to the screen where you can modify that view. The URL in the browser should have a View and List value. I stripped the %7B and %7D off as those are the { and } respectively. You don’t need to use a view and can omit that completely if you want to connect directly to the list. But I found I normally wanted to just get a certain view or I wanted to create a special view just for this activity.

Regardless, you can now follow the auto prompts to get the data into a spreadsheet. You can also open the developer tab and dive into the XML itself (see the MS link above for more instructions) and drag and drop the fields to other locations. Now when you get the data it will put it wherever you bound it.

By default it will dump it into a table and will include the schema items which you don’t need. You can open the Developer tab, select “Source” in the XML section and then just deselect the ns2 : Schema element. It will stop syncing those fields and you’ll have to clean up your table to get rid of them. I was able to rename the columns etc as needed, but since this is a read only feed I was typically using it to calculate other columns so I frequently stick with the ows_* columns that are the defaults for the data element.

Screenshot of the XML section from Developer tab in Excel:

XML Refresh Data

 

Once I figured out I needed to find the XML import stuff for Excel, I was able to find lots of articles on it online to help. The following was my favorite and links to a lot of other ones.

http://blogs.msdn.com/b/kaevans/archive/2009/05/01/getting-xml-data-from-a-sharepoint-list-the-easy-way.aspx

posted @ Friday, July 11, 2014 3:39 PM | Feedback (0) |

Monday, June 30, 2014

Generating a Date Dimension table in C#

I use a table similar to this quite frequently for my own personal reporting and analytics on various things. I figured I would polish it slightly and publish it in case anyone else needed it. =)

posted @ Monday, June 30, 2014 8:35 AM | Feedback (0) | Filed Under [ DEV ]

Powered by: