{"id":3495,"date":"2016-05-16T08:13:16","date_gmt":"2016-05-16T07:13:16","guid":{"rendered":"https:\/\/www.infolaw.co.uk\/newsletter\/?p=3495"},"modified":"2016-05-19T12:07:18","modified_gmt":"2016-05-19T11:07:18","slug":"working-with-data","status":"publish","type":"post","link":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/","title":{"rendered":"Working with data"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg\" title=\"Egyptian spreadsheet? by Karen Green\" alt=\"Egyptian spreadsheet?\"\/><\/p>\n<p>Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program <a href=\"https:\/\/en.wikipedia.org\/wiki\/VisiCalc\">VisiCalc<\/a> (a contraction of \u201cvisible calculator\u201d), which turned the microcomputer from a hobby for computer enthusiasts into a serious business tool and prompted IBM to launch their PC.<\/p>\n<p>VisiCalc\u2019s mantle was soon wrested from it by the superior Lotus 1-2-3 which reigned supreme on the PC until the advent of Windows when Microsoft\u2019s Excel started to outsell it. Today there can be no organisation which does not rely heavily on Excel or an equivalent spreadsheet application for its planning, budgeting and forecasting, accounting \u2026 and many other functions.<\/p>\n<h3>What are spreadsheets for?<\/h3>\n<p>Silly question! They\u2019re for crunching numbers and presenting financial data, right? If you work with numbers, then, yes, spreadsheets were designed specifically for you: they were initially developed as computerised simulations of paper accounting worksheets. But if you regard spreadsheets as number crunchers only, then you overlook their immense value for organising just about any data.<\/p>\n<p>We all keep lists. Some of these are simple, but most include items that benefit from categorisation, annotation and ordering in various ways.\u00a0 Whether we recognise it or not, we are daily organising our tasks with tables of data; best to do it efficiently with <a href=\"https:\/\/en.wikipedia.org\/wiki\/Spreadsheet\">spreadsheets<\/a>. Spreadsheets are now used extensively in any context where tabular lists are built, sorted and shared.<\/p>\n<p>By way of example, following is a list of the spreadsheets that I use on a regular basis (in deliberately random order):<\/p>\n<ul>\n<li>profit and loss projections*<\/li>\n<li>address lists<\/li>\n<li>legislation amendment lists<\/li>\n<li>publication contents planners<\/li>\n<li>email lists<\/li>\n<li>subscriber lists<\/li>\n<li>production schedules<\/li>\n<li>sales reports*<\/li>\n<li>personal tax calculations*<\/li>\n<li>publication costings*<\/li>\n<li>case data<\/li>\n<\/ul>\n<p>You\u2019ll note that only those marked * are based on financial data.<\/p>\n<p>As a publisher I obviously have some specific needs that are met with some of these, but most of my other needs are generic and the range of applications for which you might find spreadsheets useful will be similar.<\/p>\n<h3>Doing stuff with your data<\/h3>\n<p>If your spreadsheet is of financial information then probably the spreadsheet is the end product. You plug in the variable information, everything is calculated and laid out in neat rows and columns and you have your desired result. Maybe you need to tweak a few things to produce a pretty print version; or maybe you\u2019ll use a graphing function to produce a nice bar- or pi-chart.<\/p>\n<h4>Mail merge and more<\/h4>\n<p>For most other applications we use spreadsheets as a convenient data store and need to do something more to get our result.<\/p>\n<p>A very common application, built into word processors, is mail merge which is designed to read data from your address file data source (spreadsheet or other database), select items matching the filters you set, sort them in a particular order and merge them into your word processor templates for mailings, envelopes or labels.<\/p>\n<p>What\u2019s worth noting here is that you can use \u201cmail\u201d merge to merge <em>any<\/em> data with <em>any<\/em> template, so, for example, you can produce a staff directory, or record cards for your library, or whatever.<\/p>\n<h4>Working with data on the web<\/h4>\n<p>But isn\u2019t this talk of mail merge rather dated? Typically we are nowadays concerned more with email rather than with snail mail communications and with transferring data to and from web-based applications.<\/p>\n<p>Most good web applications will accept data directly from Excel or other native spreadsheet format. You can happily copy and paste from your spreadsheets to some web application editors when in wysiwyg mode. You can also upload data directly from your spreadsheets to online databases and download therefrom in spreadsheet format (typically Excel\u2019s <strong>xls<\/strong> format). So, for example, you can download Google AdWords reports for further manipulation and analysis.<\/p>\n<h4>Data interchange formats<\/h4>\n<p>Working directly with spreadsheet data is often not optimal. What are the other file format options?<\/p>\n<p><strong>csv<\/strong> (comma separated values) is a long-established format for data interchange. Spreadsheet applications will save as csv and import from csv or open a csv file directly; but in csv, non-numeric values need to be enclosed in \u201cquote marks\u201d which makes it an awkward format to work on directly if needed.<\/p>\n<p><strong>tsv<\/strong> (tab separated values) has the huge advantage that you can copy and paste directly between a tsv text file and your spreadsheet and is much easier to edit.<\/p>\n<p>You\u2019ll need a good (plain) text editor to work efficiently (for comments and suggestions, see my article \u201c<a href=\"http:\/\/bit.ly\/1OdT9iG\">Working with Documents<\/a>\u201d in the January issue).<\/p>\n<h4>A simple worked example \u2013 email lists<\/h4>\n<p>Let\u2019s say you have an email list as two columns in a spreadsheet in the form Firstname Lastname, Email; you want to change this to the three-column format Email, Firstname, Lastname.<\/p>\n<p>1) Swap the two columns in the spreadsheet.<\/p>\n<p>2) Copy the Firstname Lastname column data to your text editor.<\/p>\n<p>3) Using Find and Replace, change the spaces to tabs (^t).<\/p>\n<p>4) Copy the block of data back to the spreadsheet.<\/p>\n<p>Voila!<\/p>\n<p>Of course you\u2019ll have to adjust for spaces in names like da Silva etc, but you get the principle.<\/p>\n<p><em>Nick Holmes is Editor of the Newsletter. Email <a href=\"mailto:nickholmes@infolaw.co.uk\">nickholmes@infolaw.co.uk<\/a>. Twitter @<a href=\"https:\/\/twitter.com\/nickholmes\">nickholmes<\/a>.<\/em><\/p>\n<p>Image: <a href=\"https:\/\/www.flickr.com\/photos\/klg19\/4415755891\/\">By Karen Green<\/a> on Flickr.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which turned the microcomputer from a hobby for computer enthusiasts into a serious business tool and prompted IBM to launch their PC. VisiCalc\u2019s mantle was soon wrested from it by the superior Lotus [&hellip;]<\/p>\n","protected":false},"author":149,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[105,60],"tags":[],"class_list":["post-3495","post","type-post","status-publish","format-standard","hentry","category-internet-skills","category-practice-tools"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Working with data - Internet for Lawyers Newsletter<\/title>\r\n<meta name=\"description\" content=\"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_GB\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Working with data - Internet for Lawyers Newsletter\" \/>\r\n<meta property=\"og:description\" content=\"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/\" \/>\r\n<meta property=\"og:site_name\" content=\"Internet for Lawyers Newsletter\" \/>\r\n<meta property=\"article:published_time\" content=\"2016-05-16T07:13:16+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2016-05-19T11:07:18+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg\" \/>\r\n<meta name=\"author\" content=\"Nick Holmes\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@nickholmes\" \/>\r\n<meta name=\"twitter:site\" content=\"@nickholmes\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nick Holmes\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/\",\"url\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/\",\"name\":\"Working with data - Internet for Lawyers Newsletter\",\"isPartOf\":{\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg\",\"datePublished\":\"2016-05-16T07:13:16+00:00\",\"dateModified\":\"2016-05-19T11:07:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/cc32805b4a8fda14e34ede9a76474263\"},\"description\":\"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which\",\"breadcrumb\":{\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage\",\"url\":\"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg\",\"contentUrl\":\"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg\",\"width\":640,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.infolaw.co.uk\/newsletter\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working with data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/#website\",\"url\":\"https:\/\/www.infolaw.co.uk\/newsletter\/\",\"name\":\"Internet for Lawyers Newsletter\",\"description\":\"Edited by Nick Holmes\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.infolaw.co.uk\/newsletter\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/cc32805b4a8fda14e34ede9a76474263\",\"name\":\"Nick Holmes\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f5934805c5d6f0496dda81fe2290fdaefdab50842bc2c69868eff99ab0b15cc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f5934805c5d6f0496dda81fe2290fdaefdab50842bc2c69868eff99ab0b15cc?s=96&d=mm&r=g\",\"caption\":\"Nick Holmes\"},\"description\":\"Nick Holmes is Editor of this Newsletter. He is a publishing consultant specialising in the legal sector and is Managing Director of legal web services company infolaw Limited. Email nickholmes@infolaw.co.uk. Twitter @nickholmes.\",\"sameAs\":[\"http:\/\/www.binarylaw.co.uk\"],\"url\":\"https:\/\/www.infolaw.co.uk\/newsletter\/author\/nickholmes\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Working with data - Internet for Lawyers Newsletter","description":"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/","og_locale":"en_GB","og_type":"article","og_title":"Working with data - Internet for Lawyers Newsletter","og_description":"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which","og_url":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/","og_site_name":"Internet for Lawyers Newsletter","article_published_time":"2016-05-16T07:13:16+00:00","article_modified_time":"2016-05-19T11:07:18+00:00","og_image":[{"url":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg","type":"","width":"","height":""}],"author":"Nick Holmes","twitter_card":"summary_large_image","twitter_creator":"@nickholmes","twitter_site":"@nickholmes","twitter_misc":{"Written by":"Nick Holmes","Estimated reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/","url":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/","name":"Working with data - Internet for Lawyers Newsletter","isPartOf":{"@id":"https:\/\/www.infolaw.co.uk\/newsletter\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage"},"image":{"@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage"},"thumbnailUrl":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg","datePublished":"2016-05-16T07:13:16+00:00","dateModified":"2016-05-19T11:07:18+00:00","author":{"@id":"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/cc32805b4a8fda14e34ede9a76474263"},"description":"Back in 1979 Apple published what is regarded as the first \u201ckiller app\u201d, the spreadsheet program VisiCalc (a contraction of \u201cvisible calculator\u201d), which","breadcrumb":{"@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/"]}]},{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#primaryimage","url":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg","contentUrl":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-content\/uploads\/2016\/05\/Egyptian-spreadsheet.jpg","width":640,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/2016\/05\/working-with-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.infolaw.co.uk\/newsletter\/"},{"@type":"ListItem","position":2,"name":"Working with data"}]},{"@type":"WebSite","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/#website","url":"https:\/\/www.infolaw.co.uk\/newsletter\/","name":"Internet for Lawyers Newsletter","description":"Edited by Nick Holmes","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.infolaw.co.uk\/newsletter\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/cc32805b4a8fda14e34ede9a76474263","name":"Nick Holmes","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.infolaw.co.uk\/newsletter\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f5934805c5d6f0496dda81fe2290fdaefdab50842bc2c69868eff99ab0b15cc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f5934805c5d6f0496dda81fe2290fdaefdab50842bc2c69868eff99ab0b15cc?s=96&d=mm&r=g","caption":"Nick Holmes"},"description":"Nick Holmes is Editor of this Newsletter. He is a publishing consultant specialising in the legal sector and is Managing Director of legal web services company infolaw Limited. Email nickholmes@infolaw.co.uk. Twitter @nickholmes.","sameAs":["http:\/\/www.binarylaw.co.uk"],"url":"https:\/\/www.infolaw.co.uk\/newsletter\/author\/nickholmes\/"}]}},"_links":{"self":[{"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/posts\/3495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/users\/149"}],"replies":[{"embeddable":true,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/comments?post=3495"}],"version-history":[{"count":7,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/posts\/3495\/revisions"}],"predecessor-version":[{"id":3526,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/posts\/3495\/revisions\/3526"}],"wp:attachment":[{"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/media?parent=3495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/categories?post=3495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.infolaw.co.uk\/newsletter\/wp-json\/wp\/v2\/tags?post=3495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}