Copy/Paste

One of the core tenets of Gemini is that the user should be able to import data from virtually any source. The blog post Importing data into Gemini talks about the steps involved in importing data from a typical relational source. Gemini supports all of the popular relational databases – SQL Server, Access, Oracle, Sybase, Informix, DB2, Teradata, etc. Gemini also supports importing from third party OLEDB, ODBC and ADO.NET providers. The blog post Data Feeds talks about importing data from Reporting Services reports and other ATOM based data feed providers. Look for even more data import options in the next CTP of Gemini. Remember that all of these data sources are refreshable, i.e. you can publish the Gemini workbook to SharePoint and schedule a recurring job to automatically refresh the data.

Despite all of the data source options available in Gemini, there will be times where a user sees some data in tabular form that is not accessible via  any data source connectivity API such as OLEDB, ODBC, ADO.NET, ATOM, etc. Suppose you are viewing a web page that contains an HTML table, for example, the exchange rates to/from US Dollars at https://moneycentral.msn.com/investor/market/exchangerates.aspx?selRegion=0&selCurrency=1.

In order to import this data into Gemini, you just need to highlight the table contents and copy it to the clipboard. Switching to the Gemini window, you will see that the “To New Table” button in the ribbon is now enabled. Gemini automatically recognizes that a tabular chunk of data has been copied to the clipboard. Pressing the button brings up the following dialog which shows a preview of the data that is about to be pasted. You can choose to use the first row in your data as the column names in the resulting Gemini table.

Press the OK button and you will see a new table in the Gemini window containing the data that you pasted. This is just like any other Gemini table – you can change the table name, the column names, add calculated columns, etc.

Now suppose you’re viewing the same exchange rates web page a day later and you would like your Gemini workbook to contain the latest exchange rates. Just copy the HTML table to the clipboard like before and select exchange rates table in the Gemini window. You will notice that in addition to the “To New Table” button, the “Paste Append” and “Paste Replace” buttons are also enabled. Press the “Paste Replace” button and it will bring up the following preview dialog. Press the OK button and the data in the exchange rates table is now refreshed.

You can also use the “Paste Append” option to append the data from the clipboard to the Gemini table without touching the existing rows. For both “Paste Replace” and “Paste Append”, Gemini will validate that the structure of the data in the clipboard matches that of the Gemini table. The number of columns and their data types must match. If you have renamed the columns in the Gemini table or added calculated columns, Gemini will still let you paste the data.

In summary, Copy/Paste is a quick and easy way to get data into Gemini. Most applications that deal with tabular data (Excel, Access, Internet Explorer, etc.) do support copying it into the clipboard in a tabular format, and that makes the data easily available for Gemini to paste into a table. Although Copy/Paste is temptingly easy, it is important to note that the data in the table is static, i.e. not refreshable. Hence Copy/Paste should only be used when there is no other refreshable data source option available. For example, it would be easy to Copy/Paste data out of a Reporting Services report, however the recommended way would be to use data feeds to allow automatic data refresh.