Tuesday, December 21, 2010

Copy/Paste Rows in Advantage Data Architect

This is a guest post by Will Foster, our R&D intern this year, on a feature he implemented in ARC version 10.1



The Advantage Data Architect (ARC) now supports copy and pasting directly from the table browser! This functionality has been added in order to allow copy and pasting into and out of ARC. This means when you select one or more records in a table view (SQL Utility in next v10.1 public update) you will now be able to right click and “Copy Records” which will place all your data, including memo and blob field types, onto the system-wide clipboard. The data is placed onto the clipboard in CSV, HTML Table, and Tab delimited formats. This means you will be able to Paste directly into Microsoft Office products with some default formatting, and directly into an HTML formatted e-mail message. This framework also allows you to select data in Microsoft Excel or other spreadsheet software and paste directly into ARC, making adding data to a table as easy as a copy and a paste. You can also copy rows from a table and paste back into that same table (to duplicate rows, then tweak individual fields, for example).



Copying data directly from a table browser


Pasting directly to Microsoft Excel


Pasting directly into email client


Binary/BLOB Data

It should be noted that blob field types will be 64 bit encoded prior to being placed onto the clipboard. This means that if copying large quantities of blob data, this encoding may take a while, and will remain 64-bit encoded if pasted anywhere other than ARC. However, you can feel free to copy and paste blob data within ARC, retaining all the original integrity of the field.


Field Mapping

You can also copy and paste from two entirely different tables. This is accomplished via a mapping dialog. This allows you to modify the orientation of fields from the source table so that they match the destination table. If any conflicts occur you will be notified and allowed to retry with all the data still remaining on the clipboard. This dialog will also let you choose if you have a header row or not (useful when copying partial bits of data from Excel or the Web. This dialog also attempts to match based on field names, so if you have fields in a different order on the source table it will be no problem at all!



Field Mapping Dialog



Overall, the copy/paste functionality is a great addition to ARC and we believe it will make your data manipulation tasks even easier than before!


Rafick said...

Good idea & well done guys! Looking forward to using this feature.

Anonymous said...

I tried copying 534 rows x 14 columns from Excel into a newly created table in ARC 10.10 and found it did not work (everything tried to go into the first cell). I'm also disappointed to see that it still seems to be impossible to use import via OLE DB/ADO data source | Microsoft OLE DB provider for ODBC, connecting to an Excel driver to import from Excel into an ARC table. JD noted the problem in the middle of last year, and I thought I saw a reference to it in the 10.10 what's new, but it still sits doing nothing when I try to use it. Am I dense, or are Excel interfaces cursed?

Anonymous said...

On further experimentation I see I _was_ dense about copying from Excel into a table; it works if you right click and do the obvious things with the Copy Records alternative. The other issue I raised about the broken OLE DB/ADO Data Source import from Excel remains problematic even in version 10.10, however, as far as I can tell.

J.D. Mullin, Advantage Engineering Manager said...

Don't know who you are (posting as Anonymous), but send a table and steps to re-create the import issue to advantage@ianywhere.com and put Attn:JD in the subject line.

Post a Comment