I’ve found that on many occasions I want to take a quick look at some data without going through the process of extracting a data set or querying a database. Maybe a colleague has sent me a spreadsheet, and I just want to quickly visualize the data set. Maybe I just want to take a quick look at my progress working on an analysis and see if the data are telling me what I think they are.
The ability to paste data directly into JMP or Tableau is probably one of my favorite underestimated features. Here’s how I do it:
I pulled 647,899 records of daily user login totals from application logs. I copied the records directly from my query results. In a blank Tableau workbook, it’s as simple as Cmd-V/Ctrl-V to paste the data. You can also find the option in the Data menu.
Loading these records into Tableau on my Macbook Pro took about 20 seconds. You can see from the first screenshot that the pasted data has been added a data source with the prefix “Clipboard_” and a timestamp.
The one strike here is that Tableau guesses at how I want to look at the data — I wish it wouldn’t do that. To get a clean workbook you can either (1) create a new sheet and delete the first one, or (2) choose “Clear Shelf” from the Row/Column drop down and “Remove” from the Mark drop down.
After clearing the sheet, I added a calculated field to format the login values as a date and created the chart below in about 1 - 2 minutes.
For this analysis, the time of day is important for some questions I’m trying to answer. I’ve now created a calculated field to grab the hour from the LoginDate I brought over (I intentionally created the date + hour value in one field for this example). A couple of quick changes, and now I have a chart that shows me I haven’t yet accounted for the time zone in my analysis. My users are logging in from the United States, but my logging uses the GETUTCDATE() function in SQL Server. I can now trash the workbook without saving and go back to my database queries.
If I wanted to save that work, Tableau will save the clipboard data as a Datasource closing the workbook.
Pasting the same data set into JMP took about 8 seconds. Just for kicks, I ran a separate query from the source database that returned 2,237,152 records. I wanted to push my laptop a bit and also show an example that won’t fit in Excel.
One important consideration when pasting into JMP is that there is a separate option to Paste with Column Names (On the Mac, this is Shift-Cmd-V). You want to choose this if you bothered to copy with headers.
Pasting these 2.2mm records into JMP took about 18 seconds. You can see that JMP will guess at data types just like Tableau.
A new column for the LoginHour and a couple of clicks later, and I’ve honed in again on my time zone issue.
Two tools, one quick method for quick exploration of data.