How Strip Sync CSV Import Works (beta)

2010-03-24 20:00:00 -0400

Updated: 6/6/2012 This blog post is out of date, and the software referenced in it, Strip Sync, has been discontinued in favor of Strip for Windows and Strip for OS X. For more information on our data import format, please see this newer blog post.

As mentioned here previously, we’re on the cusp of starting the beta test of Strip Sync, our new desktop companion tool for Strip. Among the application’s features are CSV import and export. The intent of this article is to describe the format used on export and required on import. This format is subject to change, but it won’t be changing much. Any changes will be announced here on this blog and noted as updates at the end of this article.

The General Gist

Each row in the CSV corresponds to an Entry in your Strip database. There’s a field indicating what Category the Entry belongs in, the name of the Entry, and every other column is considered a Field. The import process creates a new Entry for each row in the CSV file after the header row. Note: Bulk updating is now supported on SSM, requires use of EntryID column. Bulk updating will be made available soon in Strip Sync for Windows (SSW).

This is what a sample import CSV might look like:

Entry,Category,Account,Email,Note,Password,Phone,PIN,show,Username,Website
Credit Card,Financial,3759 876613 21001,,"exp:12/12
CVV:3829",secret,1-800-123-4567,4,,mscott,http://mycreditcard.com
Insurance Policy,Financial,3759 876613 21001,,secret: name of your first pet? spot,secret,1-800-123-4567,4,,mscott,http://myinsurance.com
jordie laforge,trekkers,,,,,,,nextgeneration|deep space nine,,
kirk,trekkers,,,,,,,star trek,,
patrick stewart,trekkers,,,,,,,star trek|next\|generation|voyager,,
riker,trekkers,,,,,,,,,
Shopping Website,Personal,,mscott@mailinator.com,,secret,,,,,http://paypal.com

Header Row Required

Just like the subtitle says, a header row is currently required to describe the data in the spreadsheet you’re importing for Strip.

Header Specification

  • One column must be named “Entry”, and this is case-insensitive.
  • One column must be named “Category”, and this is also case-insensitive.
  • The name of every other column (for now) is considered the name of a Field.
  • No columns should be named “Guid”… Guid is no longer a restricted name.
  • One column may be named “EntryID” on SSM, making the row an update to an existing record.

When Strip Sync reads the header row of your import file, it looks up each Field name in your database to see if there’s already a label/type associated with it. If not, a Field with this label is created for you in your database, with the default mode set to “text”, and you can simply change this setting to URL or whatever you like by editing your labels in Strip.

When a row contains an EntryID, Strip Sync looks up the Entry in your database and replaces it’s name, category, and fields using the data in the rest of the row.

Bulk Updating

To do bulk updates via CSV import, you need to get the unique identifiers for your entries! Simply use the export feature of Strip Sync to export a CSV file containing all records in your database, with their EntryIDs.

Row Processing

During import, after the header row has been read, Strip Sync begins cranking through all the other rows, creating new Entries using the data in each row (bulk update via CSV import is not supported yet, but we plan to support that soon). Here’s how it works:

  • Strip Sync looks at the Category field and does a case-sensitive lookup to find a matching Category in your database. If no match is found, a new Category with this name is created.
  • The Entry column is used as the name of the new Entry, as indicated above.
  • For each additional column in the row:
    • If the column is empty, it is ignored
    • If the column is not empty, a Field is created on the Entry, with a type/label corresponding to the column’s header name.
    • Field columns may contain multiple values, separated by the ‘pipe’ character, ‘|’. If multiple values are detected, multiple Fields will be created on the Entry, labeled according to the column’s header name.
    • If your Field needs to contain a pipe character as part of the Field value, you may escape it with a backslash character (i.e. ‘\|’).
  • If an Entry or Category column is blank, the entire import will be rolled back, and an error message will display detailing the problem and the line number where the problem was found.

Our import and CSV processing is based on the scanning technique and EBNF outlined by Matt Gallagher to fully support properly escaped CSV data.

If one were to extend that EBNF definition to take into account our use of | to separate multiple field values, we think it would look like this:

file = [header lineSeparator] record {lineSeparator record}
header = name {separator name}
record = field {separator field}
name = field
field = escaped | nonEscaped
escaped = doubleQuote {innerField | separator | lineSeparator | twoDoubleQuotes} doubleQuote
nonEscaped = innerField
doubleQuote = '"'
twoDoubleQuotes = '""'
separator = ','
lineSeparator = ('\r' | '\n') {'\r' | '\n'}
innerField = textData { innerFieldSeparator | textData }
innerFieldSeparator = '|'
textData = {characters up to the next double quote character, un-escaped innerFieldseparator, separator string, or lineSeparator}

Obviously, commentary and corrections are welcome (as well as bug reports).

 

Tempo Updates: Tagging, Status, UI tweaks

2010-03-23 20:00:00 -0400


Last night I pushed a couple of updates out to our Tempo time tracking service that were a touch over-due:

  • Tweaked date field on time entry to fix text-obscuring problem
  • Tweaked order receipt for providing copies of past invoices
  • Improved navigation and visibility for Team → Status
  • Fixed display margins in module boxes site-wide
  • Adds a “add new user” link in project team settings for convenience
  • Allows meta-characters in tags, no longer restricted to [A-z0-9-_]

The tags thing was getting annoying, I will admit. It’s pretty useful to be able to, for instance, begin certain tags with a * character to mark them as project defaults.

The Team Status thing was a visibility issue. I think most administrators and managers of Tempo accounts were unaware that in addition to the basic Team view (a simple list of your users), there’s a Status view indicating who is working on what:

Tempo Status

Aaaaand it even has a handy mobile view that you could bookmark:

Tempo Status Mobile

If you notice any bugs, or there’s anything bothering you about the system, please get in touch.


In Search of a Pragmatic Programmer

2010-03-22 20:00:00 -0400


We’ve been really busy here at Zetetic lately and we’re looking for a new teammate to help us develop and support client systems and our own products. As a small and flexible software development consultancy, we’re mostly interested in personal qualities, not resume buzzwords:

  1. Natural or Artificial intelligence (robots welcome)
  2. Intense desire to learn new things & hack on technology
  3. An unflappably positive attitude
  4. Plays well with others

A background in one or more of the following goes a long way too!

  • Strength in at least one Object Oriented Programming Language (C#, Ruby, Java, Obj-c). Anything except VB.
  • Desire to “switch things up” and work on multiple programming languages
  • Experience with databases, relational SQL, or otherwise (mongo, couch, etc)
  • Security Technology (i.e LDAP authentication, Single Sign-on, PKI)
  • Linux and general networking
  • 2-5 years of software development experience
  • Located in Philadelphia, NYC, or Central NJ to allow collaborative work with our current team members

In short, we mostly care that you’re smart, love to hack, and get things done.

If this sounds like you, or someone you know, please reach out and let us know: sjlombardo@zetetic.net.


Strip Sync Screens

2010-03-19 20:00:00 -0400

Updated: 6/6/2012 This blog post is out of date, and the software referenced in it, Strip Sync, has been discontinued in favor of Strip for Windows and Strip for OS X.

I have a couple small things to fix still, but we’re planning to start the beta for Strip Sync next week. We’ve been getting a lot of “where is it!?!” emails, so we thought it’d be a good idea to post some screens and show you that it’s not vaporware. I’m heading development on the Mac side, so I’ll show you screens from Mac OS X. You’ll get a look at the .NET version for Windows soon.

When you start up the Strip Sync application, it is locked, and requires you to enter your access password:

Login

On first-time start-up, it will ask you to set a password for the local database replica. I should note that, at least for the time being, the password on your desktop database must match that of your iPhone database. Still gotta work out nicely paginated printing, and to be honest I’m not sure it’s necessary. Will probably be left out on initial release, as anybody can export to CSV and print that, and stick it in a safe.

Once you’re in, you’ll see the main utility window:

Initial Database

One of the key features here, Import, can be fired off pretty easily to import Strip CSV data (more on that in a later post, I have an EBNF that I need to clean up for programmers):

Import-csv

Import Completed

Then, you fire up the iPhone version, select the Sync tab, browse for your desktop on the local network, and begin the sync operation:

Iphone Sync

Those other buttons on the select switch indicate performing a restore from your desktop, and performing an authoritative override of what’s on the desktop. Once we’ve chosen the desktop we wish to sync with, we go for it (click to embiggen):

Completed Sync

Here we can see that a new category called “trekkers” has been added:

New Catagory

And we can also see that the data import supports multiple values for field types on an entry:

Multiple Fields

The reason this worked is because they were delimited by a | character in the CSV data shown above. It can be escaped with a backspace. Like I said above, more on the import format later. Obviously, my Trek knowledge above is bogus, just needed some data.


Ruby Nation / Best Practices

2010-03-18 20:00:00 -0400


I’ll be attending this year’s Ruby Nation conference in Reston, VA April 9-10, and I’m quite looking forward to it. The list of speakers and talks is absolutely fantastic, and includes the excellent Nick Sieger, whom I had the pleasure of first meeting at Ruby Fringe in 2008. There are still a couple of open registration seats left if you haven’t signed up yet. If any of the Ruby heads out there want to meet up, I’ll be in town from the evening of April 8th to the morning of April 11th. You can find me on Twitter as @billymeltdown.

I’ve been spending a little time revisiting some of the core concepts and industry practices with Ruby, and recently Bret Morgan of DBL Systems pointed me in the direction of Gregory Brown’s newly published book, Ruby Best Practices. The first chapter alone is excellent, a kind of sermon for Test-Driven Development, and I highly recommend it.